MySQL to Drupal 7 db_select

Question

I am trying to execute the following mysql query below with drupal 7 db_select. But I cant understand how this can be done. Is anyone can help me to translate the following mysql query into drupal 7 dynamic db query?

My main goal is actually sorting the mysql result by given string position in the name. Please keep in mind that i dont want to fetch all the results and sort them with php, instead I want to use mysql to do that. As i know the "ORDER BY LOCATE" command is doing that exactly.

SELECT name FROM `taxonomy_term_data` WHERE LOCATE('credit', name) > 0 ORDER BY LOCATE('credit', name)

Answer 1

1. Proper example of db_select

It is possible, using drupal 7 db_select, here is my example working code (done with help of this post)

My example in with table cities containing column city. Find cities with double "o" and sort by it's position:

$r = db_select('cities', 't')
  ->fields('t')
  ->condition('t.city', '%' . db_like('oo') . '%', 'LIKE');
$r->addExpression("LOCATE('oo', city) ", 'loc');
$r = $r->orderBy('loc', 'DESC')
  ->execute()
  ->fetchAllAssoc("id");

So similar in your example would be:

$r = db_select('taxonomy_term_data', 't')
  ->fields('t')
  ->condition('t.name', '%' . db_like('credit') . '%', 'LIKE');
$r->addExpression("LOCATE('credit', name) ", 'loc');
$r = $r->orderBy('loc', 'DESC'); //Or ASC
//Execute your query and gather result anyway you want.

2. Do you need to use db_select?

As someone stated in comment in link I posted "There are times and places to just use db_query."

I think this is that time :) Dont overcomplicate your code just to use drupal-way logic, which is often outdated or just too simple for complex tasks.

Answer 2

I think you should try something like this. db_like function seems to do what you are looking for.

$result = db_select('taxonomy_term_data', 'ttd')
  ->fields('ttd', 'name')
  ->condition('ttd.name, '%' . db_like('credit') . '%', 'LIKE')
  ->orderBy('ttd.name', 'DESC')
  ->execute();