How to Do "Where Exists" in Arel

How to do where exists in Arel

Here you go:

suppliers= Supplier.arel_table
orders= Order.arel_table
suppliers_with_orders = Supplier.where(
                          Order.where(orders[:supplier_id]
                                        .eq(suppliers[:id])).exists).to_sql =>
"SELECT `suppliers`.* FROM `suppliers` 
 WHERE (EXISTS (SELECT `orders`.* 
                FROM `orders` 
                WHERE `suppliers`.`id` = `orders`.`supplier_id`))"

Though, an inner join would do this in a more simple - and eventually less performant - way :

Supplier.joins :orders

Rails 3: Arel for NOT EXISTS?

Here is the answer, with strange names because I don't know how to give names for a domain that is for me unknown.

deals = Deal.arel_table
reward_deals = RewardDeal.arel_table
awarding_condition= reward_deals[:awarding_type].eq('deal')\
                                .and(reward_deals[:deal_id]\
                                       .eq(reward_deals[:awarding_id]))

reward_deals_condition= reward_deals[:deal_id].eq(deals[:id])\
                              .and(awarding_condition.not)
Deal.where(RewardDeal.where(reward_deals_condition).exists.not)

Rails ActiveRecord WHERE EXISTS query

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

Rails: Convert a complex SQL query to Arel or ActiveRecord

While we can certainly build your query in Arel, after reviewing your SQL a bit it looks like it would actually be much cleaner to simply build this using the AR API instead.

The following should produce the exact query you are looking for (sans "newsletter_stories"."author_id" = "group_members"."id" because this is already implied by the join)

class Newsletter::Author < Application Record
  belongs_to :newsletter, inverse_of: :authors
  belongs_to :group_member, class_name: "GroupMember", inverse_of: :authorships
  scope :without_submissions_for, ->(newsletter_id) {
    group_members = GroupMember
         .select(:id)
         .joins(:stories)
         .where(newsletter_stories: {status: 'draft'})
         .where.not( 
           Newsletter::Story
            .select(1)
            .where(status: 'submitted')
            .where(Newsletter::Story.arel_table[:author_id].eq(GroupMember.arel_table[:id]))
            .arel.exists
         ).distinct
    where(discarded_at: nil, newsletter_id: newsletter_id, group_member_id: group_members)
  }
end 

How to write an SQL NOT EXISTS query/scope in the Rails way?

This isn't actually a query that you can build with the ActiveRecord Query Interface alone. It can be done with a light sprinkling of Arel though:

class ProxyConfig < ApplicationRecord
  def self.current_versions
    pc = arel_table.alias("pc")
    where(
      unscoped.select(1)
         .where(pc[:environment].eq(arel_table[:environment]))
         .where(pc[:proxy_id].eq(arel_table[:proxy_id]))
         .where(pc[:version].gt(arel_table[:version]))
         .from(pc)
         .arel.exists.not
    )
  end
end

The generated SQL isn't identical but I think it should be functionally equivilent.

SELECT "proxy_configs".* FROM "proxy_configs" 
WHERE NOT (
  EXISTS (
    SELECT 1 FROM "proxy_configs" "pc" 
    WHERE "pc"."environment" = "proxy_configs"."environment" 
    AND "pc"."proxy_id" = "proxy_configs"."proxy_id" 
    AND "pc"."version" > "proxy_configs"."version"
  )
)

Rails3 Arel Building the Where Clause

What do you mean not execute? where only return a Relation object, you have to call all to get the result:

rec.all

How do you create and use functions in Arel that are not bound to a column?

You can use Arel::Nodes::NamedFunction.

This class initializes with the name of the function and an arguments array. The Vistor for a NamedFunction will assemble the SQL as FUNCTION_NAME(comma, separated, arguments).

Since NOW has no arguments all we need to do is pass an empty arguments array.

fn = Arel::Nodes::NamedFunction.new("NOW",[]) 
memberships = Arel::Table.new("memberships")
memberships.project(Arel.star).where(
  memberships[:expires].lt(fn)
).to_sql 
#=> "SELECT * FROM [memberships] WHERE [memberships].[expires] < NOW()"

Function appears to be a super class for inheritance purposes only, children include Sum,Exists,Min,Max,Avg, and NamedFunction (which allows you to call any other function by name as shown above)

---

Related Topics