Rails how to get table results with a join on itself?

Question

I have the following Activity table:

ID      TYPE        SKU
=======================
1       create      ABC
2       create      DEF
3       destroy     DEF

How can I get all create events which DON'T have a destroy event?

Doing Activity.where(type: 'create') gives me ids 1 and 2.

But I only want the first row since id 2 also has a destroy event.

Unless you are doing STI, using Type as a column name is not recommended. — John Naegle, Jan 11 '14 at 05:01

Ju Liu · Accepted Answer · 2014-01-11T00:19:09.833

3

I guess this should work

Activity.where('activities.type = ? AND activities.sku NOT IN
  (SELECT sku from activities where type = ?)', 'create', 'destroy')

edited Jan 11 '14 at 00:19

answered Jan 10 '14 at 23:06

Ju Liu

3,939
13
18

Thanks. Is there a rails way of doing this as well? – Hopstream Jan 10 '14 at 23:08
http://stackoverflow.com/questions/4581376/how-to-exclude-an-array-of-ids-from-query-in-rails-using-activerecord – Brad Werth Jan 10 '14 at 23:15
This still gives back activities which have a 'destroy' record. – Hopstream Jan 10 '14 at 23:27
Ahh, had to change the id to sku.... `Activity.where('activities.type = ? AND activities.sku NOT IN (SELECT sku from activities where type = ?)', 'create', 'destroy')` – Hopstream Jan 10 '14 at 23:33

score 1 · Answer 2 · answered Jan 11 '14 at 05:08

1

IF you really want to use active record, this gets you closer:

class Activity < ActiveRecord::Base
  scope :with_destroy, lambda { where(:action => 'destroy') }
  scope :create, lambda {where(:action => 'create') }

  scope :create_without_destroy, lambda {
    create.where("sku not in (" << Activity.select(:sku).with_destroy.group(:sku).to_sql << ")")
  }

end

The generated SQL:

> Activity.create_without_destroy
SELECT "activities".* FROM "activities" 
WHERE "activities"."action" = 'create' AND (
  sku not in (
  SELECT 
    sku 
  FROM "activities" 
  WHERE "activities"."action" = 'destroy' 
  GROUP BY sku
))

answered Jan 11 '14 at 05:08

John Naegle

8,077
3
38
47

That's awesome. But the active record way seems like it would be hard for other devs to make sense of later. – Hopstream Jan 11 '14 at 05:12
Yes, I'd go with just the straight SQL, but you asked for a more "rails" way. – John Naegle Jan 13 '14 at 01:59

Rails how to get table results with a join on itself?

2 Answers2