search by association causes problem with generated SQL #73

Closed
adamblum opened this Issue Oct 21, 2012 · 7 comments

Comments

3 participants
@adamblum

I have a model called Resource with a postgres fulltext indexed field called title

pg_search_scope :search_title,:against=>:title

Searches against title work fine.

@resources=@resources.search_title(params[:title]).scoped

generates

SELECT "resources".*, (ts_rank((to_tsvector('simple', coalesce("resources"."title"::text, ''))),    (to_tsquery('simple', ''' ' || 'algebra' || ' ''')), 0)) AS pg_search_rank FROM "resources" WHERE (((to_tsvector('simple', coalesce("resources"."title"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'algebra' || ' ''')))) ORDER BY pg_search_rank DESC, "resources"."id" ASC LIMIT 20

Resources have and belong to many Grades. And the search just based on grades works fine

@resources=@resources.where(:grades=>{:grade=>params[:grade]}).scoped

generates:

   SELECT DISTINCT "resources".id FROM "resources" LEFT OUTER JOIN "grades_resources" ON "grades_resources"."resource_id" = "resources"."id" LEFT OUTER JOIN "grades" ON "grades"."id" = "grades_resources"."grade_id" WHERE "grades"."grade" = 'K' LIMIT 20
  SQL (3.1ms)  SELECT "resources"."id" AS t0_r0, "resources"."title" AS t0_r1, "resources"."site_name" AS t0_r2, "resources"."url" AS t0_r3, "resources"."asset_type" AS t0_r4, "resources"."rating" AS t0_r5, "resources"."duration" AS t0_r6, "resources"."creator" AS t0_r7, "resources"."rights" AS t0_r8, "resources"."views" AS t0_r9, "resources"."download_url" AS t0_r10, "resources"."subject" AS t0_r11, "resources"."video_encoding" AS t0_r12, "resources"."published" AS t0_r13, "resources"."contribution_id" AS t0_r14, "resources"."created_at" AS t0_r15, "resources"."updated_at" AS t0_r16, "resources"."resource_type" AS t0_r17, "resources"."description" AS t0_r18, "resources"."language" AS t0_r19, "resources"."picture_file_name" AS t0_r20, "resources"."picture_content_type" AS t0_r21, "resources"."picture_file_size" AS t0_r22, "resources"."picture_updated_at" AS t0_r23, "resources"."embeddable" AS t0_r24, "resources"."image" AS t0_r25, "grades"."id" AS t1_r0, "grades"."grade" AS t1_r1, "grades"."lowage" AS t1_r2, "grades"."highage" AS t1_r3, "grades"."leaf" AS t1_r4, "grades"."created_at" AS t1_r5, "grades"."updated_at" AS t1_r6 FROM "resources" LEFT OUTER JOIN "grades_resources" ON "grades_resources"."resource_id" = "resources"."id" LEFT OUTER JOIN "grades" ON "grades"."id" = "grades_resources"."grade_id" WHERE "grades"."grade" = 'K' AND "resources"."id" IN (37320, 35191, 37333, 37370, 37397, 37315, 37407, 37361, 37386, 37357, 37638, 37401, 37394, 37400, 37343, 37674, 37375, 37368, 37369, 37336)

But COMBINING these searches generates bad SQL. Specifically I get

SELECT  DISTINCT "resources".id, pg_search_rank, "resources"."id" AS alias_0 FROM "resources" LEFT OUTER JOIN "grades_resources" ON "grades_resources"."resource_id" = "resources"."id" LEFT OUTER JOIN "grades" ON "grades"."id" = "grades_resources"."grade_id" WHERE "grades"."grade" = 'K' AND (((to_tsvector('simple', coalesce("resources"."title"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'math' || ' ''')))) ORDER BY pg_search_rank DESC, "resources"."id" ASC LIMIT 20):

and I get an error from Postgres that pg_search_rank does not exist:

ActiveRecord::StatementInvalid (PG::Error: ERROR:  column "pg_search_rank" does not exist
LINE 1: SELECT  DISTINCT "resources".id, pg_search_rank, "resources"...

From perusing the discussion groups it appears that there have been problems in this area in the past with the pg_search gem. I think this may be a pg_search gem bug. What do you think?

@nertzy

This comment has been minimized.

Show comment Hide comment
@nertzy

nertzy Oct 22, 2012

Collaborator

When you say you combined the two, does that mean you chained one right after the other?

Like this?

@resources=@resources.search_title(params[:title]).scoped
@resources=@resources.where(:grades=>{:grade=>params[:grade]}).scoped

Also, what is the value of @resources before these lines? It might be easier to understand what's going on if you could condense it to a single chain of method calls. Is it something like this?

Resource.search_title(params[:title]).scoped.where(:grades=>{:grade=>params[:grade]}).scoped

Also, last but not least, the calls to scoped are superfluous. You can take them out and nothing should change. That method is only useful for getting the "base" scope out of a model when you want a scope and are not going to chain anything onto it. For example, Resource.scoped gives the default scope for the model, where Resource gives you the actual model class.

So, does this code work?

Resource.search_title(params[:title]).where(:grades=>{:grade=>params[:grade]})
Collaborator

nertzy commented Oct 22, 2012

When you say you combined the two, does that mean you chained one right after the other?

Like this?

@resources=@resources.search_title(params[:title]).scoped
@resources=@resources.where(:grades=>{:grade=>params[:grade]}).scoped

Also, what is the value of @resources before these lines? It might be easier to understand what's going on if you could condense it to a single chain of method calls. Is it something like this?

Resource.search_title(params[:title]).scoped.where(:grades=>{:grade=>params[:grade]}).scoped

Also, last but not least, the calls to scoped are superfluous. You can take them out and nothing should change. That method is only useful for getting the "base" scope out of a model when you want a scope and are not going to chain anything onto it. For example, Resource.scoped gives the default scope for the model, where Resource gives you the actual model class.

So, does this code work?

Resource.search_title(params[:title]).where(:grades=>{:grade=>params[:grade]})
@adamblum

This comment has been minimized.

Show comment Hide comment
@adamblum

adamblum Oct 22, 2012

Yes, you are right this can be simpler to illustrate the problem. In the example above @resources=Resource.scoped. But to highlight the specific problem here:

 Resource.search_title('math').where(:grades=>{:grade=>'K'})

results in:

SELECT "resources".*, (ts_rank((to_tsvector('simple', coalesce("resources"."title"::text, ''))), (to_tsquery('simple', ''' ' || 'math' || ' ''')), 0)) AS pg_search_rank FROM "resources" WHERE "grades"."grade" = 'K' AND (((to_tsvector('simple', coalesce("resources"."title"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'math' || ' ''')))) ORDER BY pg_search_rank DESC, "resources"."id" ASC

which fails of course because the GRADES table is not in the FROM clause.

Yes, you are right this can be simpler to illustrate the problem. In the example above @resources=Resource.scoped. But to highlight the specific problem here:

 Resource.search_title('math').where(:grades=>{:grade=>'K'})

results in:

SELECT "resources".*, (ts_rank((to_tsvector('simple', coalesce("resources"."title"::text, ''))), (to_tsquery('simple', ''' ' || 'math' || ' ''')), 0)) AS pg_search_rank FROM "resources" WHERE "grades"."grade" = 'K' AND (((to_tsvector('simple', coalesce("resources"."title"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'math' || ' ''')))) ORDER BY pg_search_rank DESC, "resources"."id" ASC

which fails of course because the GRADES table is not in the FROM clause.

@adamblum

This comment has been minimized.

Show comment Hide comment
@adamblum

adamblum Oct 23, 2012

Any ideas? Is this a confirmed bug? Is it easy to fix?

Any ideas? Is this a confirmed bug? Is it easy to fix?

@adamblum

This comment has been minimized.

Show comment Hide comment
@adamblum

adamblum Oct 26, 2012

Sorry I realize that I didn't precisely answer your (Grant's) question. No the code

Resource.search_title(params[:title]).where(:grades=>{:grade=>params[:grade]})

does not work. It generates (where params[:title] is "math" and params[:grade] is "K") the following:

SELECT "resources".*, (ts_rank((to_tsvector('simple', coalesce("resources"."title"::text, ''))), (to_tsquery('simple', ''' ' || 'math' || ' ''')), 0)) AS pg_search_rank FROM "resources" WHERE "grades"."grade" = 'K' AND (((to_tsvector('simple', coalesce("resources"."title"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'math' || ' ''')))) ORDER BY pg_search_rank DESC, "resources"."id" ASC

If you confirm its a bug we may be able to dive in and try to fix it and make a pull request. Don't want to do that unless you confirm that its a bug.

Sorry I realize that I didn't precisely answer your (Grant's) question. No the code

Resource.search_title(params[:title]).where(:grades=>{:grade=>params[:grade]})

does not work. It generates (where params[:title] is "math" and params[:grade] is "K") the following:

SELECT "resources".*, (ts_rank((to_tsvector('simple', coalesce("resources"."title"::text, ''))), (to_tsquery('simple', ''' ' || 'math' || ' ''')), 0)) AS pg_search_rank FROM "resources" WHERE "grades"."grade" = 'K' AND (((to_tsvector('simple', coalesce("resources"."title"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'math' || ' ''')))) ORDER BY pg_search_rank DESC, "resources"."id" ASC

If you confirm its a bug we may be able to dive in and try to fix it and make a pull request. Don't want to do that unless you confirm that its a bug.

@nertzy

This comment has been minimized.

Show comment Hide comment
@nertzy

nertzy Oct 26, 2012

Collaborator

How about this?

Resource.search_title(params[:title]).joins(:grades).where(:grades=>{:grade=>params[:grade]})

Which assumes that you have a has_many :grades in Resource.

I thought that you always need to use the joins method when adding conditions across tables.

Collaborator

nertzy commented Oct 26, 2012

How about this?

Resource.search_title(params[:title]).joins(:grades).where(:grades=>{:grade=>params[:grade]})

Which assumes that you have a has_many :grades in Resource.

I thought that you always need to use the joins method when adding conditions across tables.

@Peeja

This comment has been minimized.

Show comment Hide comment
@Peeja

Peeja Mar 11, 2013

Collaborator

@adamblum Does adding the join in @nertzy's suggestion above fix the issue?

Collaborator

Peeja commented Mar 11, 2013

@adamblum Does adding the join in @nertzy's suggestion above fix the issue?

@nertzy

This comment has been minimized.

Show comment Hide comment
@nertzy

nertzy May 15, 2015

Collaborator

Closing this out because queries are structured differently in pg_search 1.0, which should address the original problem. Feel free to reopen or create a new issue if this is not the case.

Collaborator

nertzy commented May 15, 2015

Closing this out because queries are structured differently in pg_search 1.0, which should address the original problem. Feel free to reopen or create a new issue if this is not the case.

@nertzy nertzy closed this May 15, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment