sql - joins - relationship rails 5

Rieles de alcance para comprobar si la asociación NO existe.

Question

Estoy buscando escribir un alcance que devuelva todos los registros que no tengan una asociación en particular.

foo.rb

class Foo < ActiveRecord::Base    
  has_many :bars
end

bar.rb

class Bar < ActiveRecord::Base    
  belongs_to :foo
end

Quiero un alcance que pueda encontrar todos los Foo''s que no tengan bars . Es fácil encontrar a los que tienen una asociación mediante joins , pero no he encontrado la manera de hacer lo contrario.

Answer 1

Para Rails 5+ (Ruby 2.4.1 y Postgres 9.6)

Tengo 100 foos y 9900 bars . 99 de los foos tienen 100 bars cada uno, y uno de ellos no tiene ninguno.

Foo.left_outer_joins(:bars).where(bars: { foo_id: nil })

Produce una consulta SQL:

Foo Load (2.3ms)  SELECT  "foos".* FROM "foos" LEFT OUTER JOIN "bars" ON "bars"."foo_id" = "foos"."id" WHERE "bars"."foo_id" IS NULL

y devuelve el de Foo sin bars

La respuesta actualmente aceptada Foo.where.not(id: Bar.select(:foo_id).uniq) no está funcionando. Está produciendo dos consultas SQL:

Bar Load (8.4ms)  SELECT "bars"."foo_id" FROM "bars"
Foo Load (0.3ms)  SELECT  "foos".* FROM "foos" WHERE ("foos"."id" IS NOT NULL)

que devuelve todos los foos porque todos los foos tienen un id que no es nulo.

Se debe cambiar a Foo.where.not(id: Bar.pluck(:foo_id).uniq) para reducirlo a una consulta y encontrar nuestro Foo , pero tiene un bajo rendimiento en los puntos de referencia

require ''benchmark/ips''
require_relative ''config/environment''

Benchmark.ips do |bm|
  bm.report(''left_outer_joins'') do
    Foo.left_outer_joins(:bars).where(bars: { foo_id: nil })
  end

  bm.report(''where.not'') do
    Foo.where.not(id: Bar.pluck(:foo_id).uniq)
  end

  bm.compare!
end

Warming up --------------------------------------
    left_outer_joins     1.143k i/100ms
           where.not     6.000  i/100ms
Calculating -------------------------------------
    left_outer_joins     13.659k (± 9.0%) i/s -     68.580k in   5.071807s
           where.not     70.856  (± 9.9%) i/s -    354.000  in   5.057443s

Comparison:
    left_outer_joins:    13659.3 i/s
           where.not:       70.9 i/s - 192.77x  slower

Answer 2

Prefiero usar gema squeel para construir consultas complejas. Extiende ActiveRecord con tal magia:

Foo.where{id.not_in Bar.select{foo_id}.uniq}

que construye la siguiente consulta:

SELECT "foos".* 
FROM "foos" 
WHERE "foos"."id" NOT IN (
  SELECT DISTINCT "bars"."foo_id"
  FROM "bars" 
)

Asi que,

# in Foo class
scope :lonely, where{id.not_in Bar.select{foo_id}.uniq}

Es lo que puedes usar para construir el alcance solicitado.

Answer 3

Rails 4 hace esto muy fácil :)

Foo.where.not(id: Bar.select(:foo_id).uniq)

Esto genera la misma consulta que la respuesta de Jdoe.

SELECT "foos".* 
FROM "foos" 
WHERE "foos"."id" NOT IN (
  SELECT DISTINCT "bars"."foo_id"
  FROM "bars" 
)

Y como alcance:

scope :lonely, -> { where.not(id: Bar.select(:item_id).uniq) }

Answer 4

Usar NOT EXISTS con una subconsulta LIMITADA puede ser más rápido:

SELECT foos.* FROM foos
WHERE NOT EXISTS (SELECT id FROM bars WHERE bars.foo_id = foos.id LIMIT 1);

Con ActiveRecord (> = 4.0.0):

Foo.where.not(Bar.where("bars.foo_id = foos.id").limit(1).arel.exists)

Answer 5

en foo.rb

class Foo < ActiveRecord::Base    
  has_many :bars
  scope :lonely, lambda { joins(''LEFT OUTER JOIN bars ON foos.id = bars.foo_id'').where(''bars.foo_id IS NULL'') }
end