Chain Rails .where to choose if ANY true

Question

I have an issue with a conditional query I've written in a Rails application. Currently I have Stores being queried if certain locations (location_north, location_south, etc) are true and if certain types (type_dining, type_unique, etc) are true, but I instead want it to query them if any of those are two, not ALL of them are true.

For example, if the Lead has location_north and location_east set as true, I want to query all Stores with location_north OR location_east, the Store wouldn't need both set to true.

Same for the types, if a Lead has a store_type_unique true and a store_type_dining true, I want to query for all Stores that have a type_unique OR type_dining true, the Store wouldn't need both set to true.

I know there are issues with how my database is organized, but is there anyway to set this up with the current build? Thanks for any guidance, it's appreciated!

    def build_filters_obj
        filters = []
        filters.push 'location_north' if @lead.location_north
        filters.push 'location_east' if @lead.location_east
        filters.push 'location_south' if @lead.location_south
        filters.push 'location_west' if @lead.location_west
        filters.push 'location_other' if @lead.location_other
        
        return filters
    end

    def perform(lead_id)
        @lead = Lead.find(lead_id)

        lead_email = ValidEmail2::Address.new(@lead.email)
        UserNotifierMailer.send_signup_email(@lead).deliver if lead_email.valid?

        @stores = Store.all
        @stores = @stores.where.not(email: [nil, ''])

        n = @lead.guests_total.to_i
        @stores = @stores.where("capacity_min <= ? AND capacity_max >= ?", n, n)
        
        @stores = @stores.where(:type_unique => true) if @lead.store_type_unique
        @stores = @stores.where(:type_dining => true) if @lead.store_type_dining
        @stores = @stores.where(:type_hotel => true) if @lead.store_type_hotel
        
        filters = build_filters_obj
        filters.each do |filter|
            @stores = @stores.where(filter.to_sym => true)
        end

        @stores = @stores.or(Store.where(:receive_all => true))

        @stores.each do |store|
            store_email = ValidEmail2::Address.new(store.email)
            UserNotifierMailer.send_lead_email(store, @lead).deliver if store_email.valid?
        end
    end

Answer 1

A very quick change would be to rewrite each of these lines:

@stores = @stores.where(:type_unique => true) if @lead.store_type_unique

to this:

@stores = @stores.or(Store.where(:type_unique => true)) if @lead.store_type_unique

And the same for the line inside your filters loop.

From there, write tests to make sure you're getting the correct logic. Finally, you can refactor to DRY up the code.

Answer 2

Two thoughts:

1. There is definitely at least one way to get what you want with the current way your app is built/organized.
2. I would not use any of them. This app needs to be refactored, and now is a great time to do it.

Since the Rails or method requires a query with the object class specifically stated as its parameter, your current option gets a bit tricky. It looks like you want stores that have an email address. A query like this would mean the or is grabbing stores that don't have one.

@stores = Store.where.not(email: [nil, '']).where(:type_unique => true)
@stores = @stores.or(Store.where(:type_dining => true))

You could define a scope on the Store class that could then be applied to both, ie.

class Store
  scope :with_email, () -> { where.not(email: [nil, '']) }
end

def perform
  ...
  @stores = Store.with_email.where(:type_unique => true)
  @stores = @stores.or(Store.with_email.where(:type_dining => true))
end

But let's say you refactored your app so that instead of columns for the each of the store types, you had a single column called store_type. I'd use an enum here but for simplicity, let's say it's a string field. Then the above code becomes just this:

@stores = Store.where.not(email: [nil, '']).where(store_type: ["unique", "dining"])

That's equivalent to a store_type IN ("unique", "dining") SQL statement. Which is what you're looking for.

Moreover if a Lead's location was stored as an array string field, totally possible and simple if your app is using a PostgreSQL database, then @lead.location would return an array like ["east", "north"] and you could scrap your build_filters_obj method for just this:

@stores = @stores.where(location: @lead.location)

Refactoring would make your perform method so much easier to read and understand for other developers (which includes yourself in the future). If you do go that route, you'll need to refactor some database data. There are great tools for this.

• The AfterParty gem hasn't been updated in a while, but it's a great gem for just this kind of problem.
• Shopify has a Maintenance Tasks gem that also works for this purpose.

Both apps essentially run rake tasks behind the scenes which you'll need to do to convert your data over. Here's my typical sequence of events:

1. Deploy new code that has and makes use of new database columns (ie. location, store_type).
2. Use one of the above gems to convert old data to use the new format and columns.
3. Deploy code that removes the now defunct database columns (ie. location_north, location_west, etc.).

I usually do steps 1 and 2 in one release and step 3 in the next, but it's all about what fits your app and it's ability for downtime.