Scope with "WHERE ... LIKE" on a related table

Question

I'm trying to get data from a Postgresql table (table1) filtered by a field (property) of an other related table (table2).

In pure SQL I would write the query like this:

SELECT * FROM table1 JOIN table2 USING(table2_id) WHERE table2.property LIKE 'query%'

This is working fine:

scope :my_scope, ->(query) { includes(:table2).where("table2.property": query) }

But what I really need is to filter with a LIKE operator rather than strict equality. However this is not working:

scope :my_scope, ->(query) { includes(:table2).where("table2.property LIKE ?", "#{query}%") }

As I am getting this error:

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "table2" LINE 1: ...ble2" WHERE "table1"."user_id" = $1 AND (tabl... ^ : SELECT "table1".* FROM "table1" WHERE "table1"."user_id" = $1 AND (table2.property LIKE 'query%') ORDER BY last_used_at DESC

What am I doing wrong here?

Answer 1

.includes() usually runs 2 separate queries unless it can find that your conditions forces a single LEFT OUTER JOIN query, but it fails to do so in your case as the references are in a string (see this example).

You can force the single query behaviour by specifing .references(:table2):

scope :my_scope, ->(query) { includes(:table2)
                               .references(:table2)
                               .where("table2.property LIKE ?", "#{query}%") }

Or you can you can just use .eager_load():

scope :my_scope, ->(query) { eager_load(:table2)
                               .where("table2.property LIKE ?", "#{query}%") }