This project is archived and is in readonly mode.

#5364 ✓stale
Frank Richter

Incorrect SQL from find with :limit and function call in :order

Reported by Frank Richter | August 12th, 2010 @ 10:33 AM

Given two associated Models one can do:
ModelA.find(:all, :include => :model_b, :order => 'some_field, another_field', :limit => 10)

This works fine. But if we use a function call with comma separated parameter list in the order-by clause,
as in

ModelA.find(:all, :include => :model_b, :order => 'substr(some_field, 3, 5)', :limit => 10)

we get a PGError (Syntaxerror). The generated SQL is:

SELECT * FROM (SELECT DISTINCT ON ("model_a".id) "model_a".id, substr(model_b.some_field AS alias_0, 3 AS alias_1, 5) AS alias_2 FROM "model_a" LEFT OUTER JOIN "model_b" ON "model_b"."model_a_id" = "model_a"."id") AS id_list ORDER BY id_list.alias_0 , id_list.alias_1 , id_list.alias_2 LIMIT 2

Looks like rails generated a field list from the order by clause by separating the clause on commas, regardless
for what purpose they are there.

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