SQL - WHERE IF condition in QUERY BUILDER request

Hi @binho

You could do it in 3 times,

-1- Query for this location between dates in custom location, if 1 - result 1

-2- If 0 at the previous query, query is there a custom location between dates, if 1 - result 0

-3- if 0 , query for this location in default -> result

To be understandable (in the future and by others), it's easier to create a php function with these 3 queries and the right logic.

Hello @trin ,

Thanks for your help. I tried the your request but I still have one results with the following data :

• start_date = 2021-02-19
• end_date = 2021-02-19
• day = 5
• location_id = 1

I don't know if I missed something. Please find bellow my query :

$query->whereHas('workingLocations', function($query) use($days, $values) {
   $query->selectRaw('user_id, location_id, day, MAX(date), IF(MAX(date) IS NOT NULL, "custom", "default") type')
				->whereIn('location_id', $values)
                ->whereIn('day', $days)
                ->where(function($query) {
                         $query->whereNull('date')
                                ->orWhereBetween('date', [$this->start_date, $this->end_date]);
                            })->groupBy('day', 'user_id', 'location_id');

Many thanks for your help

My try:

Route::get('/test', function () {
    $startDate = '2021-02-22';
    $endDate = '2021-02-22';
    $days = [1];
    $locations = [5];

    $constraints = function ($query) use ($locations, $days, $startDate, $endDate) {
        $query->whereIn('location_id', $locations);
        $query->whereIn('day', $days);

        $model = $query->getModel();

        $query->where(function ($query) use ($model, $startDate, $endDate) {
            $query->orWhere(function ($query) use ($startDate, $endDate) {
                $query->where('type', 'custom');
                $query->whereBetween('date', [$startDate, $endDate]);
            });

            $query->orWhere(function ($query) use ($model, $startDate, $endDate) {
                $query->where('type', 'default');
                $query->whereNotExists(function ($query) use ($model, $startDate, $endDate) {
                    $query->selectRaw(1);
                    $query->from($model->getTable(), 'inner');
                    $query->whereColumn('user_id', $model->qualifyColumn('user_id'));
                    $query->whereColumn('day', $model->qualifyColumn('day'));
                    $query->whereColumn('location_id', '<>', $model->qualifyColumn('location_id'));
                    $query->where('type', 'custom');
                    $query->whereBetween('date', [$startDate, $endDate]);
                });
            });
        });
    };


    return \App\Models\User::query()
        ->with(['workingLocations' => $constraints])
        ->whereHas('workingLocations', $constraints)
        ->get();
});

Basically it checks if there is a "custom" record within the dates OR there is not a custom record on a different location for the same date.

Hope this helps.

sleep helps. if now I understand everything correctly (how difficult it is not to know English in 2021), then the problem is solved by join.

• first, we select by location_id, day and date, for only custom (date not null)
• left join itself table, with the conditions:
  • a.user_id = b.user_id
  • a.day = b.day
  • a.location_id != b.location_id
• we accept default locations only if the join returned null

raw sql

SELECT
    a.*
FROM
    locations a
    LEFT JOIN locations b ON (
        a.user_id = b.user_id AND 
        a.location_id != b.location_id AND 
        a.day = b.day
    )
WHERE 
	a.location_id IN (5) AND
    a.day IN (5) AND
	(
        a.date BETWEEN '2021-02-01' AND '2021-02-20' OR 
        (
        	a.date IS NULL AND
            b.id IS NULL
        )
    )

in laravel, wait 5-10 minut, i need to feed my bear. i check your data and what u need to result and query now correct. and EXPLAIN says all right, but my last word about indexes not quite correct. u steel need location_id for first query and day or user_id for second. depends on size of table, count day/user_id etc. in general, you need an index on all the fields that participate in the selection. and the mysql will decide for itself