Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

How to do search with OR between two ANDs #169

Open
lironbeni opened this Issue · 9 comments

5 participants

@lironbeni

I would like to use advanced search to create a query that looks something like this:

SELECT DISTINCT `cell_lines`.* FROM `cell_lines` 
INNER JOIN `chars` ON `chars`.`model_id` = `cell_lines`.`id` AND `chars`.`model_type` = 'CellLine' 
WHERE (cell_lines.deleted_at IS NULL) 
AND ((`chars`.`char_type_id` = 48 
AND `chars`.`value` LIKE '%run%'
OR `chars`.`char_type_id` = 39 
AND `chars`.`value` LIKE '%Be%'))

(note the OR at the end)
I catch the params[:q] before passing it to the ransack engine and do some manipulations on it so at the moment the best params[:q] I was able to produce is:

params[:q]
{
  "c"=>{
    "1353408836163"=>{
      "v"=>{"0"=>{"value"=>"48"}}, "p"=>"eq", "a"=>{"0"=>{"name"=>"chars_char_type_id"}}
    }, 
    "1353408836162"=>{
      "v"=>{"0"=>{"value"=>"run"}}, "p"=>"cont", "a"=>{"0"=>{"name"=>"chars_value"}}
    },
    "1"=>{
      "v"=>{"0"=>{"value"=>"39"}}, "p"=>"eq", "a"=>{"0"=>{"name"=>"chars_char_type_id"}}
    }, 
    "0"=>{
      "v"=>{"0"=>{"value"=>"Be"}}, "p"=>"cont", "a"=>{"0"=>{"name"=>"chars_value"}}
    }
  }
}

When passing this to ransack I get the following query:

SELECT `cell_lines`.* FROM `cell_lines` 
INNER JOIN `chars` ON `chars`.`model_id` = `cell_lines`.`id` AND `chars`.`model_type` = 'CellLine' 
WHERE (cell_lines.deleted_at IS NULL)
 AND ((`chars`.`char_type_id` = 39 
AND `chars`.`value` LIKE '%run%' 
AND `chars`.`char_type_id` = 48 
AND `chars`.`value` LIKE '%Be%'))

was trying to use Alter Lagos suggestion: http://stackoverflow.com/questions/12940629/make-an-or-with-ransack
but it replaced all the ANDs with ORs and this is not what I'm trying to accomplish....

Any suggestion would be appreciated..

@graywh

You need 2 AND groupings combined by an OR. The params would look something like this:

params[:q]
{
  "m"=>"or",
  "g"=>{
    "0"=>{
      "m"=>"and",
      "c"=>{
        "1353408836163"=>{
          "v"=>{"0"=>{"value"=>"48"}}, "p"=>"eq", "a"=>{"0"=>{"name"=>"chars_char_type_id"}}
        }, 
        "1353408836162"=>{
          "v"=>{"0"=>{"value"=>"run"}}, "p"=>"cont", "a"=>{"0"=>{"name"=>"chars_value"}}
        }
      }
    },
    "1"=>{
      "m"=>"and",
      "c"=>{
        "1"=>{
          "v"=>{"0"=>{"value"=>"39"}}, "p"=>"eq", "a"=>{"0"=>{"name"=>"chars_char_type_id"}}
        }, 
        "0"=>{
          "v"=>{"0"=>{"value"=>"Be"}}, "p"=>"cont", "a"=>{"0"=>{"name"=>"chars_value"}}
        }
      }
    }
  }
}
@lironbeni

Thank you very very much!!!

@prikha

Is there any documentation on this kind of manipulation? what does those single chars keys mean?

@nishantmodak

@prikha were you able to find any documentation for that? I need to do some advanced queries and this would have really helped.

[edit]
Meanwhile, found this - a handy way to convert to the ransack format. https://github.com/gabynaiman/nql

Example:

q= 'line: It | line: the'
NQL.to_ransack(q)
 => {:g=>[{:m=>"or", :c=>[{:a=>{"0"=>{:name=>"line"}}, :p=>"cont", :v=>{"0"=>{:value=>"It"}}}, {:a=>{"0"=>{:name=>"line"}}, :p=>"cont", :v=>{"0"=>{:value=>"the"}}}]}]} 
@prikha

no luck, went the other way round

@graywh

@prikha I learned what I know from examining the incoming params and reading https://github.com/ernie/ransack/blob/master/lib/ransack/helpers/form_builder.rb.

@rilian

Thanks for awesome examples of grouping! This should certainly go to README

@rilian

another handy way to write complex combinations:

for example you have Items, with name and value

{
  combinator: 'or',
  groupings: {
    '0' => {'item_name_eq' => 'Color', 'item_value_eq' => 'Green'},
    '1' => {'item_name_cont' => ['Size', 'Value'], 'item_value_gt' => 50},
  }
}
@rilian

@lironbeni your example may look like

{
  combinator: 'or',
  groupings: {
    '0' => {'chars_char_type_id_eq' => 48, 'chars_value_cont' => 'run'},
    '1' => {'chars_char_type_id_eq' => 39, 'chars_value_cont' => 'Be'},
  }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.