Arel generates invalid SQL when using DISTINCT ON in PostgreSQL

Not sure if this is appropriate, and i'm no doctor, but i concur.

I need Arel to generate this SQL in postgres.

Discount.select("DISTINCT ON(email) *").order("email, created_at DESC")
=> SELECT DISTINCT ON (email) * FROM discounts ORDER BY email, created_at DESC

I ran across the same issue using Postgresql under Rails 3.0.4 and Ruby 1.9.2p0 2 months ago.

I wanted to do this:

scope :last_concerts,

select("DISTINCT (concerts.artist_id)").
joins(:event).
where("result <> ? and result <= ?", CONCERT_LOST, CONCERT_WON).
order(["concerts.artist_id", "events.date DESC"])

And ended up having to do it with find_by_sql:

def self.last_concert

find_by_sql("SELECT DISTINCT ON (artist_id) concerts.*
FROM concerts INNER JOIN events ON events.id = concerts.event_id
WHERE (result <> #{CONCERT_LOST} and result <= #{CONCERT_WON})
ORDER BY artist_id, events.date DESC;")

end

I meant to write under Rails 3.0.3 ...

We're getting good results after overwriting arel method: https://github.com/rails/arel/blob/master/lib/arel/visitors/postgre... to return just super in any case - not really sure why this subqueries are needed.
So after that change it's possibly to do in activerecord:

select("DISTINCT ON (posts.author_id) posts.*").order("posts.author_id")

which can be chained with other order calls.

Just small update - it seems that the column in DISTINCT ON must match the first column in ORDER BY clause:

This works fine:

SELECT DISTINCT ON(countries.name) countries.* FROM countries ORDER BY countries.name;

This causes error:

SELECT DISTINCT ON(countries.id) countries.* FROM countries ORDER BY countries.name;
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON(countries.id) countries.* FROM countries ...

According to PostgreSQL docs: The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). So only if it's not the case, a subquery needs to be generated.

I'm for #2 rake rails:freeze:edge on windows in a short term, because right now it doesn't work at all.

However, having e.g. the following requirement: "Display the latest post posted by each user and sort the results by created_at", it's really not so easy to do.

If one does:

SELECT DISTINCT ON(user_id) * FROM posts ORDER BY user_id, created_at DESC;

it will display the latest post by each user, but the results will be ordered by user_id. So, the results need to be used as a subquery, which will be ordered by created_at DESC once more:

SELECT * FROM posts WHERE id IN (SELECT DISTINCT ON(user_id) id FROM posts ORDER BY user_id, created_at DESC) ORDER BY created_at DESC

which is probably exactly what ARel tries to do now.

So in a long term, it would be nice if ARel did all this stuff automatically, but it's probably really complicated to implement it properly, so the most important thing for now is just to make it work at all :)

rolex watches are very common in our Audemars Piguet Replicas lifes, there are quite several well known wrist watches brands, the majority Gucci Replicas of them are Swiss bands, Panerai Replicas ,and it is Omega Replicas unlikely, unless the replica watches 's owner is filthy rich and equally careless replica breitling with his, Even the highest quality, Some replica omega are believed to be to acquire luxury wrist that are Replica Concord founded of gold or platinum or other high priced materials. placing on these wrist fake rolex certainly will make us stand out from other Swiss Replica Watch people.Does everyone can afford these genuine, replica tag heuer Taking your or replica watch ? When should expensive replica watch uk , before you take your precious?