Does Drupal support DISTINCT ON (field1, field2)

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
mariacha1's picture
Posted by mariacha1 on December 19, 2013 at 12:23am

Hi guys,

I'm new to postgreSQL in Drupal and can't figure out how to do a DISTINCT ON select within drupal's query classes with declared fields like this:

SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM table1

I see you can set a query as $query->distinct(TRUE) but that just seems to add the word DISTINCT, which if I understand it, just makes sure that all columns combined are unique.

Is this possible to pass those DISTINCT ONs to a funciton or should I rewrite my query to do it some other way?

Categories:

Comments

Db query

Posted by metzlerd on December 19, 2013 at 2:09pm
metzlerd's picture

The database api doesn't support all syntaxes since they aren't available in all databases, however if you don't need to build the query dynamically consider using db_query() instead of db->select. If you use db_query you can use whatever postgres syntax you need.

Extender

Posted by mradcliffe on December 19, 2013 at 3:08pm
mradcliffe's picture

I like to maintain compatibility with as many databases as possible in my code. The other way of doing this, is to write a Query Extender class for DistinctOn.

You could also write database specific implementations that use MySQL's "group by" to support MySQL as well.

Db query

Posted by metzlerd on December 19, 2013 at 2:24pm
metzlerd's picture

Woops duplicate....deleting.

Db query

Posted by metzlerd on December 19, 2013 at 2:25pm
metzlerd's picture

Woops duplicate deleting.

Thanks for the advice

Posted by mariacha1 on December 19, 2013 at 6:01pm
mariacha1's picture

I ended up doing a really hacky thing and adding the DISTINCT ON as an expression like so:

$query->addExpression('DISTINCT ON (field1, field2) field1', 'field1');

Which produces
SELECT DISTINCT ON (field1, field2) field1 as field1, field2, field3 FROM table1

But I think the QueryExtend for DistinctOn is the correct way to go about it. I'm just suprised such a thing doesn't exist already! I'll post back if I get it working.