CODEWITHSUNDEEP
ruby-on-railspostgresqlrails-activerecord
ZimZimma
ZimZimma

Reputation: 228

Using multiple tables in Rails scope query

Within my Cart model, which has_many Subcarts and belongs_to Locations, I'm trying to create a scope query where I get all Carts that have subcarts that have a created_at time stamp that is outside a particular amount of time from the Cart.Location.active_minutes. I'm having an issue trying to find a way to use the location.active_minutes in my query. I keep getting an undefined table error.

This is basically what I have so far.

scope :inactive_cart_bucket, -> {
  where('EXISTS (SELECT s.cart_id FROM cart_subcarts s WHERE (s.cart_id = cart.id) AND (? - s.created_at) / 3600 > cart.location.active_minutes)', Time.zone.now.utc)
}

Upvotes: 0

Views: 238

Answers (1)

Sebastián Palma
Sebastián Palma

Reputation: 33420

You have to get access to the locations table, perhaps by joining it through the belongs_to relation you have in the cart model:

scope :inactive_cart_bucket, -> {
  joins(:location).where(
    "EXISTS (
      SELECT s.cart_id
      FROM cart_subcarts s
      WHERE (s.cart_id = cart.id)
      AND ((? - s.created_at) / 3600) > locations.active_minutes
    )",
    Time.zone.now.utc
  )
}

Notice that's an INNER JOIN, depending on what you're trying to do, you're might need a different one.

Upvotes: 2

Related Questions