Clever Geek Handbook

Scope of application "WHERE ... LIKE" on the corresponding table

I am trying to get data from a Postgresql ( table1

) table filtered by field ( property

) of another 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 works fine:

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

But I really need to filter using the LIKE operator, not strict equality. However, this doesn't work:

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

How do I get 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?

+3


source to share


2 answers


.includes()

usually runs 2 separate queries, unless it can detect that your conditions are forcing a single query LEFT OUTER JOIN

, but it cannot do that in your case, since the links are in a string (see this example ).

You can enforce one request behavior by specifying .references(:table2)

:

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


Or you can just use .eager_load()

:

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


source


Try this method by adding []

to the request.

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


Also try adding (?)

.

0


source






More articles:


All Articles