Does Drupal support DISTINCT ON (field1, field2)

mariacha1's picture

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?


Db query

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.


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

metzlerd's picture

Woops duplicate....deleting.

Db query

metzlerd's picture

Woops duplicate deleting.

Thanks for the advice

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.


Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week