[SOLVED] Find users who have made two or more orders with ActiveRecord

Find users who have made two or more orders with ActiveRecord

With the following Rails models:

    class User < ActiveRecord::Base
      has_many :orders
    end

    class Order < ActiveRecord::Base
      belongs_to :user
    end

I want a query that finds users who:

placed 2 or more orders in the past, and in the same query
filter by users who placed orders over the month

I tried something like this:

    start_date = 1.month.ago

    users = User.joins(:orders)
      .where('orders.created_at >= ?', start_date)
      .having('COUNT(orders.user_id) > 1')
      .group('orders.user_id')

But it didn't work. Please help. I can't work out from the docs how to use the having clause, or if it's the right approach. Let me know if you need more info. Thanks!

Update: Here's the sql query generated by Rails:

    SELECT user_id 
    FROM "users" 
    INNER JOIN "orders" 
    ON "orders"."user_id" = "users"."id" 
    WHERE (orders.created_at >= '2017-05-02') 
    GROUP BY orders.user_id 
    HAVING COUNT(orders.user_id) > 1

Solution

You can query to find all the users with more than 2 orders in the past along with their latest order's created_at. And then select users with their latest order's created_at less than a month. Like this:

repeat_users = User.joins(:orders).group("(users.id) having count(users.id) > 2").select("users.*", "max(orders.created_at) as last_order_time")

past_month_users = repeat_users.select{|user| user.last_order_time > Time.now - 1.month}