Using SQL functions in conditionals in Drupal 7 db_select()

Question

I am trying to write a condition into a SQL WHERE clause that forces the column comparison to a variable to be compared in lowercase. However the addExpression function isn't accomplishing this (since that is putting the expression in the field selection, not the where clause.

Here is what I have tried:

$category = 'mobile wifi';
$query = db_select('taxonomy_term_data', 'ttd')
    ->fields('ttd', array('tid'));
$query->innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid');
$query->addExpression("LOWER(ttd.name) = $category");
$result = $query->condition('machine_name', 'images_cat', '=')
    ->execute()
    ->fetchAssoc();

And this:

$category = 'mobile wifi';
$query = db_select('taxonomy_term_data', 'ttd')
    ->fields('ttd', array('tid'));
$query->innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid');
$result = $query->condition('machine_name', 'images_cat', '=')
    ->condition('LOWER(ttd.name)', $category, '=')
    ->execute()
    ->fetchAssoc();

The second one becomes invalid and the first one causes the problem I mentioned above. Any thoughts or suggestions?

Thanks, Patrick

Answer 1

change

$query->addExpression("LOWER(ttd.name) = $category");

to

$query->where('LOWER(ttd.name) = :category', array(':category' => $category));

Answer 2

Using LOWER() is considered slow in MySQL. It isn't necessary too because LIKE in Drupal's Database API (DBTNG) is case insensitive, at least when your MySQL table is configured to use one of the *_ci collations. A standard MySQL installation uses *utf8_general_ci* and so is Drupal.

So you just need to use a LIKE condition:

$query->condition('name', $category, 'LIKE');

See Conditional Clauses for a comprehensive explaination.

BTW: A DBTNG based database driver is responsible for implementing a case insensitive LIKE. PostgreSQL for instance uses ILIKE instead of LIKE which is handled in includes/database/pgsql/database.inc.

Answer 3

You can still use addExpression.

// Functions - You can use sql functions using addExpression ($expression, $alias = NULL, $arguments = array()).
$query->addExpression('MIN(o.tid)', 'min_term_id');

Resource: SQL query examples in Drupal 7