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.

Solution 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).

Solution 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%'])

Solution 3

This is a refactored version of my first attempt that results much closer to your intended target (my apologies for the first, misleading one).

In app/models/trip.rb:

scope :with_title, ->(title) { where(arel_table[:title].matches('%' + title + '%')) }

def self.has_place(place_name)
  _trips = Trip.arel_table
  _places = Place.arel_table
  where(Place.joins(:trip).where(_places[:name].eq(place_name)).exists)
end

Then you can write:

Trip.for_title('Thailand').has_place('Bangkok').has_place('Phuket')

which will give the following SQL:

SELECT "trips".* FROM "trips" WHERE ("trips"."title" LIKE '%Thailand%') AND (EXISTS (SELECT "places".* FROM "places" INNER JOIN "trips" ON "trips"."id" = "places"."trip_id" WHERE "places"."name" = 'Bangkok')) AND (EXISTS (SELECT "places".* FROM "places" INNER JOIN "trips" ON "trips"."id" = "places"."trip_id" WHERE "places"."name" = 'Phuket'))

This allows you flexibility in how you piece together the query without having to rely on hardcoded SQL in your query and is portable between database engines.

This solution assumes that that Place belongs_to Trip (and its complement). Adjust the joins clause if the Association(s) is/(are) different.

This solution was inspired in part by How to do "where exists" in Arel and Make search NOT case sensitive on my rails app.

Comments

[Josh Wood]

This is the solution closest to my original attempt and seems the simplest solution. The caveat you mentioned doesn't bother me since I will only need to use LIKE which requires the literal string (as far as I know).

[Edgar Ortega]

Didn't know this existed, looks great!

[Maxime Lapointe]

This answer actually appears to be wrong for the SQL that would be generated. The original query has places.trip_id = trips.id in the EXISTS, where as the generated one here doesn't, and has a random unneeded JOIN. As a result, it appears to just check that places with these names exist, and that Trip is linked to any place without condition. One more reason to actually use a gem for this :)

[Robert Nubel]

@MaximeLapointe thank you for pointing that out -- I'm surprised it took nearly five years! I have updated the answer to reflect Rails' current behavior, and also included a note about your gem.

[Muhammad Hashir Anwaar]

I see what you did there :)