Prioritise condition in QueryInterface

Cedric Lenders

7/7/24, 11:58 AM

I have an overview with a search field. The query should search on both the title and description, but I would want to show my results that have the search parameter in the title first in the list. I am using a QueryInterface and have following condition group

 $group = $query->orConditionGroup();
 $group->condition('title', "%" . $searchString . "%", 'LIKE');
 $group->condition('field_description', "%" . $searchString . "%", 'LIKE');
 $query->condition($group);

Does anyone know a way to use a groupBy in a QueryInterface to do something like this

ORDER BY
(title LIKE "%" . $searchString . "%") DESC,
(field_description LIKE "%" . $searchString . "%") DESC;

0 + 5

database

Clive

7/7/24, 8:08 PM

Not sure it's the most efficient, but this is one outline way to do it: use `$query->addTag('foo');`, then implement [`hook_query_foo_alter()`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21database.api.php/function/hook_query_TAG_alter/8.8.x) to get access to the regular query once it's been built. From there you can add `CASE` expressions for your fields (example [here](https://drupal.stackexchange.com/questions/199806/how-so-i-execute-query-orderbyfieldnode-nid-5-12-3-6-asc)), then use `$query->orderBy` with those added expressions

Clive

7/7/24, 8:37 PM

As a slight aside, it'd be a good idea to inject the `database` service and use its `escapeLike` method on `$searchString`. Without it, if someone searches for a string with a `%` character you're risking an SQL error or at least inaccurate results

apaderno

7/9/24, 7:19 AM

`condition()` is for `WHERE` clauses, which have a different effect than `ORDER BY`. `ORDER BY` would then not show first the rows for which the desired value is found in the title and then the rows for which the same value is found in a field description.

Cedric Lenders

7/12/24, 6:56 AM

@Clive Can i use my variable $searchstring in this hook?

Clive

7/12/24, 8:19 PM

I don't think there's a way to pass it through, but anything you can declare in your current context you can also declare in the hook (or define a service to give you that data if you use it in more than one place)

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Prioritise condition in QueryInterface

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.