Binarylogic Searchlogic

Comments and changes to this ticket

• 

  Ben Johnson January 21st, 2009 @ 01:44 PM

  • State changed from “new” to “open”

  Ok, I need more details about how it doesn't work.

• You flagged this item as spam.
  

  Marcel Ruegenberg January 22nd, 2009 @ 05:15 AM

  Actually, I'm generating a model dynamically which represents a many-to-many join table. I added a unique index over both columns to the table.

  When I do new_search on the generated type, I get

  
  You have a nil object when you didn't expect it!
  The error occurred while evaluating nil.name
  

  
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/shared/virtual_classes.rb:16:in `create_virtual_class'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/search/conditions.rb:17:in `initialize'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/active_record/base.rb:191:in `new'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/active_record/base.rb:191:in `searchlogic_search'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/active_record/base.rb:65:in `new_search'
  ...
  

  It works with ActiveRecords .all.

  In this specific case, I don't currently use Searchlogic for search, only for pagination.

• 

  Ben Johnson January 22nd, 2009 @ 12:08 PM

  I'm not sure what you mean by generating a model dynamically.

• 

  Ben Johnson January 22nd, 2009 @ 12:08 PM

  I'm not sure what you mean by generating a model dynamically.

• You flagged this item as spam.
  

  Marcel Ruegenberg January 23rd, 2009 @ 03:50 AM

  By dynamically, I mean that it is not a predefined model in app/models, but created in the controller when the action is called.

  Anyway, the dynamic part is not really important. What's important is that the model does not have an id column, only two columns, which are references, and an index over both.

  The problem is basically that the pagination (page_links und page_count) depends on the existence of an id column.

• 

  Ben Johnson January 23rd, 2009 @ 03:54 AM

  If you can tell me how it depends on the id column I'll see if I can change that, but pagination shouldn't care about the id column.

• You flagged this item as spam.
  

  Marcel Ruegenberg January 23rd, 2009 @ 04:32 AM

  From looking at some of the files in the trace, I now think the problem of depending on an id column being present is not with Searchlogic, but with how ActiveRecord's 'count' calculation is implemented. I'll probably file a bug for ActiveRecord.

  (The above trace was false / a different (old) issue.)

  The error I actually get is:

  
  PGError: ERROR:  column "id" does not exist
  LINE 1: SELECT count(id) AS count_id FROM "permissions"
                       ^
  : SELECT count(id) AS count_id FROM "permissions"
  
  
  Extracted source:
  <% if @search.page_count > 1 %>
     <%= page_links %> 
  

  Application trace:

  
  /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/abstract_adapter.rb:188:in `log'
  /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/postgresql_adapter.rb:503:in `execute'
  /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/connection_adapters/postgresql_adapter.rb:1000:in `select_raw'
  .
  .
  .
  /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/calculations.rb:122:in `catch'
  /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/calculations.rb:122:in `calculate_without_searchlogic'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/active_record/base.rb:15:in `calculate'
  /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/calculations.rb:48:in `count'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/search/searching.rb:23:in `count'
  /usr/lib/ruby/gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:2003:in `with_scope_without_searchlogic'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/active_record/base.rb:47:in `with_scope'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/search/searching.rb:16:in `send'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/search/searching.rb:16:in `count'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/search/pagination.rb:66:in `page_count'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/helpers/control_types/links.rb:205:in `add_page_links_defaults!'
  vendor/plugins/searchlogic-git-20090108/lib/searchlogic/helpers/control_types/links.rb:116:in `page_links'
  app/views/mycontroller/myview.html.erb
  

• 

  Ben Johnson January 23rd, 2009 @ 04:34 AM

  Yeah, it looks like it. The ultimate test is to see if you can execute that query without searchlogic, but just using ActiveRecord. If you get the same error then you know for sure its ActiveRecord.

• You flagged this item as spam.
  

  Marcel Ruegenberg January 23rd, 2009 @ 05:06 AM

  It turns out that searchlogic uses the primary_key of the model for sorting.

  I didn't explicitly set the primary key which resulted in it having the default value "id". Searchlogic adds the primary key to the arguments for the calculation in the following code fragment in searching.rb

  
                if #{CALCULATION_METHODS.include?(method)}
                  options[:distinct] = true if #{method == :count} && !joins.blank? && Config.search.remove_duplicates?
                  args[0] = klass.primary_key if [nil, :all].include?(args[0])
                end
  

  I don't know if there is a more elegant solution for this, but by changing the above code to the following, all my problems seem to go away:

  
                if #{CALCULATION_METHODS.include?(method)}
                  options[:distinct] = true if #{method == :count} && !joins.blank? && Config.search.remove_duplicates?
                  args[0] = klass.primary_key if [nil, :all].include?(args[0]) && (! #{method == :count})
                end
  

• 

  Ben Johnson January 24th, 2009 @ 07:30 AM

  Yeah, that doesn't really solve the issue, it just solves it for the count method. Let me see what I can do. What does your class return for primary_key ?

• You flagged this item as spam.
  

  Marcel Ruegenberg January 26th, 2009 @ 04:16 AM

  It returns the default, "id", because I didn't set the primary key explicitly. AFAIK, a primary key is restricted to one column which makes it impossible in this case to set it to a meaningful value that covers all uses of primary keys.

  I just tried to use a Gem that allows for ActiveRecord to have primary keys over multiple columns (see http://compositekeys.rubyforge.o..., but using that results in a new error, because the argument of SQL's 'count' has to be a real column, not any key.

  ---

  Is there any specific reason why :count explicitly asks for the primary_key column instead of just count()? I did a query on two databases, one MySQL, one Postgres: count() worked and returned the correct result for both. That would also solve this issue.

• You flagged this item as spam.
  

  Marcel Ruegenberg January 26th, 2009 @ 09:14 AM

  I meant to write that

  
  count(*)
  

  works.

  (Lighthouse somehow eliminated the star.)

• 

  Ben Johnson January 26th, 2009 @ 12:53 PM

  I should have commented my code there. Anyways, I'm pretty sure the * doesn't work with PostgreSQL if you are doing joins or includes. Otherwise I think it has to do with the other calculation functions that require a specific column to be specified, like avg().

• 

  Ben Johnson January 26th, 2009 @ 12:53 PM

  If you can try this with PostgreSQL while using includes and confirm it works I'll make the change.

• 

  Ben Johnson January 26th, 2009 @ 03:52 PM

  I found the issue, I tried to make the change and I get the following:

  SQLite3::SQLException: near "*": syntax error: SELECT count(DISTINCT *) AS count_all FROM "users" LEFT OUTER JOIN "accounts" ON "accounts".id = "users".account_id
  

  You can't use * with DISTINCT. I need to use this option, otherwise the calculations are not accurate with a left outer join, since that type of join returns duplicate results. So, by default, for a count operation, I need a column. Maybe I could just pick the first column returned by Model.column_names, since your model doesn't have a primary key.

• You flagged this item as spam.
  

  jmrepetti (at gmail) January 26th, 2009 @ 06:49 PM

  I don't know if this is the correct place but I have a problem related to composed keys.

  I have a model, Cliente, I use composed keys(http://compositekeys.rubyforge.o...

  class Cliente < ActiveRecord::Base #... set_primary_keys :clienteID, :nodo_codigo #... end

  When I do the search:

  @search = Cliente.new_search

  @clientes = @search.all This work well, return all records. But this:

  @clientes_count = @search.count ActiveRecord::StatementInvalid: Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nodo_codigo) AS count_clienteid_nodo_codigo FROM clientes' at line 1: SELECT count(clienteID,nodo_codigo) AS count_clienteid_nodo_codigo FROM clientes

  If I do:

  @clientes_count = @search.count("clienteID AND nodo_codigo") => 4504

  Works!, but in index.html.erb view, when I use 'page_select' helper I get this error again.

  I do this, but is ugly, and not everyone always want :select. This work for me because I use MySQL and need a solution now

  Searchlogic::ActiveRecord::Base def calculate_with_searchlogic(*args)

    options = args.extract_options!
    options = filter_options_with_searchlogic(options, false)
  
    #-------
    if [primary_key].flatten.size > 1
      options[:select] = primary_key.join(" AND ")
    end  
    #-------
  
    args << options
    calculate_without_searchlogic(*args)
  
  

  end end

  I hope that this has been useful. Juan Matías

• You flagged this item as spam.
  

  Marcel Ruegenberg January 27th, 2009 @ 03:46 AM

  I think using the first column of the model would be a good solution.

  An alternative solution would be to add an option to specify which key to use. I don't think that is really necessary for 'count', but that would also solve the problem with composite keys, or at least provide a workaround.

• 

  Ben Johnson January 27th, 2009 @ 04:50 AM

  • State changed from “open” to “resolved”

  This is all set, update from the repo. Let me know if you have any issues.

• 

  Ben Johnson January 27th, 2009 @ 04:51 AM

  This should be fixed, update from the repo and let me know if you have any other issues.

• You flagged this item as spam.
  

  Marcel Ruegenberg January 27th, 2009 @ 06:57 AM

  Thanks you, it might take a while until I can check if everything works, because I have to use the tarball from Github instead of cloning the repo directly (due to proxy issues).

• You flagged this item as spam.
  

  Marcel Ruegenberg January 27th, 2009 @ 08:30 AM

  The Git tree seems to be a little out of date, at http://github.com/binarylogic/se... it says that the last change was made on Jan. 6?

• 

  Ben Johnson January 27th, 2009 @ 12:06 PM

  Sorry, I pushed and didn't realize that. Should be good now.

• You flagged this item as spam.
  

  Marcel Ruegenberg January 28th, 2009 @ 05:31 AM

  Works for me now. Thanks.