I have two activerecords classes, User and UserSkill. UserSkill has a squeel sifter, as shown below:
class User < ActiveRecord::Base
has_many :user_skills
end
and
class UserSkill < ActiveRecord::Base
belongs_to :user
belongs_to :skill
sifter :with_skill_id_and_value_of_at_least do |params|
if params[1].present?
skill_id.eq(params[0]) & value.gteq(params[1])
else
skill_id.eq(params[0])
end
end
end
I am implementing the search at the moment, and have this in the controller:
def search
users = User.skip_user(current_user)
if params[:user_skills].present?
params[:user_skills].each do |user_skill|
#user_skill is an array where [0] is the id, and [1] the minimum value.
#if [1] is empty, it will default to 0 in the sifter
users = users.joins(:user_skills).where do
{
user_skills: sift(:with_skill_id_and_value_of_at_least, user_skill)
}
end
end
end
end
my problem is that basically, when the relation is applied, I end up with the following sql:
SELECT "users".* FROM "users"
INNER JOIN "user_skills" ON "user_skills"."user_id" = "users"."id"
WHERE "user_skills"."skill_id" = 10
AND (("user_skills"."skill_id" = 11 AND "user_skills"."value" >= 2))
which is not what I want:
indeed, I want the users that have a user_skill with a skill_id of 10 AND that have also a user_skill with a skill_id of 11 and a value higher than 2.
ActiveRecords seems to "chain" the conditions, and I always end up with an empty relation as a UserSkill cannot have an id of 10 and 11 in the same time!
I would like to somehow differentiate between the first and the second condition so that I get the users with the user_skills that satisfy the first condition and the second condition in a sequence, and not for the same user_skill row, and am not sure how to achieve that!
I think aliases would be the way to go, but how can i specify a different alias for each of the user_skill I want to reference using squeel or ActiveRecord syntax?
I would imagine that my question is quite similar to this one, however since I'm using a loop I don't think I can use the proposed solution!
edit: I also found this on the squeel github, but not sure how to apply it to my particular use case: Need a way to alias joins for multiple joins on the same relation
LAST EDIT:
I've written a solution in sql that works, but as you can see it is very ugly:
params[:user_skills].each_with_index do |user_skill,index|
users = users.joins(",user_skills as u#{index}")
.where("u#{index}.skill_id= ? AND u#{index}.value >= ?"
,user_skill[0].to_i, user_skill[1].to_i)
end
This is the case where you need to take the intersection of sets of users. In other words, you need to apply the "AND" to the sets of users, not to the where
conditions. When you chain where
conditions, you are "anding" the conditions, which is why you see the results you're seeing.
You can do this either in Ruby or in SQL, using the basic technique described in Intersection of two relations. For the sake of simplicity, I'll illustrate both here using two sets and leave the extension to more than two sets and to squeel to you.
First, let's define two sets:
set1 = User.joins(:user_skills).where('user_skills.skill_id = 10')
set2 = User.joins(:user_skills).where('user_skills.skill_id = 11 and user_skills.value >= 2')
Note: Your example text said "value higher than 2" but your code expressed "value greater than or equal to 2". I obviously used the latter interpretation above.
If you want to take the intersection in Ruby, you take the intersection of the resulting arrays, using the &
operator, as follows:
set1 & set2
In this case, you are doing two separate SQL queries and taking the intersection of the results.
If you want to take the intersection using SQL, you need to compose the SQL, which can be done using the technique described in this answer from @MikDiet to the aforementioned question:
User.connection.unprepared_statement do
User.find_by_sql "#{set1.to_sql} INTERSECT #{set2.to_sql}"
end
In this case, you are performing a single query, although that query is obviously doing "more work". You should read the original answer to understand the reason for use of unprepared_statement
.
I'll leave the extension to squeel and greater than two sets to you except to note that you can use the SQL INTERSECT
operator with more than two operands as follows:
query1 INTERSECT query2 INTERSECT query3 ...