-1

In my rails application, at some point, I query my model simply. I want to query customers order information like how many orders were given by this customer within three months.

Just now, I query the model in that way:

@customer = Customer.all

customer.rb

class Customer < ApplicationRecord
  audited
  has_many :orders
end

And customer may have orders.

order.rb

class Order < ApplicationRecord
  audited
  belongs_to :customer
end

What I would like to do is to query customers model and to inject aggregate function result to every customer records.

EDİT

I tried to simulate every solution but couln't achieve.

I have the following query in mysql.

How do I need to code in ruby with activerecord to create that query ?

SELECT 
(SELECT 
        COUNT(*)
    FROM
        orders o
    WHERE
        o.customer_id = c.id
            AND startTime BETWEEN '2017.12.04' AND '2018.01.04') AS count_last_month,
(SELECT 
        COUNT(*)
    FROM
        orders o
    WHERE
        o.customer_id = c.id
            AND startTime BETWEEN '2017.10.04' AND '2018.01.04') AS count_last_three_month,
    c.*
FROM
customers c;

How can I achieve that?

Thanks.

Hilmi Yamcı
  • 463
  • 8
  • 20
  • You should use group by – Vishal Jan 04 '18 at 11:31
  • Can you try this query `Customer.joins(:orders).group("orders.customer_id").where('created_at > ? and created_at < ?', Date.today - 3.months, Date.today).order("count(orders.customer_id) desc")` – Vishal Jan 04 '18 at 11:37
  • @Hilmi Yamci, Are you looking for particular customer's order count within 3 months? – sam Jan 04 '18 at 11:45
  • Yes actually, I'm using pagin when I query customer, in that case, I want to aggregate only those customers orders counts – Hilmi Yamcı Jan 04 '18 at 11:50
  • @HilmiYamcı, I have answered so please have a look. – sam Jan 04 '18 at 12:04
  • I have just looked, I think it didn't help me. Actually, I will add another comment to the question just now. Can you look at it please ? – Hilmi Yamcı Jan 04 '18 at 12:21
  • Answering your question stated in “edit”: there should be 2 different queries, both like shown in my answer. – Aleksei Matiushkin Jan 04 '18 at 13:04

2 Answers2

2
Customer.
  joins(:orders).
  group('customers.id').
  where('orders.created_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)')
  select('sum(orders.id), customers.*')
Aleksei Matiushkin
  • 119,336
  • 10
  • 100
  • 160
-1

As my understanding of you question. I have this solution for you question. Please have a look and try it once. In below query, 'includes' used to solve N+1 problem.

Customer.includes(:orders).where('created_at BETWEEN ? AND ?', Time.now.beginning_of_day, Time.now.beginning_of_day-3.months).group_by{|c|c.orders.count}

If you are looking for particular customer's order count then you can try this one.

@customer.orders.where('created_at BETWEEN ? AND ?', Time.now.beginning_of_day, Time.now.beginning_of_day-3.months).group_by{|c|c.orders.count}
sam
  • 372
  • 2
  • 12