Derk153 Derk153 - 3 months ago 15
Ruby Question

Rails 5. Wrong result in scope chaining

I have next models

user.rb

class User < ApplicationRecord
has_many :user_tags, dependent: :destroy
has_many :tags, through: :user_tags
has_many :children, foreign_key: 'parent_id', dependent: :destroy


tag.rb

class Tag < ApplicationRecord
has_many :user_tags, dependent: :destroy
has_many :users, through: :user_tags


user_tag.rb

class UserTag < ApplicationRecord
belongs_to :tag
belongs_to :user


child.rb

class Child < ApplicationRecord
belongs_to :parent, class_name: 'User'


In model user I have several scopes.

Select users, associated with ALL of required tags:

scope :search_by_tags, -> (ids) {
joins(:tags).where(tags: {id: ids}).having('COUNT(tags.id) >= ?', ids.count).group('users.id')
}


Which, generates next SQL request

SELECT "users".* FROM "users" INNER JOIN "user_tags" ON "user_tags"."user_id" = "users"."id" INNER JOIN "tags" ON "tags"."id" = "user_tags"."tag_id" WHERE "tags"."id" IN (17, 37, 70, 8, 11, 3, 91, 64) GROUP BY users.id HAVING (COUNT(tags.id) >= 8)


Other scope select users, associated with children in specific ages

scope :children_in_ages, -> (age_ranges) {
joins(:children).where(children_ages_query(age_ranges))
.where.not(children: {gender: -1}).group('users.id')
}

def self.children_ages_query(age_ranges)
child_table = Child.arel_table
range_conditions = age_ranges.map { |r| child_table[:birth_date].in(r) }
range_conditions.inject(range_conditions.shift, &:and).to_sql
end


where
age_ranges
is array of dates. This scope generates next SQL query:

SELECT "users".* FROM "users" INNER JOIN "children" ON "children"."parent_id" = "users"."id" WHERE ("children"."birth_date" BETWEEN '2016-02-29 08:35:00.322919' AND '2016-08-31 08:35:00.327283') AND ("children"."gender" != $1) GROUP BY users.id


Separately, scopes are working correct, but when I'm merging scopes, I have wrong result.

User.children_in_ages(a).search_by_tags(ids)
User Load (85.2ms) SELECT "users".* FROM "users" INNER JOIN "children" ON "children"."parent_id" = "users"."id" INNER JOIN "user_tags" ON "user_tags"."user_id" = "users"."id" INNER JOIN "tags" ON "tags"."id" = "user_tags"."tag_id" WHERE ("children"."birth_date" BETWEEN '2016-02-29 08:35:00.322919' AND '2016-08-31 08:35:00.327283') AND ("children"."gender" != $1) AND "tags"."id" IN (17, 37, 70, 8, 11, 3, 91, 64) GROUP BY users.id HAVING (COUNT(tags.id) >= 8)


Any ideas what could be wrong?

Answer

I've found solution for my issue.

Proper way to select users with specific tags:

models/user.rb

scope :search_by_tags, -> (ids) { where(id: UserTag.users_with_tags(ids)) unless ids.blank? }

models/user_tag.rb

scope :users_with_tags, -> (ids) {
  select(:user_id).where(tag: ids).having('COUNT(user_id) >= ?', ids.count).group('user_id')
}

In this way, chaining with other scopes is working correct