NDB query with complex ORDER BY RLIKE not working correct
Notice: This thread is very old.
- ondrejsotek
- Member | 5
I was unable to found solution for creating NDB query with more complex ORDER BY part. I need to search for rows containing specified string (or part) and show rows containing whole string first:
$search = 'MacBook Air';
$query = $this->database->table('items');
foreach(explode(' ',$search) as $val) {
$query = $query->where('name LIKE ?','%'.$val.'%');
}
return $query->order('name RLIKE "(^| )'.$search.'( |$)" DESC,name');
However I got this SQL query with first word in ORDER BY RLIKE wrapped in “`”:
SELECT `id`, `name`
FROM `items`
WHERE (`name` LIKE '%MacBook%') AND (`name` LIKE '%Air%')
ORDER BY `name` RLIKE "(^| )`MacBook` Air( |$)" DESC,`name`
When search string contains only one word, it works fine:
$search = 'Air';
SELECT `id`, `name`
FROM `items`
WHERE (`name` LIKE '%Air%')
ORDER BY `name` RLIKE "(^| )Air( |$)" DESC,`name`
I found workaround solution – I used uppercase string in ORDER BY:
return $query->order('UPPER(name) RLIKE "(^| )'.strtoupper($search).'( |$)" DESC,name');
Returns:
SELECT `id`
FROM `items`
WHERE (`name` LIKE '%MacBook%') AND (`name` LIKE '%Air%')
ORDER BY UPPER(`name`) RLIKE "(^| )MACBOOK AIR( |$)" DESC,`name`
- David Matějka
- Moderator | 6445
use parameters
->order('name RLIKE ? DESC, name', '(^| )'.$search.'( |$)')
- ondrejsotek
- Member | 5
David Matějka wrote:
use parameters
->order('name RLIKE ? DESC, name', '(^| )'.$search.'( |$)')
Works perfect, thank you very much – I didn't know that I can use params in order() like in where().