0

I am using groupdate gem for grouping the records with date/time. I want to group the records and select the users with having clause and then group the records with date/month/time. But its not giving proper result which i want. eg: 

User.group_by_month(:created_at).count # gives proper result but
User.joins("LEFT JOIN devices ON devices.user_id=users.id").group("users.id").having("COUNT(devices.id)=0").count #gives all users in hash while i want only count for the months.

I am using this for showing charts using chartkick gem.

Any help will be appreciated.

jbmyid
  • 1,985
  • 19
  • 22
  • try `User.joins("LEFT JOIN devices ON devices.user_id=users.id").group("users.id").having("COUNT(devices.id)=0").count.length` – Sonalkumar sute Feb 05 '15 at 06:55
  • I cant as i just have to forward this result to the chartckick, and the code i have written is generic for all. – jbmyid Feb 05 '15 at 06:58

1 Answers1

0

Finally i got workaround for it using 2 queries.

user_ids = User.joins("LEFT JOIN devices ON users.id=devices.user_id").group("users.id").having("count(devices.id)=0").pluck("users.id")

User.where(id: user_ids).group_by_month("created_at").count

Another way was to include the first query in sub-query.

User.where("id IN (SELECT u.id FROM users u LEFT JOIN devices ON u.id=devices.user_id GROUP BY us.id HAVING count(devices.id)=0)").group_by_month("created_at").count
jbmyid
  • 1,985
  • 19
  • 22