Extra conditions in JOIN statement

[kashif-umair]

Hi, I have a scenario where I want to add extra conditions in join statement apart from the foreign key column condition. I have gone through almost all of the relevant code of this gem but can't find any option to achieve that. Can anyone please guide me how to do that? I'm including an example

I have a model Item:

class Item
  has_many :custom_attribute_values, dependent: :destroy
end

And model CustomAttributeValue:

# id: int(11)
# value: varchar(255)
class CustomAttributeValue
end

I also have added a custom predicate in my config/initializers/ransack.rb

Ransack.configure do |config|
  config.add_predicate 'null_or_eq', arel_predicate: 'null_or_eq'
end

module Arel
  module Predications
    def null_or_eq(value)
      eq(value).or(eq(nil))
    end
  end
end

So I applied the following filter

filters = { 'custom_attribute_values_id_null_or_eq' => 4, 'custom_attribute_values_value_null' => true }
search_results = Item.all.ransack(filters)

calling search_results.result.to_sql returns the following SQL Query:

SELECT * FROM items
LEFT OUTER JOIN custom_attribute_values ON custom_attribute_values.item_id = items.id
WHERE ((custom_attribute_values.id IS NULL OR custom_attribute_values.id = 4) AND custom_attribute_values.value IS NULL)

However, I need the following SQL Query to be built to get the correct results:

SELECT * FROM items
LEFT OUTER JOIN custom_attribute_values ON custom_attribute_values.item_id = items.id
  AND custom_attribute_values.id = 4
WHERE (custom_attribute_values.value IS NULL)

Note that extra condition in JOIN statement custom_attribute_values.id = 4

[ideaoforder]

I'm very late to this party too, but +1.

In MySQL having the additional condition in the JOIN clause is much more efficient. In large tables, this results in remarkably inefficient queries.

[jlefley]

I'm running into the same issue. I have a similar schema/data model as what is in the example above and need to generate a similar query. The association join conditions need to include both the foreign key and an additional column value condition so the "where is null" clause returns the correct rows.

I think I could put together an example app with a test case if that would be helpful.