This project is archived and is in readonly mode.

#6426 new
Igor Alexandrov

[PATCH] Allow array as order param for postgresql

Reported by Igor Alexandrov | February 13th, 2011 @ 04:16 PM

Hello. This ticked is enhanced version of this one

The problem is that PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and also requires that the ORDER BY include the distinct column. All works pretty well if you just have plain parameters in ORDER BY clause. For example, something like

ORDER BY "pcs".min_cost DESC, "pcs".title
will be parsed well and added to DISTINCT PART as
SELECT DISTINCT ..., "pcs".min_cost AS alias_0, "pcs".title AS alias_1

But if you will use something complex in ORDER BY string – all will be bad! The problem is that now distinct() method of PostgreSQL adapter simple splits ORDER BY string by ','.

For example:

ORDER BY COALESCE("pcs".min_cost, "pcs".max_cost) DESC

will be parsed, splitted and transformed into incorrect DISTINCT clause:

SELECT DISTINCT ON ("pcs".id) "pcs".id, COALESCE(pcs.min_cost AS alias_0, pcs.max_cost) AS alias_1 FROM "pcs"

This query won't be executed because of syntax errors. I created patch that allows to use Array as :order param in find() method.
:order => ["COALESCE(pcs.min_cost, pcs.max_cost) DESC"] will be translated into

SELECT DISTINCT ON ("pcs".id) "pcs".id, COALESCE(pcs.min_cost, pcs.max_cost) AS alias_0

:order => ["COALESCE(pcs.min_cost, pcs.max_cost) DESC", "pcs.title"] will be

SELECT DISTINCT ON ("pcs".id) "pcs".id, COALESCE(pcs.min_cost, pcs.max_cost) AS alias_0, pcs.title AS alias_1

All works good, and Array syntax is easy to understand. All test run well. This affects 2.3 and 3.0 branches.

Thanks. Hope it will be useful.

Comments and changes to this ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket