Eloquent query results order - where before orWhere

Level 56

$results = Terms::query()
    ->where(function ($builder) use ($query) {
        $builder
            ->orWhere('name', '=', $query)
            ->orWhere('name', 'like', $query)
            ->orWhereJsonContains('synonyms', $query);
    })
    ->orderByRaw('CASE
        WHEN name = ? THEN 1
        WHEN name LIKE ? THEN 2
        ELSE 3
    END', [$query, $query])
    ->get();

Note: always try to group OR WHERE clauses inside a group, so Laravel adds a parenthesis around them, and if you later needs to add another WHERE condition, or you add a global scope added to your model, you avoid undesired results by combining AND and OR WHERE clauses without parenthesis.

Level 3

@rodrigo.pedra thanks for this solution but how do you orderBy for json array column of synonyms?

Level 56

@CLab well, if it does not match the first condition, and not match the second condition, all matches by synonyms will be grouped in the end, that was what you asked for.

Ordering by the whole synonyms list is pretty straight forward, you just need to add an additional order by clause:

$results = Terms::query()
    ->where(function ($builder) use ($query) {
        $builder
            ->orWhere('name', '=', $query)
            ->orWhere('name', 'like', $query)
            ->orWhereJsonContains('synonyms', $query);
    })
    ->orderByRaw('CASE
        WHEN name = ? THEN 1
        WHEN name LIKE ? THEN 2
        ELSE 3
    END', [$query, $query])
    ->orderByRaw('CASE
        WHEN name = ? THEN name
        WHEN name LIKE ? THEN name
        ELSE synonyms
    END', [$query, $query])
    ->get();

This will first group your result set into buckets: first the exact matches, then the matches with the LIKE operator, and then the rest, in other words what matched something into the synonyms list.

The second order by, will sort the first two buckets by their name field, and the third bucket by the JSON array string representation, including non-matched synonyms.

Sorting the last bucket just using the matched synonyms is trickier, and actually depends on some decisions:

Should I compare the whole synonyms list, including the ones that do not match the query, or just the matched ones?
If one record matches a synonym in the second position, and the other matches a synonym in the fifth position, will this position differences will be taken into account?

Anyways, I don't use JSON arrays in MySQL that much, and if you want to dive in this last option, I can't help you anymore.

Good luck =)

Level 3

@rodrigo.pedra thanks for the details. I wanted an exact match on the synonyms array before the like matches on the name and like match on the synonyms. I made a mistake in the original question so I am sorry for the confusion. I have edited the question and order of things I want.

Level 56

@CLab you have two options:

adjust the conditions on the order by clause to use the criteria in the order you want
adjust the results for each case, so it orders accordingly to what you want.

Let's go with option 2:

$results = Terms::query()
    ->where(function ($builder) use ($query) {
        $builder
            ->orWhere('name', '=', $query)
            ->orWhere('name', 'like', $query)
            ->orWhereJsonContains('synonyms', $query);
    })
    ->orderByRaw('CASE
        WHEN name = ? THEN 3
        WHEN name LIKE ? THEN 2
        ELSE 1
    END', [$query, $query])
    ->get();

Note I inverted the results of each case branch, so ORDER BY will get a first bucket with the whereJsonContains results, a second bucket with the LIKE match, and finally a third bucket with the exact match.

In this case you could have added DESC right after the END keyword on the CASE statement, but I wanted to illustrate how you can play with each CASE result to achieve your desired ordering.

Level 3

@rodrigo.pedra to play with the CASE how can I put a case for the synonyms column. Is something like this possible:

 WHEN synonyms = ? THEN 2
 WHEN synonyms LIKE ? THEN 3

Once I understand that I can rearrange it but I couldn't find a way to orderBy JSON array values.

reference: https://github.com/laravel/framework/blob/544ee0d2add5619ea85993c6462e86a1a250ecbe/src/Illuminate/Database/Query/Grammars/MySqlGrammar.php#L89-L101=

Best Answer

Level 56

@CLab

from Laravel's codebase you can see it uses a MySQL function called JSON_CONTAINS:

$results = Terms::query()
    ->where(function ($builder) use ($query) {
        $builder
            ->orWhere('name', '=', $query)
            ->orWhere('name', 'like', $query)
            ->orWhereJsonContains('synonyms', $query);
    })
    ->orderByRaw('CASE
        WHEN JSON_CONTAINS(synonyms, ?) THEN 1
        WHEN name LIKE ? THEN 2
        ELSE 2
    END', [json_encode($query), $query])
    ->get();

Note the json_encode() call inside the bindings. This is needed as the string needs to be quoted as a JSON string, and then quoted as a SQL string value.

If you are using a database other than MySQL, you can follow the link above and navigate to the other supported database's grammars to see what the syntax is on each one.

1 like