Query an association with an alias

Question

I have a users table and a tasks table with a model with the following association:

class Task < ActiveRecord::Base
  belongs_to :owner, class_name: User
end

I'm trying to build a query that will search the task's description and its owner's name as such:

includes(:owner).where("LOWER(tasks.description) LIKE ? OR LOWER(owner.name) LIKE ?", "%#{q.downcase}%", "%#{q.downcase}%")

...but I don't know how to properly query the users table for the task owners. In place of owner.name, I've tried users.name, tasks.users.name, and probably a few other things, all to no avail. How can I do this?

Note: I do not want to add a gem for this. I'm looking for a solution that is native to rails.

EDIT:

The foreign key as it exists in my schema.rb

add_foreign_key "tasks", "users", :name => "tasks_owner_id_fk", :column => "owner_id", :dependent => :nullify

SECOND EDIT:

I also need a solution that will return an AREL. I can get this to work by returning an array of objects, but I need to add other query methods to the result, so it has to be AREL.

score 0 · Answer 1 · answered Jul 05 '16 at 18:33

0

You need to include a foreign_key of owner_id if you want to call owner.name in your view or query.

Second, you should be able to still call user, but since it's a belongs_to, it would be user.name, not users.name

If you set up the foreign_key you can call owner.name like you did before and it will work.

This should help you set it up:

http://guides.rubyonrails.org/active_record_migrations.html#foreign-keys

answered Jul 05 '16 at 18:33

dtakeshta

214
2
4

The foreign key already exists - see my edit above. As for using `user.name` rather than `users.name`, note that this is inside a query, and so is referencing a table name, not an activerecord association method. – Michael Cruz Jul 05 '16 at 18:41
hit save by mistake – dtakeshta Jul 05 '16 at 18:49
try this: includes(:owner).where("LOWER(tasks.description) LIKE ? OR LOWER(tasks_owner_id_fk.name) LIKE ?", "%#{q.downcase}%", "%#{q.downcase}%" – dtakeshta Jul 05 '16 at 19:00
What's the error? Cause I use foreign keys in a few models and I mimicked your query and it worked for me. – dtakeshta Jul 05 '16 at 20:02
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "tasks_owner_id_fk" LINE 1: ...E (LOWER(tasks.description) LIKE '%and%' OR LOWER(tasks_owne... – Michael Cruz Jul 05 '16 at 20:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/116511/discussion-between-michael-cruz-and-dtakeshta). – Michael Cruz Jul 05 '16 at 20:09
I was finally able to find a solution. See my answer if you are curious. Thanks for putting some time into helping! – Michael Cruz Jul 05 '16 at 21:08

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

After digging around all day, this post helped me out, and my solution is the following:

joins("LEFT OUTER JOIN users ON users.id = tasks.owner_id").where("LOWER(users.name) LIKE ? OR
       LOWER(tasks.description) LIKE ?",
       "%#{q.downcase}%", "%#{q.downcase}%")

edited May 23 '17 at 12:32

Community

1
1

answered Jul 05 '16 at 21:06

Michael Cruz

864
6
13

Query an association with an alias

2 Answers2