#45 ✓resolved
Marcel Ruegenberg

Search with using different unique indices

Reported by Marcel Ruegenberg | January 21st, 2009 @ 09:39 AM

If a table has no row named id, but instead a unique index (possibly over more than one row, see Rails' add_index), Searchlogic should still work.

Comments and changes to this ticket

  • Ben Johnson

    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.

  • Marcel Ruegenberg

    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

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

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

  • Ben Johnson

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

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

  • Marcel Ruegenberg

    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

    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.

  • Marcel Ruegenberg

    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

    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.

  • Marcel Ruegenberg

    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

    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 ?

  • Marcel Ruegenberg

    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.

  • Marcel Ruegenberg

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

    I meant to write that

    
    count(*)
    

    works.

    (Lighthouse somehow eliminated the star.)

  • Ben Johnson

    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

    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

    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.

  • jmrepetti (at gmail)

    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

  • Marcel Ruegenberg

    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

    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

    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.

  • Marcel Ruegenberg

    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).

  • Marcel Ruegenberg

    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

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

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

  • Marcel Ruegenberg

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket

Pages