How to build a dynamic scope in rails

Question

How do i construct a dynamic scope search given a variable-length array of elements to exclude, as in:

class Participant < ApplicationRecord

scope exclude_names, -> (['%name1%', '%name2%', '%name3%', ...]) {
  where.not(Participant.arel_table[:name_search].matches('%name1%').or(
   Participant.arel_table[:name_search].matches('%name2%').or(
     Participant.arel_table[:name_search].matches('%name3%').or(
...
}

but done dynamically as the name_list is of variable length.

Beware that this approach does leave you vulnerable to SQL injection attacks and should not be used if the input originates from the users. The match method doesn't actually have a good way AFAIK to use placeholders and this is one of those places where a SQL string is actually a perfectly acceptable solution. — max
– max, Commented Oct 5, 2023 at 11:18

mechnicov · Accepted Answer · 2023-10-05 08:58:39Z

1

I suggest to use does_not_match method and accumulate AND conditions iterating through excluded names. You also don't need call explicitly class name inside the scope, because it is class method

class Participant < ApplicationRecord
  scope :exclude_names, ->(*names_to_exclude) {
    query = names_to_exclude.reduce(nil) do |q, name|
      condition = arel_table[:name_search].does_not_match("%#{name}%")
      q&.and(condition) || condition
    end

    where(query)
  }
end

After that you can call this scope

Participant.exclude_names('name1')
# SELECT * FROM participants
# WHERE name_search NOT LIKE '%name1%'

Participant.exclude_names('name1', 'name2')
# SELECT * FROM participants
# WHERE name_search NOT LIKE '%name1%'
# AND name_search NOT LIKE '%name2%'

Participant.exclude_names(%w[name1 name2])
# SELECT * FROM participants
# WHERE name_search NOT LIKE '%name1%'
# AND name_search NOT LIKE '%name2%'

Of course you can use OR like in your question, in this case it will be like this

class Participant < ApplicationRecord
  scope :exclude_names, ->(*names_to_exclude) {
    query = names_to_exclude.reduce(nil) do |q, name|
      condition = arel_table[:name_search].matches("%#{name}%")
      q&.or(condition) || condition
    end

    where.not(query)
  }
end

After that you can call this scope, compare with previous queries

Participant.exclude_names('name1')
# SELECT * FROM participants
# WHERE NOT (name_search LIKE '%name1%')

Participant.exclude_names('name1', 'name2')
# SELECT * FROM participants
# WHERE NOT (name_search LIKE '%name1%' OR name_search LIKE '%name2%')


Participant.exclude_names(%w[name1 name2])
# SELECT * FROM participants
# WHERE NOT (name_search LIKE '%name1%' OR name_search LIKE '%name2%')

answered Oct 5, 2023 at 8:58

mechnicov

16.2k5 gold badges48 silver badges69 bronze badges

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

1 Comment

engineersmnky Over a year ago

A few comments: 1) current implementation allows for Participant.exclude_names resulting in where(nil). Would add a guard clause return scope if names_to_exclude.empty?; 2) exclude_names(%w[name1 name2]) results in something similar to LIKE '%["name1","name2"]%'. Adding flatten would solve this. 3) if postgres this would be ILIKE because case sensitive defaults to false.

Siim Liiser · Accepted Answer · 2023-10-05 08:48:40Z

0

Create all the conditions in a loop and then combine them with or-s into your condition.

scope :exclude_names, -> (names) do
  clauses = names.map do |name|
    Participant.arel_table[:name_search].matches(name)
  end
  condition = clauses.inject(:or)
  where.not(condition)
end

answered Oct 5, 2023 at 8:48

Siim Liiser

4,58314 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to build a dynamic scope in rails

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