dimanche 12 avril 2020

How to join request on 2 fields with OR condition?

In my laravel 5.8 app I need to make report by table storage_spaces with 2 fields check_in_date and check_out_date when year and monthes are entered. I make as:

$report_year = (int)$requestData['report_year'];
$report_month = (int)$requestData['report_month'];

$report_date_from= $report_year.'-'.$report_month.'-01';
if($report_month == 12) { // calculate from and till dates
    $report_month= 1;
    $report_year++;
} else {
    $report_month++;
}
$report_date_till= $report_year.'-'.$report_month.'-01';

$storageSpacesCollection = StorageSpace
    ...
    ->getByCheckInDate($report_date_from, ' >= ')
    ->getByCheckInDate($report_date_till, '< ')

    ->getByCheckOutDate($report_date_from, ' >= ')
    ->getByCheckOutDate($report_date_till, '< ')
    ...

As result I have invalid sql with several AND conditions :

   SELECT `storage_spaces`.*, 
    FROM `storage_spaces` 
    ...
    WHERE storage_spaces.check_in_date  >= '2020-1-01'      AND storage_spaces.check_in_date < '2020-2-01'      AND storage_spaces.check_out_date  >= '2020-1-01'      AND storage_spaces.check_out_date < '2020-2-01'  
    ORDER BY `storage_spaces`.`id` asc 

But the error is that between check_out_date and check_out_date must be OR condition, as any date withing start/end dates must be in report.

Scopes are defined in the model :

public function scopeGetByCheckInDate($query, $filter_check_in_date_from = null, string $sign = null)
{
    if ( ! empty($filter_check_in_date_from)) {
        if ( ! empty($sign)) {
            $query->whereRaw(DB::getTablePrefix().with(new StorageSpace)->getTable() . '.check_in_date ' . $sign . "'" . $filter_check_in_date_from . "' ");
        } else {
            $query->where(with(new StorageSpace)->getTable() . '.check_in_date', $filter_check_in_date_from);
        }
    }
    return $query;
}

public function scopeGetByCheckOutDate($query, $filter_check_out_date_from = null, string $sign = null)
{
    if ( ! empty($filter_check_out_date_from)) {
        if ( ! empty($sign)) {
            $query->whereRaw(DB::getTablePrefix().with(new StorageSpace)->getTable() . '.check_out_date ' . $sign . "'" . $filter_check_out_date_from . "' ");
        } else {
            $query->where(with(new StorageSpace)->getTable() . '.check_out_date', $filter_check_out_date_from);
        }
    }
    return $query;
}

Which is valid way to set OR condition ?

Thanks!



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire