#68 add explicitly :includes in a search request - Searchlogic

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

#68 ✓resolved

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

You flagged this item as spam.
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 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 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

You flagged this item as spam.
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
You flagged this item as spam.
Deepu March 23rd, 2009 @ 10:28 AM
@nico :-

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

Cheers, Deepu.
You flagged this item as spam.
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 March 23rd, 2009 @ 05:07 PM

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

You flagged this item as spam.
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
You flagged this item as spam.
timur March 30th, 2009 @ 08:07 AM
any progress with this issue?
You flagged this item as spam.
nico March 30th, 2009 @ 08:15 AM
no. finally due to any reply , I switched to the normal find method too bad..
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.
You flagged this item as spam.
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.
You flagged this item as spam.
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.
You flagged this item as spam.
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 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.
You flagged this item as spam.
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 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.
You flagged this item as spam.
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
You flagged this item as spam.
seomaster April 14th, 2021 @ 08:20 AM
I invite you to the page where see how much we have in common. online casino
You flagged this item as spam.
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
You flagged this item as spam.
seomaster April 15th, 2021 @ 04:10 AM
I invite you to the page where see how much we have in common. casino online canada
You flagged this item as spam.
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,
You flagged this item as spam.
seomaster April 16th, 2021 @ 05:09 AM
I invite you to the page where see how much we have in common. website,
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
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,
You flagged this item as spam.
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.
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
seomaster April 22nd, 2021 @ 12:35 AM
I invite you to the page where see how much we have in common. keno online
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
seomaster April 24th, 2021 @ 08:48 PM
Why do only so much written on this subject? Here you see more. read more,
You flagged this item as spam.
seomaster April 24th, 2021 @ 09:39 PM
It is somewhat fantastic, and yet check out the advice at this treat. use all anchor equally.
You flagged this item as spam.
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
You flagged this item as spam.
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
You flagged this item as spam.
xoslotxo May 10th, 2021 @ 02:10 AM
g-club
slotxo
joker gaming
pgslot
คาสิโน
สล็อต

JOKER SLOT
SLOT JOKER

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.

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.

Keyword searching

add explicitly :includes in a search request

Comments and changes to this ticket

Create your profile

People watching this ticket

Pages