Ransack: how to join table multiple times with dif

问题:

Suppose I have :items with a has_many association with :properties, then I can search for all items that have a property with name 'a_name' and value 'a_value' like this

q: { properties_name_eq: 'a_name', properties_value_eq: 'a_value' }

Now what if I want to search for all items that have a property with name 'a_name' and value 'a_value' and also a property with name 'another_name' and value 'another_value'?

The following doesn't work as it joins the properties table only once

q: {
  g: {
    '0' => { properties_name_eq: 'a_name', properties_value_eq: 'a_value' },
    '1' => { properties_name_eq: 'another_name', properties_value_eq: 'another_value'}
  }
}

The generated SQL looks something like this

  SELECT DISTINCT "items".* FROM "items"
  LEFT OUTER JOIN "properties" ON "properties"."item_id" = "items"."id"
  INNER JOIN ((SELECT "items".* FROM "items")) AS sel_111 on sel_111.id
  WHERE
  (("properties"."name" = 'a_name' AND "properties"."value" = 'a_value') AND ("properties"."name" = 'another_name' AND "properties"."value" = 'another_value'))

EDIT:

To make it more clear what I am after, I'll paste a spec below.

Item.create name: 'ab', properties_attributes: [{ name: 'a', value: 'a1'}, {name: 'b', value: 'b1'}]
Item.create name: 'a', properties_attributes: [{ name: 'a', value: 'a1'}]
Item.create name: 'b', properties_attributes: [{name: 'b', value: 'b1'}]
Item.create name: 'ax', properties_attributes: [{ name: 'a', value: 'a1'}, {name: 'b', value: 'x'}]
Item.create name: 'bx', properties_attributes: [{ name: 'a', value: 'x'}, {name: 'b', value: 'b1'}]
Item.create name: 'other', properties_attributes: [{ name: 'other', value: '123'}]

get :index, q: { properties_name_eq: 'a', properties_value_eq: 'a1' }
names = JSON.parse(response.body).map{|u| u['name']}
expect(names).to match_array ['ab', 'a', 'ax'] # OK!

get :index, 
  q: {
    m: 'or',
    g: {
      '0' => { properties_name_eq: 'a', properties_value_eq: 'a1' },
      '1' => { properties_name_eq: 'b', properties_value_eq: 'b1'}
    }
  }
names = JSON.parse(response.body).map{|u| u['name']}
expect(names).to match_array ['ab'] #FAILS!

回答1:

Just use Model.search(params[:q].try(:merge, m: 'or')), using your example:

q: {
  m: 'or',
  g: {
    '0' => { properties_name_eq: 'a_name', properties_value_eq: 'a_value' },
    '1' => { properties_name_eq: 'another_name', properties_value_eq: 'another_value'}
  }
}

You can find more information here

You need an or at the where level of your query, because properties.name can't be equal 'a_name' and 'another_name' at the same time. A second alias for the table is not required.

回答2:

You can solve this by using multiple queries.

1. For each name + value property, get all item IDs with this property
2. Intersect the resulting IDs for each property into item_ids
3. In the final query on :items, add the clause WHERE id IN (item_ids)

Here's a code example that does steps 1 & 2:

def property_item_ids(conditions)
  conditions.inject([]) do |result, (key, condition)|
    result.method(result.empty? ? '+' : '&').(Property.ransack(m: "and", g: condition).pluck(:item_id).to_a)
  end
end

Get the item IDs that have all properties:

conditions = {
  '0' => { properties_name_eq: 'a', properties_value_eq: 'a1' },
  '1' => { properties_name_eq: 'b', properties_value_eq: 'b1'}
}

item_ids = property_item_ids(conditions)

For step 3, invoke ransack with item_ids:

q: {
  m: 'and',
  g: {
    '0' => { item_id_in: item_ids }
  }
}