Rails & ActiveRecord: Difficult SQL Query

Question

In my Rails app I allow users to "vote" on events. Some events are one specific date only and some events are ongoing.

I am trying to build a query that renders all the events that either A) a user has not voted on OR B) the ongoing events a user has voted on over 1 month ago.

Event has a "ongoing" boolean column. Event has_many votes. And User has_many votes.

Here's my query to get all the events a user has not voted on. It works.

scope :unvoted, ->(user_id) {
    joins(
         "LEFT OUTER JOIN votes ON votes.event_id = events.id AND " +
         "votes.user_id = #{user_id}"
         ).where("votes.id IS NULL")
    }

I am trying to add the or part of the query. I need to include "ongoing" events that the user has voted on over a month ago. Here's what I tried:

scope :unvoted, ->(user_id) {
    joins(
          "LEFT OUTER JOIN votes ON votes.event_id = events.id AND " +
          "votes.user_id = #{user_id}"
         )
         .where(
                 "votes.id IS NULL OR events.ongoing = ? AND MAX(votes.created_at) < ?", true, 1.month.ago
          )
  }

But this is giving me the error:

PG::GroupingError: ERROR: aggregate functions are not allowed in WHERE

So I tried this:

scope :unvoted, ->(user_id) {
    joins(
          "LEFT OUTER JOIN votes ON votes.event_id = events.id AND " +
          "votes.user_id = #{user_id}"
                            ).
   having('votes.id IS NULL OR events.ongoing = ? AND MAX("votes"."created_at") < ?', true, 1.month.ago).
   group("events.id")
  }

But this is giving me the error:

ERROR: column must appear in the GROUP BY clause or be used in an aggregate function

What's the proper query that I'm looking for?

For complex SQL questions make sure to tag them with the RDBMS and create a good example of the tables and expected result. There are far more followers for the postgres and mysql tags than ruby-on-rails for example and you tend to get better answers. — max, Oct 20 '16 at 00:56
What is your purpose for using MAX? Just get a latest one from matched records, or order records from new to old? — YTorii, Oct 20 '16 at 07:06
@YTorii I need to get the latest one from matched records to see if it was created over a month ago — Jackson Cunningham, Oct 20 '16 at 17:20
You might like to express that second one as "the ongoing events for which there is no vote for the user within the last month". In SQL terms, a simple NOT EXISTS correlated subquery, or maybe an outer join if you wanted to go that route. — David Aldridge, Oct 24 '16 at 21:29

score 0 · Answer 1 · edited May 23 '17 at 12:19

0

EDIT: Seems there is no way of writing OR with HAVING. You can use a subquery(Using 'OR' between HAVING and WHERE clause in MySQL?) or UNION to solve this.

Or to keep things simple you can break this into two queries.

only ActiveRecord solution: https://stackoverflow.com/a/15413611/5213979

edited May 23 '17 at 12:19

Community

1
1

answered Oct 20 '16 at 17:00

Abhishek Kumar

674
1
6
15

Hmmm.. I'm not sure if this will work because it will return all events with a vote older than 1 month ago. But I only want to return events where the most recent vote was over 1 month ago – Jackson Cunningham Oct 20 '16 at 17:23
@JacksonCunningham Ok, realized that. – Abhishek Kumar Oct 20 '16 at 17:49
Can you provide an example of doing this with two queries? I like this approach but not sure how to do it with "OR" – Jackson Cunningham Oct 20 '16 at 17:56

score 0 · Answer 2 · answered Oct 24 '16 at 22:10

Can you try

scope :unvoted, ->(user_id) {
    joins(
      "LEFT OUTER JOIN votes ON votes.event_id = events.id AND " +
      "votes.user_id = #{user_id}"
    ).where(
      "votes.id IS NULL OR events.ongoing = ?", true                      
    ).select('events.*, MAX(votes.created_at) as created_at')
    ).group('events.id')
}

and then in your method, you can do

Event.unvoted.select{|e| e.created_at < 1.month.ago}

Rails & ActiveRecord: Difficult SQL Query

2 Answers2