#68 ✓resolved
nico

add explicitly :includes in a search request

Reported by nico | March 23rd, 2009 @ 06:38 AM

I would like to explicitly includes 2 tables in a search query to improve the process of the results

I didn't find any documentation to do that.

I tried: search = Measure.new_search({:includes=>[:measures_tags,:tags]})

search = Measure.new_search() search.options[:includes]=>[:measures_tags,:tags]

search = Measure.new_search() search.includes=[:measures_tags,:tags]

but nothing works..

Is there a way to do that with searchlogic?

Thanks in advance

Comments and changes to this ticket

  • Deepu

    Deepu March 23rd, 2009 @ 07:25 AM

    @Nico - If i am not wrong you want to include some conditions on the associated tables ? If so then this will do:-

    search = Measure.new_search search.conditions.measures_tags.x_gt = params[:x] search.conditions.tags.y_lt = params[:y] @results = search.all

    just make sure you have proper associations in the respective models.

    Am not sure whether i understood your problem correctly though

  • Deepu

    Deepu March 23rd, 2009 @ 07:28 AM

    sorry above comment was formatted all lost. The code is

    @@@

     search = Measure.new_search search.conditions.measures_tags.x_gt = params[:x] search.conditions.tags.y_lt = params[:y] @results = search.all
    
    
    
    
  • Deepu

    Deepu March 23rd, 2009 @ 07:28 AM

    damn this formatting.

    
    search = Measure.new_search; search.conditions.measures_tags.x_gt = params[:x];
    search.conditions.tags.y_lt = params[:y];
     @results = search.all
    
  • nico

    nico March 23rd, 2009 @ 09:38 AM

    Thanks Deepu,

    my pb is a problem of SQL performance. I dont want to use any conditions on the 'tags' table, just include it in the results.

    for instance, if I search a set of measures and want analyze the associated tags after, I have to do that:

    search=Measure.new_search search.conditions=... results=search.all

    results.each {|measure| do

    measure.tags{|tag| do /// <-- here a SQL query .. doing something here end end

    such code requires 1 SQL query to get the tags associated for each measure.. (so 100 SQL queries for a result of 100 measures)

    Normaly with active_record you can do the following instruction: find(:all, :includes=>:tags , :conditions=>"..") to avoid such common problem of Lazy association.

    I would like to know how to do a :includes=>"tags" with the searchlogic lib

    to I wanted to load the tags associated to each measure so that I don't have to do a sql query each time I

  • Deepu

    Deepu March 23rd, 2009 @ 10:28 AM

    @nico :-

    try search.all(:include => "tags") . it worked for me.

    Cheers, Deepu.

  • nico

    nico March 23rd, 2009 @ 11:05 AM

    Hi Deepu,

    I tried

    search=Measure.new_search ... search.conditions.tags.name_not_equal=nil search.all(:include => "tags")

    This has no impact.

    This generated the same SQL with and without the :include => "tags") :

    SELECT DISTINCT measures.* FROM measures LEFT OUTER JOIN measure_tags ON (measures.id = measure_tags.measure_id) LEFT OUTER JOIN tags ON (tags.id = measure_tags.tag_id) WHERE (measures.lng > 2.0 AND measures.lng < 2.1 AND measures.lat < 48.1 AND measures.lat > 48.0 AND tags.name IS NOT NULL)

    it does not include the measures_tags, tags table in the SELECT

    With the classical active_records find method:

    Measure.find(:all,:include=>[:measure_tags,:tags], :conditions=>["measure_tags.id is not null and lat<? and lat>? and lng<? and lng>?",box_top,box_bottom,box_right,box_left])

    I have the generated SQL:

    SELECT measures.id AS t0_r0, measures.created_at AS t0_r1, measures.lng AS t0_r2, measures.lat AS t0_r3, measures.loudness AS t0_r4, measures.track_id AS t0_r5, measures.user_id AS t0_r6, measures.location AS t0_r7, measure_tags.id AS t1_r0, measure_tags.measure_id AS t1_r1, measure_tags.tag_id AS t1_r2, tags.id AS t2_r0, tags.name AS t2_r1 FROM measures LEFT OUTER JOIN measure_tags ON measure_tags.measure_id = measures.id LEFT OUTER JOIN measure_tags tags_measures_join ON (measures.id = tags_measures_join.measure_id) LEFT OUTER JOIN tags ON (tags.id = tags_measures_join.tag_id) WHERE (measure_tags.id is not null and lat<'48.1' and lat>'48.0' and lng<'2' and lng>'2')

    an idea?

  • timur

    timur March 23rd, 2009 @ 05:07 PM

    Hi

    I am facing the same issue.

    For Example this is my controller code

    I am reading a property of the user object in the view with search parameters there is a n+1 query problem

    @@@javascript def index

    if params[:search]
      @search = Claim.new_search(params[:search])
      @claims, @claims_count = @search.all(:include => [ :user ]), @search.count
    else
      @search = Claim.new_search
      @claims = Claim.find(:all, :include => [ :user ], :limit => 25, :order => "DATE DESC")
    end
    
    

    end

    
    
    I am checking if search parameters are provided if not i am doing a normal simple search with an include. Their is no n + 1 query problem in my view when i do the normal search. If i am using this query with searchlogic there is an n + 1 query. 
    
    The include Statement 
    
    

    @search.find(:all, :include => [ :user ])

    
    
    does not have any effect.
    
    I also tried 
    

    @search.all(:inlcude => "user")

    
    
    no effect also.
    
  • Ben Johnson

    Ben Johnson March 23rd, 2009 @ 05:26 PM

    • State changed from “new” to “open”

    I don't think this is searchlogic, I think ActiveRecord splits it up into multiple queries for performance reasons. Im fairly certain they made this change within the last 6 months or so. Try doing the query without searchlogic, i bet you get the same results.

  • timur

    timur March 23rd, 2009 @ 06:00 PM

    Here is the request with the normal activerecord find

    
    
    Processing ClaimsController#index (for 127.0.0.1 at 2009-03-23 22:54:55) [GET]
      Parameters: {"action"=>"index", "controller"=>"claims"}
      User Columns (7.2ms)   SHOW FIELDS FROM `users`
      User Load (0.3ms)   SELECT * FROM `users` WHERE (`users`.`id` = 930) LIMIT 1
      SQL (0.1ms)   BEGIN
      User Update (0.3ms)   UPDATE `users` SET `updated_at` = '2009-03-23 21:54:55', `last_request_at` = '2009-03-23 21:54:55' WHERE `id` = 930
      SQL (0.5ms)   COMMIT
      Claim Load (2.2ms)   SELECT * FROM `claims` ORDER BY DATE DESC LIMIT 25
      Claim Columns (1.8ms)   SHOW FIELDS FROM `claims`
      User Load (0.9ms)   SELECT * FROM `users` WHERE (`users`.`id` IN (886,929,920,888,922,911,912,923,890,880,891,892,881,926,893,882,915,904,917,884,885))
    Rendering template within layouts/application
    Rendering claims/index
    Rendered claims/_submenu (0.4ms)
      SQL (0.5ms)   SELECT count(`claims`.id) AS count_id FROM `claims`
    Rendered shared/_menu (0.3ms)
    Rendered shared/_messages (0.1ms)
    Completed in 73ms (View: 32, DB: 14 6 queries) | 200 OK [http://localhost/claims]
    

    and here with searchlogic

    
    Processing ClaimsController#index (for 127.0.0.1 at 2009-03-23 22:58:07) [GET]
      Parameters: {"commit"=>"Suche", "search"=>{"conditions"=>{"date_after"=>"", "user"=>{"firstname_keywords"=>"", "lastname_keywords"=>""}}, "priority_order_by"=>"", "order_as"=>"ASC", "per_page"=>"25", "priority_order_as"=>"", "order_by"=>"date"}, "action"=>"index", "controller"=>"claims"}
      User Columns (4.1ms)   SHOW FIELDS FROM `users`
      User Load (0.3ms)   SELECT * FROM `users` WHERE (`users`.`id` = 930) LIMIT 1
      SQL (0.1ms)   BEGIN
      User Update (0.3ms)   UPDATE `users` SET `updated_at` = '2009-03-23 21:58:07', `last_request_at` = '2009-03-23 21:58:07' WHERE `id` = 930
      SQL (0.4ms)   COMMIT
      Claim Columns (1.7ms)   SHOW FIELDS FROM `claims`
      Claim Load (1.3ms)   SELECT * FROM `claims` ORDER BY `claims`.`date` ASC LIMIT 25
      SQL (0.4ms)   SELECT count(`claims`.id) AS count_id FROM `claims`
    Rendering template within layouts/application
    Rendering claims/index
    Rendered claims/_submenu (0.5ms)
      CACHE (0.0ms)   SELECT count(`claims`.id) AS count_id FROM `claims`
      User Load (0.4ms)   SELECT * FROM `users` WHERE (`users`.`id` = 919)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 895)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 903)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 886)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 910)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 893)
      CACHE (0.0ms)   SELECT * FROM `users` WHERE (`users`.`id` = 910)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 881)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 921)
      User Load (0.4ms)   SELECT * FROM `users` WHERE (`users`.`id` = 898)
      User Load (0.3ms)   SELECT * FROM `users` WHERE (`users`.`id` = 922)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 905)
      CACHE (0.0ms)   SELECT * FROM `users` WHERE (`users`.`id` = 893)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 882)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 880)
      User Load (0.3ms)   SELECT * FROM `users` WHERE (`users`.`id` = 927)
      CACHE (0.0ms)   SELECT * FROM `users` WHERE (`users`.`id` = 880)
      User Load (0.3ms)   SELECT * FROM `users` WHERE (`users`.`id` = 908)
      User Load (0.3ms)   SELECT * FROM `users` WHERE (`users`.`id` = 915)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 925)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 907)
      User Load (0.2ms)   SELECT * FROM `users` WHERE (`users`.`id` = 913)
      CACHE (0.0ms)   SELECT * FROM `users` WHERE (`users`.`id` = 882)
      User Load (0.3ms)   SELECT * FROM `users` WHERE (`users`.`id` = 896)
      CACHE (0.0ms)   SELECT * FROM `users` WHERE (`users`.`id` = 886)
    Rendered shared/_menu (0.3ms)
    Rendered shared/_messages (0.1ms)
    Completed in 88ms (View: 49, DB: 14 25 queries)
    

    in the first request there are 6 queries in the second 25. It is not a big speed difference because a lot of data are cached. But you can see the classic n+1 problem.

    I am using rails 2.3.2

    Cheers Timur

  • nico

    nico March 23rd, 2009 @ 06:48 PM

    same n+1 issue

    Does searchlogic generate internally an active_record query or directly a SQL?

    if it generates an active_records query, could we inject some custom codes for instance an :includes to solve this n+1 problem?

    cheers, nico

  • timur

    timur March 30th, 2009 @ 08:07 AM

    any progress with this issue?

  • nico

    nico March 30th, 2009 @ 08:15 AM

    no. finally due to any reply , I switched to the normal find method too bad..

  • Ben Johnson

    Ben Johnson March 30th, 2009 @ 11:41 AM

    I am busy and have a job just like you. It would help me out if you could create a failing test, then I could quickly fix the issue and move on, instead of having to read through all of this. Or you could always try and fix it yourself. Thanks.

  • timur

    timur March 30th, 2009 @ 07:04 PM

    Hi Ben

    no problem. I understand that it's not easy to maintain a library like this. I' ll try to shorten my example.

    A user has_many claims. When i try searching for claims like this.

    
    @claims, @claims_count = @search.all(:include => [ :user ]), @search.count
    }
    

    this are the sql statements SELECT * FROM claims ORDER BY claims.date ASC LIMIT 25 CACHE (0.0ms) SELECT count(claims.id) AS count_id FROM claims User Load (0.4ms) SELECT * FROM users WHERE (users.id = 919) User Load (0.2ms) SELECT * FROM users WHERE (users.id = 895) User Load (0.2ms) SELECT * FROM users WHERE (users.id = 903) User Load (0.2ms) SELECT * FROM users WHERE (users.id = 886) User Load (0.2ms) SELECT * FROM users WHERE (users.id = 910) User Load (0.2ms) SELECT * FROM users WHERE (users.id = 893)

    In my view i am accessing the user object of the claim so for every access a sql statment is fired.

    When i do

    
    @claims = Claim.find(:all, :include => [ :user ], :limit => 25)
    

    i get this statements. SELECT * FROM claims ORDER BY DATE DESC LIMIT 25 SELECT * FROM users WHERE (users.id IN (886,929,920,888,922,911,912,923,890,880,891,892,881,926,893,882,915,904,917,884,885))

    That's what yo ment that activerecord splits up the include in two statements.

    So the problem seems searchlogic does not use the include statement In base.rb i found this.

    
    AR_FIND_OPTIONS = ::ActiveRecord::Base.valid_find_options
    

    so :include looks like a valid option.

    in searching.rb i found this

    
    options = args.extract_options! # can't pass options, your options are in the search
    

    for me it looks like :include is filtered out here, maybe you have a starting point for the problem.

  • timur

    timur March 31st, 2009 @ 08:26 AM

    i read in another ticket that you want a test suite for this problem. I can understand this.In your base_test.rb in method test_setting_first_level_options you are testing for the :inlcude option.

    For me it's hard to proivde a test case because the option test would be passed and the search result test also. It's more a performance issue so i hope my description for this is sufficient. tell me If you need more information or have an idea how to test this issue.

  • Georg Ledermann

    Georg Ledermann May 22nd, 2009 @ 07:16 AM

    Stumbled upon the same problem. The :include options seems to be ignored. Hoping Ben has time to fix this... Perhaps this bug is another argument for building the version 2.0 of Searchlogic :)

  • Ben Johnson

    Ben Johnson May 22nd, 2009 @ 10:33 AM

    Well it seems to be working for me:

    >> s = Neighborhood.new_search
    => #25}>
    >> s.include = :relationships
    => :relationships
    >> s.sanitize
    => {:limit=>25, :include=>:relationships}
    

    Also, I remember reading somewhere that AR changed how it treats that option. I might be way off here, but I think they lazily load those records because it was faster to perform a bunch of small queries than one big one.

  • Georg Ledermann

    Georg Ledermann May 22nd, 2009 @ 10:52 AM

    Thank you for this hint, Ben!
    It seems there is difference in this two approaches:

    1)

     s = Neighborhood.new_search
     s.include = :relationships
     s.find :all
    

    => Works fine, the include option is given to AR

    2)

     s = Neighborhood.new_search
     s.find :all, :include => :relationships
    

    => Fails because it ignores the include option

  • Ben Johnson

    Ben Johnson June 20th, 2009 @ 05:02 AM

    • State changed from “open” to “resolved”

    v2 should resolve this issue as it falls into the standard practices of using the :include option. Hope this helps. But the first example georg gave is correct.

  • seomaster

    seomaster April 14th, 2021 @ 08:11 AM

    This content is simply exciting and creative. I have been deciding on a institutional move and this has helped me with one aspect. online casino

  • seomaster

    seomaster April 14th, 2021 @ 08:20 AM

    I invite you to the page where see how much we have in common. online casino

  • seomaster

    seomaster April 15th, 2021 @ 12:46 AM

    This content is simply exciting and creative. I have been deciding on a institutional move and this has helped me with one aspect. casino online canada

  • seomaster

    seomaster April 15th, 2021 @ 04:10 AM

    I invite you to the page where see how much we have in common. casino online canada

  • seomaster

    seomaster April 16th, 2021 @ 12:48 AM

    This content is simply exciting and creative. I have been deciding on a institutional move and this has helped me with one aspect. check here,

  • seomaster

    seomaster April 16th, 2021 @ 05:09 AM

    I invite you to the page where see how much we have in common. website,

  • seomaster

    seomaster April 16th, 2021 @ 11:35 PM

    This content is simply exciting and creative. I have been deciding on a institutional move and this has helped me with one aspect. online casino canada real money

  • seomaster

    seomaster April 17th, 2021 @ 12:24 AM

    I invite you to the page where see how much we have in common. online casino canada real money

  • seomaster

    seomaster April 17th, 2021 @ 08:18 PM

    Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here. Lambingan

  • seomaster

    seomaster April 17th, 2021 @ 11:42 PM

    In this article understand the most important thing, the item will give you a keyword rich link a great useful website page: Pinoy Lambingan

  • seomaster

    seomaster April 18th, 2021 @ 11:53 PM

    Your blogs further more each else volume is so entertaining further serviceable It appoints me befall retreat encore. I will instantly grab your rss feed to stay informed of any updates. kasyno online

  • seomaster

    seomaster April 19th, 2021 @ 12:47 AM

    I am interested in such topics so I will address page where it is cool described. kasyno online

  • seomaster

    seomaster April 19th, 2021 @ 08:31 PM

    This content is simply exciting and creative. I have been deciding on a institutional move and this has helped me with one aspect. visit this page,

  • seomaster

    seomaster April 20th, 2021 @ 12:56 AM

    I invite you to the page where see how much we have in common. use all anchor equally.

  • seomaster

    seomaster April 20th, 2021 @ 08:35 PM

    Your blogs further more each else volume is so entertaining further serviceable It appoints me befall retreat encore. I will instantly grab your rss feed to stay informed of any updates. kasyna online

  • seomaster

    seomaster April 20th, 2021 @ 10:05 PM

    I am interested in such topics so I will address page where it is cool described. kasyna online

  • seomaster

    seomaster April 21st, 2021 @ 07:19 PM

    This content is simply exciting and creative. I have been deciding on a institutional move and this has helped me with one aspect. keno online

  • seomaster

    seomaster April 22nd, 2021 @ 12:35 AM

    I invite you to the page where see how much we have in common. keno online

  • seomaster

    seomaster April 23rd, 2021 @ 12:07 AM

    On this subject internet page, you'll see my best information, be sure to look over this level of detail. ranking kasyn

  • seomaster

    seomaster April 23rd, 2021 @ 12:56 AM

    Initial You got a awesome blog .I determination be involved in plus uniform minutes. i view you got truly very functional matters , i determination be always checking your blog blesss. ranking kasyn

  • seomaster

    seomaster April 24th, 2021 @ 08:48 PM

    Why do only so much written on this subject? Here you see more. read more,

  • seomaster

    seomaster April 24th, 2021 @ 09:39 PM

    It is somewhat fantastic, and yet check out the advice at this treat. use all anchor equally.

  • seomaster

    seomaster April 26th, 2021 @ 12:45 AM

    Your blogs further more each else volume is so entertaining further serviceable It appoints me befall retreat encore. I will instantly grab your rss feed to stay informed of any updates. casino online

  • seomaster

    seomaster April 26th, 2021 @ 04:55 AM

    I am interested in such topics so I will address page where it is cool described. casino online

  • xoslotxo

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