1

Context: I am using Active Admin and would like to define a scope to show me any Users with more than 5 orders in the next week. This is a Postgres DB in a Rails app.

User.where(vetting_status: ["Enabled"]).joins(:orders).where('orders.date >= ? AND orders.date <= ?', Date.today, Date.today + 6.days).select('users.first_name, COUNT(orders.id) AS order_count').group('users.first_name').having('order_count > 5').order('order_count desc')

However, this gives me the following error when I try to run it: ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "order_count" does not exist

The issue is with the .having('order_count > 5') as I can run the query if I remove that part. However, that's the crucial part and I'm not sure what I'm getting wrong. I've tried .having('gardener.order_count > 5') and .having('orders.order_count > 5') but neither of those worked either. Thanks in advance for help. P.S. I have searched a fair few answers and couldn't find a solution.

Khaled McGonnell
  • 27
  • 6

1 Answers1

0

The HAVING clause is evaluated before the SELECT - so the server doesn't yet know about that alias.

Instead of COUNT(orders.id) AS order_count you should do count(orders) as order_count You can look around here
I have tried so far below and its working, hopefully it will help you.

User.where(vetting_status: ["Enabled"]).joins(:orders).where('orders.date >= ? AND orders.date <= ?', Date.today, Date.today + 6.days).group('users.first_name').having('count(orders) > 5').order('count(orders) desc').select('users.first_name')

Alternatively you should do as with your above query: -

count(orders) as order_count

This also means the it will get total orders of user as order count. like this: -

User.where(vetting_status: ["Enabled"]).joins(:orders).where('orders.date >= ? AND orders.date <= ?', Date.today, Date.today + 6.days).select('users.first_name, count(orders) as order_count').group('users.first_name').having('count(orders) > 5').order('order_count desc')

Hence both answers are working without any error. :D

Community
  • 1
  • 1
Anand
  • 6,457
  • 3
  • 12
  • 26
  • Still no joy. I get `User.where(vetting_status: ["Enabled"]).joins(:orders).where('orders.date >= ? AND orders.date <= ?', Date.today, Date.today + 6.days).select('users.first_name, count(orders) as order_count').group('users.first_name').having('order_count > 5').order('order_count desc') ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "order_count" does not exist ` – Khaled McGonnell May 23 '18 at 10:12
  • @KhaledMcGonnell Hmm sorry, i got the same error at my console, and i figured out that its issue with having clause `having('count(orders) > 5')` and its working fine, and i think having clause doesn't accept the alias coumn name. – Anand May 23 '18 at 10:18
  • @KhaledMcGonnell `User.where(vetting_status: ["Enabled"]).joins(:orders).where('orders.date >= ? AND orders.date <= ?', Date.today, Date.today + 6.days).select('users.first_name, count(orders) as order_count').group('users.first_name').having('count(orders) > 5').order('order_count desc')` it will work fine. try this and let me know the same. – Anand May 23 '18 at 10:20
  • @KhaledMcGonnell Well today i came to know this also, that alias coulmn name can't be used with having clause check out this: - https://stackoverflow.com/questions/2068682/why-cant-i-use-alias-in-a-count-column-and-reference-it-in-a-having-clause/2068705 – Anand May 23 '18 at 10:26
  • That works and that's very useful to know the alias doesn't work. Thank you! – Khaled McGonnell May 23 '18 at 11:21