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: PostgreSQL
Comments
Db query
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
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
Woops duplicate....deleting.
Db query
Woops duplicate deleting.
Thanks for the advice
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 table1But 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.
Did you ever get this
Did you ever get this working?