0

I'm trying to sort users based on their most recent response to a certain question in a survey using Rails 5, PostgeSQL 9.4.5

So far I've got:

User.includes(responses: [answer: :question]).where(questions: {id: X}).order(...)

Not sure what to put in the order. The responses all have numerical 'scores' representing which answer it is. I'm imagining something at the end like: 

.order("answers.score ASC")

But I'm struggling to get the two to attach. I only want to sort the Users by their most recent answer to that specific question. (They can take the survey multiple times) I'm assuming I need to set a string function in some SELECT, but I'm struggling to wrap my head around it.

Any help is appreciated!

Rockster160
  • 1,579
  • 1
  • 15
  • 30

1 Answers1

1

You can print the actual SQL of the rails query like this:

User.includes(responses: [answer: :question]).where(questions: {id: X}).to_sql

Then you can order by the right table.field (find the table name in the SQL returned by to_sql) and the field in db/schema.rb

It should be a created_at. User...order('responses.created_at DESC')

UPDATE

But this will sort all responses and not users by their last response on question, as you've commented below.

In this case you have to:

  1. group the users by their responses

  2. calculate the last response(MAX(user_responses.created_at)) for each user

  3. sort the users by last response

Something like this:

User
  .includes(responses: [answer: :question])
  .where(questions: {id: X})
  .group('users.id')
  .order('MAX(responses.created_at) DESC')
Marius Zaharie
  • 61
  • 1
  • 4
  • That works on the basic idea, however, that sorts by ALL `responses` from each user. I want to filter the `responses` down to a specific question, and then order the Users only by those- rather than all of them. – Rockster160 Dec 07 '16 at 20:28
  • Wow! That actually worked. I had to add a `references(:responses)` and a few more values to the `.group`, but otherwise it was a success! – Rockster160 Dec 07 '16 at 21:36
  • 1
    The only issue now is that it loses the Users that have not yet answered the Survey. Do you know of a way to include that? That was the original goal for using the `includes`, but it might make this query a little crazy. – Rockster160 Dec 07 '16 at 21:37
  • Try to replace the `includes` with a custom [`joins` using LEFT OUTER JOIN](http://stackoverflow.com/a/24358856/5802769) – Marius Zaharie Dec 07 '16 at 22:00
  • Not having much luck with that. Running into some syntax errors writing the pure SQL out. Doesn't `includes` simply do a basic `LEFT OUTER JOIN` under the hood? – Rockster160 Dec 07 '16 at 22:33
  • As I mentioned in the answer, you can check the SQL code _under the hood_ with the `to_sql` method – Marius Zaharie Dec 07 '16 at 22:39
  • Right. I've done that, which is why I was saying- if I were to do a custom JOINS, I would do it the same way that `includes` does it, so what difference would the custom one make or need to be? – Rockster160 Dec 07 '16 at 22:42
  • > I wouldn't recommend this for has_many relationships, but it works great for has_one. – Marius Zaharie Dec 07 '16 at 22:49
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/130053/discussion-between-marius-zaharie-and-rockster160). – Marius Zaharie Dec 07 '16 at 22:49