Using multiple tables in Rails scope query

Question

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)
}

Sebastián Palma · Accepted Answer · 2021-04-06 18:38:18Z

2

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.

answered Apr 6, 2021 at 18:38

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

engineersmnky Over a year ago

I am not an expert in PostgreSQL but it seems like timestamp - timestamp provides an interval and dividing by 3600 integer does not seem to have the desired effect of changing this into the minutes between. It seems (based on some googling) the appropriate approach would be AND EXTRACT(EPOCH FROM (? - s.created_at))/60 > locations.active_minutes

Sebastián Palma Over a year ago

Yeah, me neither, that's why I just added a few changes to answer the question but is good for OP to know it. Thanks @engineersmnky ;)

ZimZimma Over a year ago

I was just going to come back in here to ask about that. Thanks! And would there be a way to get Carts based on the latest subcart?

Collectives™ on Stack Overflow

Using multiple tables in Rails scope query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related