Use or clause within where.not

Question

I'm stuck trying to upgrade a Rails 5 application to Rails 6 and remove some raw SQL in the process.

I have already managed to convert the raw SQL LEFT JOINs into ActiveRecord notation. Unfortunately, I am just failing with the OR-conditions within the where-clauses.

Old Code:

::Tyre::Entry.joins(" LEFT JOIN `core_manufacturers` ON `core_manufacturers`.`id` = `tyre_entries`.`core_manufacturer_id`
                      LEFT JOIN `tyre_designs` ON `tyre_designs`.`id` = `tyre_entries`.`tyre_design_id`
                      LEFT JOIN `tyre_dimensions` ON `tyre_dimensions`.`id` = `tyre_entries`.`tyre_dimension_id`
                      LEFT JOIN `tyre_seasons` ON `tyre_seasons`.`id` = `tyre_entries`.`tyre_season_id`
                      LEFT JOIN `tyre_usages` ON `tyre_usages`.`id` = `tyre_entries`.`tyre_usage_id`
                      LEFT JOIN `tyre_market_segments` ON `tyre_market_segments`.`id` = `tyre_entries`.`tyre_market_segment_id`")
                   .where("core_manufacturers.id IS NULL OR
                          tyre_designs.id IS NULL OR
                          tyre_dimensions.id IS NULL OR
                          tyre_seasons.id IS NULL OR
                          tyre_usages.id IS NULL OR
                          tyre_market_segments.id IS NULL ")

Code I created so far:

::Tyre::Entry.left_outer_joins(:manufacturer, :design, :dimension, :season, :usage, :market_segment)
             .where.not(core_manufacturers: { id: nil },
                              tyre_designs: { id: nil },
                              tyre_dimensions: { id: nil },
                              tyre_seasons: { id: nil },
                              tyre_usages: { id: nil },
                              tyre_market_segments: { id: nil })

The problem is that in this way where-conditions are linked with AND, which should actually be OR.

I know the following code snippet does not work but is there a way to make it work in this short notation?

::Tyre::Entry.left_outer_joins(:manufacturer, :design, :dimension, :season, :usage, :market_segment)
             .where.not(core_manufacturers: { id: nil }, or
                              tyre_designs: { id: nil }, or
                              tyre_dimensions: { id: nil }, or
                              tyre_seasons: { id: nil }, or
                              tyre_usages: { id: nil }, or
                              tyre_market_segments: { id: nil })

user_207090 · Answer

The rails way of doing or is this:

::Tyre::Entry
  .left_joins(:manufacturer, :design, :dimension, :season, :usage, :market_segment)
  .where(core_manufacturers:                     { id: nil })
  .or( ::Tyre::Entry.where(tyre_designs:         { id: nil }) )
  .or( ::Tyre::Entry.where(tyre_dimensions:      { id: nil }) )
  .or( ::Tyre::Entry.where(tyre_seasons:         { id: nil }) )
  .or( ::Tyre::Entry.where(tyre_usages:          { id: nil }) )
  .or( ::Tyre::Entry.where(tyre_market_segments: { id: nil }) )

To get "IS NULL" use where(...); to get "IS NOT NULL" use where.not(...)

https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-or

You can also use Arel:

::Tyre::Entry
  .left_joins(:manufacturer, :design, :dimension, :season, :usage, :market_segment)
  .where(CoreManufacturers.arel_table[:id].eq(nil)
    .or(        TyreDesign.arel_table[:id].eq(nil) )
    .or(     TyreDimension.arel_table[:id].eq(nil) )
    .or(        TyreSeason.arel_table[:id].eq(nil) )
    .or(         TyreUsage.arel_table[:id].eq(nil) )
    .or( TyreMarketSegment.arel_table[:id].eq(nil) )
  )

To get "IS NULL" use eq(nil); to get "IS NOT NULL" use not_eq(nil)

Use or clause within where.not

1 Answers