Rails ActiveRecord WHERE EXISTS query

Question

I have an SQL query that returns what I need, but I'm having trouble converting this into an active record query the "Rails way".

My SQL query is:

SELECT * from trips 
WHERE trips.title LIKE "%Thailand%"
AND EXISTS (SELECT * from places WHERE places.trip_id = trips.id AND places.name LIKE "%Bangkok%")
AND EXISTS (SELECT * from places WHERE places.trip_id = trips.id AND places.name LIKE "%Phuket%")

I'm trying something like this using Rails:

@trips=Trip.where("trips.title LIKE ?", "%Thailand%")
@[email protected](:places).where(("places.name LIKE ?","%Bangkok%").exists?) => true, ("places.name LIKE ?","%Phuket%").exists?) => true)

But it doesn't seem to work and i'm stumped as to what to try.

Answer 1

RAILS 5/6 EDIT: As of https://github.com/rails/rails/pull/29619, Rails started discouraging the direct .exists call from my original answer. I've updated it to use the new syntax, which invokes the arel proxy first (.arel.exists). Also, as of Rails 5, hash conditions work just fine within the EXISTS clause.

With all that taken into consideration, the pure ActiveRecord approach is now:

Trip.where("trips.title LIKE ?", "%Thailand%")
    .where( Place.where('trip_id = trips.id AND name LIKE ?', '%Bangkok%').arel.exists )
    .where( Place.where('trip_id = trips.id AND name LIKE ?', '%Phuket%').arel.exists )

If that looks a little scary, you do have some other options:

• You could just use .where('EXISTS(SELECT 1 FROM places WHERE trip_id = trips.id AND name LIKE ?', '%Bangkok%'), embedding the SQL into your application directly. It's not as hip or cool, but in the long run it may be more maintainable -- it's very unlikely to be deprecated or stop working with future versions of rails.
• Pick a gem, like activerecord_where_assoc, which makes the syntax cleaner and may save you from making simple mistakes (like not scoping your EXISTS query correctly, as my original answer did).

Answer 2

Using Where Exists gem (fair note: I'm its author):

Trip.where("trips.title LIKE ?", "%Thailand%")
  .where_exists(:places, ['name LIKE ?', '%Bangkok%'])
  .where_exists(:places, ['name LIKE ?', '%Phuket%'])