Rails Ruby on Rails → 3.x

Comments and changes to this ticket

• You flagged this item as spam.
  

  23inhouse February 19th, 2011 @ 12:52 PM

  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
  

• You flagged this item as spam.
  

  Jon Atack February 19th, 2011 @ 09:57 PM

  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

• You flagged this item as spam.
  

  Jon Atack February 19th, 2011 @ 09:58 PM

  I meant to write under Rails 3.0.3 ...

• You flagged this item as spam.
  

  Wojciech Wnętrzak February 21st, 2011 @ 10:51 AM

  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.

• You flagged this item as spam.
  

  Szymon Nowak February 24th, 2011 @ 10:03 AM

  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.

• 

  Aaron Patterson March 22nd, 2011 @ 11:30 PM

  • Milestone set to “3.x”
  • Importance changed from “” to “Low”

  I'd like to remove the automatic subselect generation. It's a relic from ARel 1.0.

  However, removing the subselect generation breaks tests in AR. Specifically, it breaks queries like this: https://github.com/rails/rails/blob/3-0-stable/activerecord/test/ca...

  I think there are two ways to fix this:

  1. If a user doesn't add the correct order by clause, add it automatically here: https://github.com/rails/rails/blob/3-0-stable/activerecord/lib/act...

  2. Don't add anything automatically, let the database blow up, and force the user to specify the column in the order clause.

  Personally I lean towards #2 since there is less magic involved. But either way, I think this should be fixed in Rails 3.1.

  Thoughts?

• You flagged this item as spam.
  

  Szymon Nowak April 11th, 2011 @ 02:10 PM

  I'm for #2 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 :)

• You flagged this item as spam.
  

  laptopbatteries April 16th, 2011 @ 03:10 AM

  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?

• 

  Repository April 22nd, 2011 @ 04:49 AM

  • State changed from “new” to “resolved”

  (from [724a7866774c7847cc79b699ccf7da9fee72b154]) stop using distinct on for the unique id queries. [#6450 state:resolved] https://github.com/rails/rails/commit/724a7866774c7847cc79b699ccf7d...