NDB query with complex ORDER BY RLIKE not working correct

Notice: This thread is very old.

4 years ago

ondrejsotek
Member | 5
+
0
-

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`

4 years ago

David Matějka
Moderator | 5988
+
0
-

use parameters

->order('name RLIKE ? DESC, name', '(^| )'.$search.'( |$)')

4 years ago

ondrejsotek
Member | 5
+
0
-

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().