How to validate a date between two columns in a db table? in Laravel

Question

How to validate a date between two columns in a db table? in Laravel

Navigation

#1 by (1 votes)

0

I'm trying this way:

Turnos::whereBetween(DB::raw("'".$req->crear_hora_inicial2."'"), ['hora_inicio','hora_final'])
      ->orWhereBetween(DB::raw("'".$req->crear_hora_final2."'"), ['hora_inicio', 'hora_final'])
      ->exists()

This creates the following sql:

select 
    exists(
        select 
            * 
        from 
            "turnos" 
        where 
            '03:00' between 'hora_inicio' and 'hora_final' 
            or
            '06:59' between 'hora_inicio' and 'hora_final'
    ) 
as "exists"

The problem is that the columns that are hora_inicio and hora_final should tener doble comilla (") , with comilla simple the data base interprets it as a string and I require that I recognize it as a column, as I already said double quotes.

Update 1:

I could not find a solution, I did this provisionally:

DB::select( DB::raw("select exists(select * from turnos where '$inicio' between hora_inicio and hora_final or '$fin' between hora_inicio and hora_final) as exists") );

The result (by Debugbar) is as follows:

array:1 [
  0 => {#641
    +"exists": false
  }
]

How could I get only the boolean of exists ?

laravel laravel-5

asked by Pablo Contreras 22.02.2017 в 08:43

source

1 answer

Array foreach and MYSQL query with php Activate PDO_OBDC drivers for PHP?

score 1 · Answer 1

I consider that there is no way to do this with whereBetween , it is a method created only to handle values and not column names.

/**
 * Add a where between statement to the query.
 *
 * @param  string  $column
 * @param  array   $values
 * @param  string  $boolean
 * @param  bool  $not
 * @return $this
 */
public function whereBetween($column, array $values, $boolean = 'and', $not = false)
{
    $type = 'between';

    $this->wheres[] = compact('column', 'type', 'boolean', 'not');

    $this->addBinding($values, 'where');

    return $this;
}

The only way I can think of without using "raw" queries would be grouping queries:

$hora1 = $req->crear_hora_inicial2;
$hora2 = $req->crear_hora_final2;

where(function ($query) use ($hora1) {
    $query->where('hora_inicio', '<=', $hora1);
    $query->where('hora_final', '>=', $hora1);
})
orWhere(function ($query) use ($hora2) {
    $query->where('hora_inicio', '<=', $hora2);
    $query->where('hora_final', '>=', $hora2);
})