0

I'm working on a project where I need to scope a model by the number of associated models under a where clause. This is the best I could come up with:

Subscription.where('message_cap > ?', TextMessage.where('created_at > ?', DateTime.now.beginning_of_month).where(subscription: subscription).count)

But, it doesn't work because subscription is undefined. Is there any way in a rails query I can reference the model currently being hit by postgres? I think this is the SQL I'm looking for:

SELECT * FROM 'subscriptions' WHERE 'subscription.message_cap' > (SELECT COUNT(*) FROM 'text_messages' WHERE 'text_messages.subscription_id' = 'subscription.id' AND 'text_message.created_at > BEGINNINGOFMONTH')

This is a really, really performance critical part of my application, so I need it to run in one query if I can.

EDIT

By the way, I'm not sure if the SQL query above is valid. I just put it there to convey the idea of what I was looking for.

carpamon
  • 6,515
  • 3
  • 38
  • 51
Dylan Karr
  • 3,304
  • 4
  • 19
  • 29

2 Answers2

1

Try with the following:

Subscription.select('subscriptions.id').
  joins(:text_messages).
  where('messages.created_at > ?', date).
  group('subscriptions.id').
  having('COUNT(*) > subscriptions.message_cap')

Being date the parameter of the query.

Note that each Subscription object in the result will contain only the id of the subscription. To retrieve the full objects you will need a query like the following:

Subscription.find(ids)

If it is a critical part of your application you should test the query using your favorite test framework.

carpamon
  • 6,515
  • 3
  • 38
  • 51
  • Maybe use `.select('subscriptions.*')` ? – Wizard of Ogz May 02 '14 at 14:23
  • Part of the problem is that the count is unique to each model as well. That's why I have `message_cap`. It's a column on the subscription model. – Dylan Karr May 02 '14 at 14:23
  • subscriptions.* will not work in PostgreSQL (at least) as you can't select columns which are not in the group by clause. – carpamon May 02 '14 at 14:25
  • @DylanKarr what does message_cap stands for? you mean each subscription has a differente count limit? – carpamon May 02 '14 at 14:27
  • It's an integer. It's just the per-basis limit on each subscription as to how many text messages they can receive. – Dylan Karr May 02 '14 at 14:28
  • in that case you can change having('COUNT(\*\) > ?', count) for having('COUNT(\*\) > subscriptions.message_cap') – carpamon May 02 '14 at 14:29
  • I take that back. It doesn't return anything when there's any text messages, even when subscriptions.message_cap is 10. – Dylan Karr May 05 '14 at 13:30
  • Also, the where clause has to be modified to allow for null created_at when there are no text_messages, and the join has to be rewritten as a left join so subscriptions with no text_messages will show up. – Dylan Karr May 05 '14 at 13:31
0
query = "SELECT * FROM 'subscriptions' WHERE 'subscription.message_cap' > (SELECT COUNT(*) FROM 'text_messages' WHERE 'text_messages.subscription_id' = 'subscription.id' AND 'text_message.created_at > BEGINNINGOFMONTH')"
Subscription.find_by_sql(query)

If that is not fast enough then you should look into caching the text messages count for each subscription. This can be done by creating a view or adding a counter column to the subscriptions table. The view is the better solution, but more involved to implement.

Update

subscriptions_table = Subscription.table_name
text_messages_table = TextMessage.table_name
foreign_key = TextMessage.reflect_on_association(:subscription).foreign_key
query = "SELECT * FROM '#{subscriptions_table}' WHERE '#{subscriptions_table}.message_cap' > (SELECT COUNT(*) FROM '#{text_messages_table}' WHERE '#{text_messages_table}.#{foreign_key}' = '#{subscriptions_table}.id' AND '#{text_messages_table}.created_at > #{Date.current.beginning_of_month}')"
Subscription.find_by_sql(query)
Wizard of Ogz
  • 12,543
  • 2
  • 41
  • 43