Rails 3: Arel for NOT EXISTS?

Question

How do you write a NOT EXISTS in Arel? I'm having trouble translating this query into Arel:

SELECT * FROM deals 
WHERE NOT EXISTS (
   SELECT 1 FROM reward_deals 
   WHERE reward_deals.deal_id = deal.id 
   AND NOT (
      reward_deals.awarding_type = 'deal' 
      AND reward_deals.deal_id = reward_deals.awarding_id
   )
 )

score 19 · Accepted Answer · edited Jul 27 '16 at 17:06

19

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)

edited Jul 27 '16 at 17:06

user229044

232,980
40
330
338

answered Aug 23 '11 at 16:43

Pedro Rolo

28,273
12
60
94

Still, my answer is answering your question, and my suposition is right. Why didn't you mark my answer as correct?! – Pedro Rolo Aug 24 '11 at 13:47
Gotta try it out for myself before I can make it as the answer. Looks like that does the trick. Thanks! – neezer Aug 31 '11 at 07:18

Rails 3: Arel for NOT EXISTS?

1 Answers1

Linked