{"id":5685,"date":"2014-04-05T04:22:27","date_gmt":"2014-04-05T04:22:27","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/04\/05\/finding-unique-records-ordered-by-field-in-association-with-postgresql-and-rails-3-collection-of-common-programming-errors\/"},"modified":"2014-04-05T04:22:27","modified_gmt":"2014-04-05T04:22:27","slug":"finding-unique-records-ordered-by-field-in-association-with-postgresql-and-rails-3-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2014\/04\/05\/finding-unique-records-ordered-by-field-in-association-with-postgresql-and-rails-3-collection-of-common-programming-errors\/","title":{"rendered":"Finding unique records, ordered by field in association, with PostgreSQL and Rails 3?-Collection of common programming errors"},"content":{"rendered":"

UPDATE<\/strong>: So thanks to @Erwin Brandstetter, I now have this:<\/p>\n

def self.unique_users_by_company(company)\n  users = User.arel_table\n  cards = Card.arel_table\n\n  users_columns = User.column_names.map { |col| users[col.to_sym] }\n\n  cards_condition = cards[:company_id].eq(company.id).\n    and(cards[:user_id].eq(users[:id]))\n\n  User.joins(:cards).where(cards_condition).group(users_columns).\n    order('min(cards.created_at)')\nend\n<\/code><\/pre>\n
… which seems to do exactly what I want. There are two shortcomings that I would still like to have addressed, however:<\/p>\n
    \n
  1. The order()<\/code> clause is using straight SQL instead of Arel (couldn’t figure it out).<\/li>\n
  2. \n
    Calling .count<\/code> on the query above gives me this error:<\/p>\n
    \n
    NoMethodError: undefined method ‘to_sym’ for # from \/Users\/neezer\/.rvm\/gems\/ruby-1.9.3-p0\/gems\/activerecord-3.1.1\/lib\/active_record\/relation\/calculations.rb:227:in ‘execute_grouped_calculation’<\/p>\n<\/blockquote>\n
    … which I believe is probably related to how I’m mapping out the users_columns<\/code>, so I don’t have to manually type in all of them in the group<\/code> clause.<\/p>\n<\/li>\n<\/ol>\n
    How can I fix those two issues?<\/p>\n
    ORIGINAL QUESTION<\/strong>:<\/p>\n
    Here’s what I have so far that solves the first part of my question:<\/p>\n
    def self.unique_users_by_company(company)\n  users = User.arel_table\n  cards = Card.arel_table\n\n  cards_condition = cards[:company_id].eq(company.id)\n    .and(cards[:user_id].eq(users[:id]))\n\n  User.where(Card.where(cards_condition).exists)\nend\n<\/code><\/pre>\n
    This gives me 84 unique records, which is correct.<\/p>\n
    The problem is that I need those User<\/code> records ordered by cards[:created_at]<\/code> (whichever is earliest for that particular user). Appending .order(cards[:created_at])<\/code> to the scope at the end of the method above does absolutely nothing.<\/p>\n
    I tried adding in a .joins(:cards)<\/code>, but that give returns 587 records, which is incorrect (duplicate User<\/code>s). group_by<\/code> as I understand it is practically useless here as well, because of how PostgreSQL handles it.<\/p>\n
    I need my result to be an ActiveRecord::Relation<\/code> (so it’s chainable) that returns a list of unique<\/strong> users who have cards that belong to a given company, ordered by the creation date of their first card… with a query that’s written in Ruby and is database-agnostic. How can I do this?<\/p>\n
    class Company\n  has_many :cards\nend\n\nclass Card\n  belongs_to :user\n  belongs_to :company\nend\n\nclass User\n  has_many :cards\nend\n<\/code><\/pre>\n
    Please let me know if you need any other information, or if I wasn’t clear in my question.<\/p>\n","protected":false},"excerpt":{"rendered":"
    UPDATE: So thanks to @Erwin Brandstetter, I now have this: def self.unique_users_by_company(company) users = User.arel_table cards = Card.arel_table users_columns = User.column_names.map { |col| users[col.to_sym] } cards_condition = cards[:company_id].eq(company.id). and(cards[:user_id].eq(users[:id])) User.joins(:cards).where(cards_condition).group(users_columns). order(‘min(cards.created_at)’) end … which seems to do exactly what I want. There are two shortcomings that I would still like to have addressed, however: The […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-5685","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/5685","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=5685"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/5685\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=5685"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=5685"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=5685"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}