CODEWITHSUNDEEP
ruby-on-rails
Aydar Omurbekov
Aydar Omurbekov

Reputation: 2117

Filtering items by multiple fields

Filtering items by title, created_at dates, but one of them would be empty and it raises an error, how could I handle that? 

where("country_translations.title ILIKE ? AND country_translations.created_at > ? AND country_translations.created_at < ?", "%#{search[:title]}%", search[:created_at_gt], search[:created_at_lt])

Upvotes: 0

Views: 117

Answers (4)

Md. Farhan Memon
Md. Farhan Memon

Reputation: 6121

you can always use scopes in such case, they come handy almost everywhere

scope :filter_by_title, -> (title) { where('title ILIKE ?', "%#{title}%") if title.present? }
scope :filter_by_created_at_lt, -> (date) { where('created_at < ?', date) if date.present? }
scope :filter_by_created_at_gt, -> (date) { where('created_at > ?', date) if date.present? }

Then you can restructure the query as

Model.filter_by_title(search[:title])
     .filter_by_created_at_lt(search[:created_at_lt])
     .filter_by_created_at_gt(search[:created_at_gt])

Upvotes: 1

Francesco Boffa
Francesco Boffa

Reputation: 1402

It really depends on how you want to handle that.

First of all, I will decompose the query into multiple wheres, that default to an AND operation. This is for readability:

Model.where("country_translations.title ILIKE ?", "%#{search[:title]}%")
     .where("country_translations.created_at > ?", search[:created_at_gt])
     .where("country_translations.created_at < ?", search[:created_at_lt])

You could either pass default values using the || operator, like this:

Model.where("country_translations.title ILIKE ?", "%#{search[:title] || ''}%")
     .where("country_translations.created_at > ?", search[:created_at_gt] || Time.now)
     .where("country_translations.created_at < ?", search[:created_at_lt] || Time.now)

or you can split this into three filters that have to be applied only when needed:

query = Model.all
query = query.where("country_translations.title ILIKE ?", "%#{search[:title]}%") if search[:title]
query = query.where("country_translations.created_at > ?", search[:created_at_gt]) if search[:created_at_gt]
query = query.where("country_translations.created_at < ?", search[:created_at_lt]) if search[:created_at_lt]

# query now is filtered only with present filters.

Upvotes: 1

Eyeslandic
Eyeslandic

Reputation: 14900

You can chain your where clauses quite easily.

@results = Model.all
@results = @results.where('country_translations.title ILIKE ?', "%#{search[:title]}%") if search[:title].present?

If you're using Postgres you can also use a regex instead of ILIKE to get rid of this %#{}% stuff.

@results = @results.where('country_translations.title ~* ?', search[:title]) if search[:title].present?

and so on for your other fields.

Upvotes: 1

Avdept
Avdept

Reputation: 2289

You can do something like this:

YourModel.where(filter_by_translations_title).where(filter_by_translations_created)

def filter_by_translations_title
  ['country_translations.title ILIKE ?', search[:title]] if search[:title].present?
end
#...add rest of methods here

Chaining #where will join all queries via AND. This way will allow you add as many sub quesries as you want, and control their behavior.

Upvotes: 1

Related Questions