search

jackc / surus

PostgreSQL extensions for ActiveRecord
MIT License
395 stars 35 forks source link

Prefix column name with table names in query conditions #21

Closed krzyzak closed 8 years ago

krzyzak commented 8 years ago

Hi,

First of all – thanks for creating this gem, it’s really useful in my project.

I recently encountered one issue though. I’ve got Blog and BlogPost models with one–to–many relation. Both models contains column "flag" (array). When I’m searching for BlogPost.array_has(:flag, ["spam"]), everything works just fine. Problem is with column ambiguity when I’m joining Blog:

BlogPost.joins(:blog).where(scanned: true).array_has(:flags, ["spam"]).limit(5)
PG::AmbiguousColumn: ERROR:  column reference "flags" is ambiguous
LINE 1: ...."blog_id" WHERE "blog_posts"."scanned" = $1 AND ("flags" @>...
                                                             ^

In my PR, instead of using "flags" @> ARRAY['spam']::character it uses "blog_posts"."flags" which solves that issue.

The only thing which I’m not really sure about is how to add ability to search by blog.flags – it would probably require some API change (eg. (array_has(blog: { flags: ["spam"] }, blog_posts: { flag: ["casino"] }) ) – what do you think about that?

jackc commented 8 years ago

Thanks. Merged and released new gem version with the fix.

As far as enhancing the queries to support search by joined table, the most important criteria to me would be to ensure backwards compatibility. The example API you gave is congruent with the normal Rails API is a plus, but it implies some functionality that might be complicated to implement. array_has(blog: { flags: ["spam"], otherflags: ["important"] }). A simpler API might be array_has(:blog, :flags, ["spam"]) If there are 3 args we use the 1st as the table name. Probably worth thinking more about this -- this was just my initial thoughts.