1

I have two models Employee & Unit. Unit has many Employees. I am using SearchLogic to search employee model. What is the equivalent of below SQL in Searchlogic

employees.unit_id IN (1,2,3)

I have tried both

unit_id_equals_all[] 
unit_id_equals_any[]

But nothing works. Can anyone help?

Thanks, Abhilash

eabhvee
  • 159
  • 1
  • 1
  • 9

3 Answers3

1
Employee.unit_id_equals([1, 2, 3])
PeterWong
  • 15,951
  • 9
  • 59
  • 68
  • Unfortunately that does not work. I get this error "wrong number of bind variables (2 for 1) in: employees.unit_id IN (?)" – eabhvee Sep 15 '10 at 07:48
  • Could you give more hints about the error message? such as when is the message being generated? I can't get why there were 2 variables...... – PeterWong Sep 15 '10 at 08:10
  • In my search view I selected 2 units and passed it as an array, if I select 5 it will say 5 for 1. My params hash is like this --> unit_id_equals"=>["332", "333"] – eabhvee Sep 15 '10 at 08:23
  • BTW, Employee.unit_id_equals([332,333]) works fine in the console – eabhvee Sep 15 '10 at 08:37
  • Yes this should work. Are you sure you are `...equals(["332", "333])` instead of `...equals("332", "333")`? The latter one give the same error message while the former one is correct and worked fine. – PeterWong Sep 15 '10 at 08:49
  • Thanks for the clarification. This is my view code <%= check_box_tag "search[unit_id_equals][]", unit.id %> Is there something wrong here? – eabhvee Sep 15 '10 at 09:20
  • Found the reason. This is an issue in searchlogic Employee.unit_id_equals([1, 2, 3]) works but Employee.search(:unit_id_equals => [1, 2, 3]).all does not work http://github.com/binarylogic/searchlogic/issues/issue/64 – eabhvee Sep 15 '10 at 11:37
  • @Angela, ref: http://stackoverflow.com/questions/3655818/will-searchlogic-work-with-rails-3 most likely searchlogic is not working in rails 3 but you could simply switch to metawhere and this solution should work too. – PeterWong Mar 26 '11 at 18:32
  • @Peterwong do I need to isolate just the id's? Or can I pass an array of user objects (for example, I am using the acts_as_network gem with User.friends creating the array. – Satchel Mar 26 '11 at 21:50
  • Since I have a long time not using search gems, I do not sure if it is ok. If your `.friends` not a so long list, `User.friends.collect(&:id)` would not slow at all ;D – PeterWong Mar 27 '11 at 04:49
1

Same problem here.

I have no idea why this worked for me, or why I even tried it, since it is undocumented. But I changed the _equals to _in, and it produced the SQL with IN, and worked perfectly.

Employee.unit_id_in([1, 2, 3])
pixelearth
  • 13,674
  • 10
  • 62
  • 110
1

We had this same issue with a Rails 2.3.12 project. With searchlogic 2.4.7 we could do:

User.id_equals([1,2,3])

After upgrading to search logic 2.5.8 (deprecation messages in the old one were cluttering out cucumber and spec output), this syntax no longer worked. It threw this error just like the one above:

ActiveRecord::StatementInvalid: Mysql::Error: Operand should contain 1 column(s): SELECT * FROM `users` WHERE (users.id = 1, 2, 3)

After trying the solutions above, we found that these two syntax alternatives worked:

User.id_in([1,2,3]) <-- as suggested above
User.id_equals_any([1,2,3])

In other words, without the "_any" the new search logic produces invalid mysql. Looking into when and why this change might have occurred, I found this commit discussion:

Github commit changing handling of arrays

The upshot of this change and the discussion was to require _any for times when you want a match against any value in the array, and otherwise just pass the array in to the equals statement directly without changing the SQL to "IN" as needed for multiple values.

Reverting to 2.4.7 clears the error. Changing all the calls to the more explicit _any or _in is what we ended up doing in order to avoid deprecation errors. Hope this helps and adds to the very helpful answers above.

Charles Forcey
  • 537
  • 5
  • 8