Rails SQL QUERY Like

Question

I've some problem with an SQL Query

I've users which have skills

  def skills=(value)
    super(value.reject(&:blank?).join("|")) if !value.nil?
   end

  def skills
   super.present? ? super.split("|") : []
  end

Here is my problem. When I query on this field with "like", word order matter. How can I avoid that ?

User.where("skills LIKE '%car%driving_license%'")

Each user with skills which at least contain car and driving_license

User.where("skills LIKE '%driving_license%car%'")

Each user with skills which at least contain car and driving_license but in a different order

I want to be able to avoid this order problem. Any help ?

One option would be to split the skills string in your Ruby code, and then check each skill against a list of accepted skills, e.g. WHERE skill1 IN ('driving_license', 'car') AND skill2 IN ('driving_license', 'car') AND ... — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 21, 2016 at 14:35

Andy Gauge · Accepted Answer · 2016-04-21 16:14:24Z

1

If you sort the skills you can know the order and assemble a like query that will match both of them. You can create a migration to sort the existing skills.

def skills=(value)
  super(value.reject(&:blank?).sort.join("|")) if value
end

rails g migration sortUserSkills

User.where.not(skills: nil).each do u
  u.skills = u.skills
  u.save
end

answered Apr 21, 2016 at 16:14

Andy Gauge

1,4282 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thounder Over a year ago

This is a good idea, I'll try it and come back if it's work! Thanks

Joe Essey · Accepted Answer · 2016-04-21 14:36:03Z

1

LIKE + OR queries are tough if my memory serves me. You can try ARel, but I think you lose LIKE.

2 things I can think of:

Do the queries separately and combine results.
Use something like this which may not work:

conditions = ['%driving_license%car%', '%car%driving_license%'] User.where("skills LIKE ?", conditions)

answered Apr 21, 2016 at 14:36

Joe Essey

3,54810 gold badges43 silver badges71 bronze badges

Collectives™ on Stack Overflow

Rails SQL QUERY Like

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related