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