ActiveRecords Join with conditions: how to join on the same table with different sets of conditions using squeel sifters?

Question

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 

Answer 1

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 ...