CODEWITHSUNDEEP
ruby-on-rails
user3023421
user3023421

Reputation: 363

Combine a join query with a normal condition

Users have a main category and multiple sub-categories

I would like to get all users who belong to a category, regardless if it is their main or sub.

@users = User.joins(:sub_categories).where('sub_category_id = ? OR type = ?', @sub_category.id, "User::#{category}User").page(params[:page])

A user's main category is also their STI type.

The query works, however I am getting duplicate results when trying to include the user's main type.

The query that is generated is:

User Load (0.0ms)  SELECT  "users".* FROM "users" INNER JOIN "user_sub_categories_users" ON "user_sub_categories_users"."user_id" = "users"."id" INNER JOIN "user_sub_categories" ON "user_sub_categories"."id" = "user_sub_categories_users"."sub_category_id" WHERE "users"."deleted_at" IS NULL AND (sub_category_id = 1 OR type = 'User::ModelUser')  ORDER BY "users"."created_at" DESC LIMIT 20 OFFSET 0

EDIT: A user can not belong to a sub-category if its their main, so its safe to simply join the two conditions together

Upvotes: 0

Views: 43

Answers (1)

rossettistone
rossettistone

Reputation: 364

Because there is more than one group for each user, your join is creating multiple rows for each user, e.g.:

User    Group
-------|------
UserA  |GroupA
UserA  |GroupB
UserB  |GroupA
UserC  |GroupA
UserC  |GroupB

Three users, but five rows!

You can safely add a .uniq on the end of your query if you're just interested in the distinct users. In the context of an ActiveRecord query, .uniq will be converted to SQL's DISTINCT().

Upvotes: 2

Related Questions