Using constant as part of SQL JOIN condition in Rails

Question

I'm attempting to do a JOIN on two tables, using a partially constant value and a second constraint.

I have two models, car and driver. They both have a column name. The association is formed as such: A car has many drivers. The driver's name is the same as the car, appended with '_driver', limited to drivers whose hair is brown.

Example:

• A car has the name quick
• Its drivers have the name quick_driver, and have hair_color brown.

Following this post https://stackoverflow.com/a/20412163/1025846, here is my attempt with has_many:

has_many :drivers, -> (object) { 
                     where('name LIKE self.name%').
                     where("name LIKE %'?'", '_driver').
                     where(hair_color: 'brown') 
                   },
                   class_name: 'Animal::Driver'

This isn't working, and I'm at a loss of how to continue.

For simplification, I don't believe my issues have to do with the hair_color, so I believe this can be ignored.

Answer 1

Perhaps try something like:

has_many :drivers, -> (object){ 
    where("name LIKE '?'", object.name).
    where("name LIKE '?'", "#{object.name}_driver").
    where(hair_color: 'brown') }, 
  :class_name => 'Animal::Driver'

Note: bug-testing is left as an exercise to the reader ;) this code is to show you the kind of thing you might be able to try