How do I get this complex query?

Question

company has_many customers
customer has_many messages
customer belongs_to company
company has_many messages
message belongs_to company
message belongs_to customer

A Message can be "sent" or "received" from the column "direction".

What I would like to find is the number of "conversations" per day where a conversation is defined as a customer sending at least one message and receiving at least one message (doesn't have to be in that order) in a single day, divided by the number of paying companies.

How can I get that in active record?

Answer 1

Lets split the query into parts:

1. You need to join the message table on itself on same company id.

Message.joins("LEFT JOIN messages AS m ON messages.company_id = m.company_id")

---

2. Where customer is same for the two joined messages where messages is sent and m is received on the same day

.where("messages.direction = 'sent' AND m.direction = 'received' AND messages.customer_id = m.customer_id AND DATE(messages.created_at) = DATE(m.created_at)")

---

3. You need to get the uniq messages sent and received by every customer everyday to/from the same company.

.select(" DISTINCT (messages.customer_id, messages.company_id, DATE(messages.created_at) )")

---

4. Count Grouping by date created

.group('Date(messages.created_at)').count

---

Final Query:

Message.joins("LEFT JOIN messages AS m ON messages.company_id = m.company_id").where("messages.direction = 'sent' AND m.direction = 'received' AND messages.customer_id = m.customer_id AND DATE(messages.created_at) = DATE(m.created_at)").select(" DISTINCT (messages.customer_id, messages.company_id, DATE(messages.created_at) )").group('Date(messages.created_at)').count