1

I'm building a query with $query = new db_select('node', 'n') and I'm grouping by a certain field (let's say $query->groupBy('title')), when I'm aggregate another field (such as $query->addField('n', 'MAX(nid)')) it gets sanitized into a field called n.MAXnid (which crashes with a warning when executed)

How can I add a n.MAX(nid) field to my select query?

2 Answers 2

3

In Drupal 7 you should add expression for this.
Read more on Dynamic queries > Expressions

0
11

In your case, the code should be something similar to the following one:

$query = db_select('node', 'n')->groupBy('title');
$query->addExpression('MAX(nid)', 'max_nid');

You don't use $query = new db_select('node', 'n'); db_select() already returns an object. If you want to clone that object, you should not invoke new on it.

$query->addExpression() returns the alias currently used for the expression you add; it is normally the same alias passed to the method, but it can be different if the alias is already used. In this case, the alias is max_nid if max_nid is not already used, or max_nid_N if the alias is already used; N is an integer that is added to make the alias unique.
Using the alias returned from $query->addExpression() is important when a function is altering a query passed from other functions, such as in the case of a query passed to a hook. In this specific case it is not necessary, as the function that is calling db_select() is also adding the expression.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.