Combine different conditions on a Active Record via Arel

Question

I have a table SubscriberProperty which has columns (name, type, account_id, category, mask, contact , mode )

How can i write a Arel condition on the table which takes into account user input like if user has entered name then i should filter on name otherwise not

subs = SubscriberPropertyType.arel_table
SubscriberProperty.where(sub[:name].eq(name))

if the user has entered mode then it should also filter on mode

mode = {mode entered by user}
SubscriberProperty.where(sub[:mode].eq(mode))

How can i combine above two conditions based on the fact it depends whether the user has entered the particular field or not

Answer 1

You can keep chaining your conditions, if this is in a controller I usually do it like this:

name = params[:name]
mode = params[:mode]

scope = SubscriberProperty.joins(:subscriber_property_type)
scope = scope.where(subscriber_property_type: {name:}) if name
scope = scope.where(subscriber_property_type: {mode:}) if mode

@subscriber_properties = scope

Arel seems unnecessary, but it is exactly the same logic, could be useful if you're planning on using other operators than eq:

scope = SubscriberProperty.joins(:subscriber_property_type)
type  = SubscriberPropertyType.arel_table

scope = scope.where(type[:name].eq(name)) if name
scope = scope.where(type[:mode].eq(mode)) if mode

@subscriber_properties = scope

Answer 2

You want to filter your SubscriberProperty table based on user input, specifically the name and mode columns. But you only want to apply these filters if the user has actually provided a value for them.

First, we get a reference to our table:

subs = SubscriberProperty.arel_table

We'll create a base query that we'll add conditions to only if the user has provided certain inputs.

Start with all SubscriberProperties

query = SubscriberProperty.all

Next, we add conditions. If the user has given us a name, we add a name filter. If they've given us a mode, we add a mode filter.

If the user provided a name, add a filter for the name

if user_provided_name
  name_condition = subs[:name].eq(user_provided_name)
  query = query.where(name_condition)
end

If the user provided a mode, add a filter for the mode

if user_provided_mode
  mode_condition = subs[:mode].eq(user_provided_mode)
  query = query.where(mode_condition)
end

user_provided_name and user_provided_mode are variables that should contain the values input by the user.

Finally, Now we can execute this query to get the results:

This will execute the query we've built with all the conditions

results = query.load