1

I have a User-Group many-to-many association with a join table (group_partecipations) which also has an additional "status" column which takes the following values:

  • "owner" (gets set automatically when there is no other record for a given group_id and user_id pair: it means that that user is the creator/owner for that group)

  • "pending" (this is the default value that is set when a user joins a group but they are not the creator, meaning they are waiting for the group creator to accept them)

  • "accepted" (when the creator accepts the pending user)

I understand that for DB normalization rules I should extract this column in another table but for now I'll leave it like that.

So.. now, I'm trying to create a query returing, for a given user, all the groups he is owner of and together with that the users for that group with a "pending" state.

Although I found a solution, it felt very complicated and I'm really not sure it's a good way so I would like some advice.

Here's what I came up with:

First I find all groups where user is owner:

groups_where_owner = u.groups.where(group_partecipations: {status: "owner" })

# u.groups would return all groups: 
# either those in which user is owner and those where he is pending or has been accepted

And then for each of his own groups I return an array made of the group itself and the "pending" users:

groups_where_owner.map { |g| 
      [ g, 
        User.includes(:group_partecipations).where
        (:group_partecipations => {:group_id => g.id, status: "pending" })
      ]
}

but as I said, this feels dirty and too much "handmade" and I am trying to understand if I have not missed an important part in ActiveRecord in order to deal with this situation.

Also, when I convert my result into a JSON ojbect it has an additional level of nesting, which I would like to avoid.

So.. is there a better way? Or, what is the correct way?

Thanks in advance for your help.

EDIT: the point of my question is: is there a way to modify my AR query in order to filter out (or avoid including in the first place) all the users who are not "pending" ("owner"s and "accepted"s) so to NOT having to later iterate on the query result to collect those "pending"s" out ?

Redoman
  • 3,059
  • 3
  • 34
  • 62

1 Answers1

1

This a total acceptable solution.

However it can be improved to make less queries to the db. At the moment you perform a separate query for each owned_group. IO Operations are multiple times slower than cpu Operations. So it is a better strategy to load all the necessary data at once and then process it.

Group.includes(group_participations: :user)
  where(group_participations: {status: "pending" }).
  where(id: user.groups.where(group_participations: {status: "owner" }).ids).
  map { |g| [g, g.users]}
dre-hh
  • 7,840
  • 2
  • 33
  • 44
  • Thanks, but the point of my question is: is there a way to filter out from the query (or avoid including in the first place) all the users who are not "pending" ("owner"s and "accepted"s) so to NOT have to later iterate on the query result to collect those "pending"s" out ? I'm trying to learn AR queries so I'd like to first understand how to squeeze the max out of it (getting exactly what I need from the DB) in case I am not fully doing it yet.. After that I will head for optimization (and thanks already for providing me with some useful insight about that too). – Redoman Jan 17 '15 at 02:12
  • Also I don't understand the reason behind the include for nested associations in `(group_participations: :users)`? AR gives an error about that saying that there is no "users" association on "group_partecipation". If I type the nested association in singular form ":user" there is no error, but I don't know what that means.. – Redoman Jan 17 '15 at 03:31
  • Plz add the exact relationship definitions to the question. I did not run the query, but it should include only the required Users as you say. I'll check this in detail tomorrow – dre-hh Jan 17 '15 at 10:34
  • Edited the answer. The joins part is not necessary, as ActiveRecord can figure it out of `:includes` only. Also the mistake was `(group_participations: :users)`, it should be `:user` (the name, of association in the group_participation model). Check out the log produced by the query, it will include only the `pending` users and none of the others, which is achieved by the `where(group_participations: {status: "pending" })` query – dre-hh Jan 18 '15 at 16:11