Rails 5 ActiveRecord optional inclusive where for nested association's attribute

user_2241385 0
Viewed 584

Assuming this simplified schema:

users has_many discount_codes

discount_codes has_many orders

I want to grab all users, and if they happen to have any orders, only include the orders that were created between two dates. But if they don't have orders, or have orders only outside of those two dates, still return the users and do not exclude any users ever.

What I'm doing now:

users = User.all.includes(discount_codes: :orders)

users = users.where("orders.created_at BETWEEN ? AND ?", date1, date2).
or(users.where(orders: { id: nil })

I believe my OR clause allows me to retain users who do not have any orders whatsoever, but what happens is if I have a user who only has orders outside of date1 and date2, then my query will exclude that user.

For what it's worth, I want to use this orders where clause here specifically so I can avoid n + 1 issues later in determining orders per user.

Thanks in advance!

activerecord ruby-on-rails
4 Answers

It doesn't make sense to try and control the orders that are loaded as part of the where clause for users. If you were to control that it'd have to be part of the includes (which I think means it'd have to be a part of the association).

Although technically it can combine them into a single query in some cases, activerecord is going to do this as two queries.
The first query will be executed when you go to iterate over the users and will use that where clause to limit the users found.
It will then run a second query behind the scenes based on that includes statement. This will simply be a query to get all orders which are associated with the users that were found by the previous query. As such the only way to control the orders that are found through the user's where clause is to omit users from the result set.

If I were you I would create an instance method in User model for what you are looking for but instead of using where use a select block:

def orders_in_timespan(start, end)
    orders.select{ |o| o.between?(start, end) }
end

Because of the way ActiveRecord will cache the found orders from the includes against the instance then if you start off with an includes in your users query then I believe this will not result in n queries.

Something like: render json: User.includes(:orders), methods: :orders_in_timespan

Of course, the easiest way to confirm the number of queries is to look at the logs. I believe this approach should have two queries regardless of the number of users being rendered (as likely does your code in the question).

Also, I'm not sure how familiar you are with sql but you can call .to_sql on the end of things such as your users variable in order to see the sql that would be generated which might help shed some light on the discrepancies between what you're getting and what you're looking for.

user_33346900

Option 1: Write a custom query in SQL (ugly).

Option 2: Create 2 separate queries like below...

@users = User.limit(10)
@orders = Order.joins(:discount_code)
  .where(created_at: [10.days.ago..1.day.ago], discount_codes: {user_id: users.select(:id)})
  .group_by{|order| order.discount_code.user_id}

Now you can use it like this ...

@users.each do |user|
  orders = @orders[user.id]
  puts user.name
  puts user.id
  puts orders.count
end

I hope this will solve your problem.

user_29120820

You need to use joins instead of includes. Rails joins use inner joins and will reject all the records which don't have associations.

User.joins(discount_codes: :orders).where(orders: {created_at: [10.days.ago..1.day.ago]}).distinct

This will give you all distinct users who placed orders in a given period of time.

user_29120820 
user  = User.joins(:discount_codes).joins(:orders).where("orders.created_at BETWEEN ? AND ?", date1, date2) + 
        User.left_joins(:discount_codes).left_joins(:orders).group("users.id").having("count(orders.id) = 0")
user_11891600
Related