Chaining orWhere clauses on a query doesn&#39;t produce correct SQL statements

Level 16

I don't see any difference in your query builders. You probably forgot to wrap the OR calls inside a single where.

Level 1

@mabdullahsari Hi - the difference is that in the first I go 'field' => 'value' and in the second I go ['field', 'value'] and the second works

Level 73

It looks correct to me.

This part

->orWhere([
    'orig' => 'BOM',
    'dest' => 'DOH',
    ['dep_time_local', 'like', '2022-12-19 04:15%']
])

Translats to

(`orig` = 'BOM' or `dest` = 'DOH' and `dep_time_local` like '2022-12-18 04:15%')

And this

->orWhere([
    ['orig',  'BOM'],
    ['dest',  'DOH'],
    ['dep_time_local', 'like', '2022-12-19 04:15%']

Translates to

(`orig` = ? and `dest` = ? and `dep_time_local` like ?)"

Updated.

Level 1

@Tray2 Hi, you've misread what I posted as my second query The first is 'field' => 'value' as you correctly put, however, the second is ['field', 'value'] not ['field' => 'value'] My way does not produce a null value, and in fact produces the correct, expected query

Level 73

@justash666

You are correct, I was a bit fast in my analasys. I updated my previous answer

Level 1

@Tray2 I just tried both methods, they do not produce the same SQL ['field' => 'value'] produces or (`BOM` is null and `dest` = 'DOH' and `dep_time_local` like '2022-12-18 04:15%')

['field', 'value'] produces or (`orig` = 'BOM' and `dest` = 'DOH' and `dep_time_local` like '2022-12-18 04:15%')

Level 73

@justash666 I updated my answer

It looks correct to me.

This part

->orWhere([ 'orig' => 'BOM', 'dest' => 'DOH', ['dep_time_local', 'like', '2022-12-19 04:15%'] ])

Translats to

(orig = 'BOM' or dest = 'DOH' and dep_time_local like '2022-12-18 04:15%') And this

->orWhere([ ['orig', 'BOM'], ['dest', 'DOH'], ['dep_time_local', 'like', '2022-12-19 04:15%'] Translates to

(orig = ? and dest = ? and dep_time_local like ?)"

Level 1

@Tray2 So you've got my point right there

In that first set - even you have found that it does field1 or field2 and field 3 It should be field1 and field2 and field3

The way it's currently running, it picked up so many results because it didn't accept my array as a group of conditions to be applied to the query as a whole

You could argue this is meant to be the case, but if that was correct, then simply changing the format of the array passed into the orWhere clause (as I've shown above) should NOT have changed the conditions from or-and-like to -and-and-like

This is why I believe this is a bug

Make sense?

Level 1

@Tray2 Got you, just seen the change

The problem with the query is that, when it's generating an or inside the orWhere clause, it's leading to many other models that shouldn't have been selected It's essentially querying like this: where ((origin = 'BOM') or (dest = 'DOH' and dep_time_local like '2022-12-18 04:15%')) or ... instead of where (origin = 'BOM' and dest = 'DOH' and dep_time_local like '2022-12-18 04:15%') or ...

Level 73

@justash666 Yes and that is why you need to use the ->orWhere([ ['orig', 'BOM'], ['dest', 'DOH'], ['dep_time_local', 'like', '2022-12-19 04:15%'] syntax.

Level 1

@Tray2 I see what you're saying now. I added another element into the array as a test

Until now, I haven't come across anything stating that in an orWhere clause, the arrow syntax denotes an or expression inside that clause

I just checked on the docs, if it's there, I've missed it

I'm not sure I am happy with that, but thanks for clearing up the confusion!

Level 1

@Tray2 I've updated my post with that and a bit of an explanation of why to use square brackets to avoid the problems I had

I'm happy to reword it if it's not clear enough

LewisCowles1986

Level 12

yo Ash :wave:, I don't use the two param syntax. As you're noticing now it's un-intuitive, as are arrays to query builders. Your code will be easier to read, lint and review sticking to individual functions. If You need to go from an array to a query-builder. Take the time and create a mapper.

Instead I usually either do a single call per group with three params, chaining calls; or use the function passing the builder to wrap something in layers of parenthesis. You can start with inline function, but later extract to a closure or callable named class. Makes my life easier.

If I want to isolate a query builder, I use clone; which I might do if I have a common component (like your orig and dest); and if I want ands and or's like this; I might even consider writing SQL; or use more chains, with sub-components as arguments. Either way; it'd produce what I expect. To me the below reads a little better.

$flights = Flght::where(function ($query) {
        return $query->where('orig', '=', 'BEG')
            ->where('dest', '=', 'DOH')
            ->where('dep_time_local', 'like', '2022-12-18 11:45%');
    })->orWhere(function($query) {
        return $query->where('orig', '=', 'BEG')
            ->where('dest', '=', 'DOH')
            ->where('dep_time_local', 'like', '2022-12-19 04:15%');
    });

I notice that you were saying something was like field and field and field... I didn't know that single array with key => value works that way, whereas grouped sub-array within array makes AND; it sort of makes sense if you squint, but arrays as arguments is always going to be disgusting, because of their loose nature. Check out https://www.parthpatel.net/laravel-multiple-where-and-or-and-conditions-example/ which uses a where (AND'ing) with function.

This is yet another one I'll pour out to query builders being an awful pattern. Composing classes of options can help things.

Btw I also would not use like with the time. You can use whereBetween, or add a select to truncate. Carbon can do some wonderful crap if you actually need so many dates, but even if there were intervals I'd like to exclude; I'd probably chain whereBetween's within an orWhere.

Flght::where('orig', '=', 'BEG')
            ->where('dest', '=', 'DOH')
            ->whereBetween('dep_time_local', [$min, $max])