CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlactiverecord
Joey
Joey

Reputation: 762

Rails 3 Distinct Join With Postgresql

Using Postgresql 9.1 and Rails 3.2.2. How would I return a collection of the last single users_checkins per venue for a given user?

I have tried 

User.find(1)
  .users_checkins
  .joins(:venues_checkins)
  .merge(VenuesCheckin.select('DISTINCT(venues_checkins.venue_id)'))

but that does not retrieve any of the attributes for the UsersCheckin. My models are below.

class Venue
  has_many :venues_checkins
  has_many :users_checkins, through: :venues_checkins
end

class UsersCheckin
  #start:datetime
  #finish:datetime
  belongs_to :user
  belongs_to :venues_checkin
end

class VenuesCheckin
  belongs_to :venue
  has_many :users_checkins
end

class User
  has_many :users_checkins
  has_many :venues_checkins, through: :users_checkins
  has_many :venues, through: :users_checkins
end

Update

If a user has 3 users_checkins records for Venue A and 3 for Venue B, my collection should return 2 records with one record being the last created users_checkin from Venue A and the other record being the last created users_checkin from Venue B with the collection ordered by first record in the collection being which of those 2 records was created last. And I start with the user because I only want the users_checkins for the given user.

Upvotes: 1

Views: 351

Answers (1)

Roboprog
Roboprog

Reputation: 3144

If you can write the SQL and get it to work in the psql prompt, make it into a custom method in the User class, and skip trying to wedge the custom query into the ORM.

Also, it sounds like you want a min/max qualifier in your query, rather than distinct.

Upvotes: 1

Related Questions