0

I have many users and each user has multiple projects.

Now, I want to get only one item per user.

@projects = Project.all.where(user_id is unique)

That is project should be fetched only if no other project from the same user was fetched already.

Hope I was clear with my question. I am on Rails 5.

EDIT:

I am using postgres in production.

HighOnRails
  • 43
  • 2
  • 7
  • If I understand correctly you want to list users and only one of their projects. What with users who don't have projects? Is presenting _any_ project acceptable? Or do you want to show a particular project? – Greg Navis Dec 28 '16 at 12:51
  • I dont want to list users. I am doing, @projects = Project.all.where(:status => "Published"), which works fine. But this also returns multiple projects by single user, which i want to limit. hence, i want to list only one published project per user. – HighOnRails Dec 28 '16 at 12:57
  • I see. Thanks for the clarification. – Greg Navis Dec 28 '16 at 15:18

1 Answers1

0

There are many ways in which you can do that. I would recommend the following code as a starting point:

projects_scope = Project.where(:status => "Published")
@projects = projects_scope.where(id: projects_scope.group(:user_id).maximum(:id).values)

Here's how it works:

  1. We'll use the scope of published projects twice so we assign it to a variable to make it more readable.

  2. We group projects by user_id and use maximum to pick the one with the highest ID (you could use minimum or any othe rmethod of picking one row from the group). In more detail:

    a. projects_scope.group(:user_id).maximum(:id) returns a Hash mapping user IDs to project IDs. For example { 12 => 17, 14 => 3 } means for user 12 we choose project 17 and for user 14 we choose project 3. b. We're interested only in project IDs so we get the values in the hash by calling Hash#values).

  3. Knowing the IDs, we query the database for the corresponding projects and assign the result to @projects.

If you'd like to learn some more advanced techniques I recommend you take a look this Stack Overflow question.

Community
  • 1
  • 1
Greg Navis
  • 2,818
  • 10
  • 10
  • I just tried this in development (sqlite) and production(postgres) and it works as expected on both environments. Sincere thanks for your solution and the explanation. Just one question, instead of picking up ids by maximum or minimum, is it possible to pick an id on random? – HighOnRails Dec 28 '16 at 17:46
  • My pleasure, @HighOnRails. You may take a look at the other Stack Overflow post that I linked for generic SQL solutions that you could use via Active Record. I'm not sure whether the extra complexity is worth it in your case. Another totally different approach you can take is do `project_scope.pluck(:user_id, :id)`. It'd return you an array of pairs `[user_id, project_id]` and you should be able to pick one `project_id` for each `user_id` in Ruby in any way you want. PS It's safer to use the same database in all environments so I recommend that you migrate development to PostgreSQL. – Greg Navis Dec 28 '16 at 18:22
  • Yes, you are correct Greg, I will migrate development to Postgres right away. Once again, many thanks for your help. – HighOnRails Dec 29 '16 at 03:21