sql - Únase a la misma mesa dos veces con condiciones

ruby-on-rails-4 rails-activerecord

Question

Hay situaciones en las que ActiveRecord establece el nombre de la tabla de alias si hay varias combinaciones con la misma tabla. Estoy atrapado en una situación donde estas uniones contienen ámbitos (usando ''fusionar'').

Tengo una relación de muchos a muchos:

Modelos nombre_tabla: users

Segundos modelos table_name: posts

Unir el nombre de la tabla: access_levels

Una publicación tiene muchos usuarios a través de access_levels y viceversa.

Tanto el modelo de usuario como el modelo de publicación comparten la misma relación:

has_many :access_levels, -> { merge(AccessLevel.valid) }

El alcance dentro del modelo AccessLevel se ve así:

  # v1
  scope :valid, -> {
    where("(valid_from IS NULL OR valid_from < :now) AND (valid_until IS NULL OR valid_until > :now)", :now => Time.zone.now)
  }

  # v2
  # scope :valid, -> {
  #   where("(#{table_name}.valid_from IS NULL OR #{table_name}.valid_from < :now) AND (#{table_name}.valid_until IS NULL OR #{table_name}.valid_until > :now)", :now => Time.zone.now)
  # }

Me gustaría llamar algo así:

Post.joins(:access_levels).joins(:users).where (...)

ActiveRecord crea un alias para la segunda unión (''access_levels_users''). Quiero hacer referencia a este nombre de tabla dentro del alcance ''válido'' del modelo AccessLevel.

V1 obviamente genera un PG::AmbiguousColumn -Error. V2 da como resultado el prefijo de ambas condiciones con access_levels. , que es semánticamente incorrecto

Así es como genero la consulta: (simplificado)

# inside of a policy
scope = Post.
  joins(:access_levels).
  where("access_levels.level" => 1, "access_levels.user_id" => current_user.id)

# inside of my controller
scope.joins(:users).select([
        Post.arel_table[Arel.star],
        "hstore(array_agg(users.id::text), array_agg(users.email::text)) user_names"
      ]).distinct.group("posts.id")

La consulta generada se ve así (usando el alcance v2 valid desde arriba):

SELECT "posts".*, hstore(array_agg(users.id::text), array_agg(users.email::text)) user_names

  FROM "posts"
  INNER JOIN "access_levels" ON "access_levels"."post_id" = "posts"."id" AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < ''2014-07-24 05:38:09.274104'') AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > ''2014-07-24 05:38:09.274132''))
  INNER JOIN "users" ON "users"."id" = "access_levels"."user_id"
  INNER JOIN "access_levels" "access_levels_posts" ON "access_levels_posts"."post_id" = "posts"."id" AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < ''2014-07-24 05:38:09.274675'') AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > ''2014-07-24 05:38:09.274688''))

  WHERE "posts"."deleted_at" IS NULL AND "access_levels"."level" = 4 AND "access_levels"."user_id" = 1 GROUP BY posts.id

ActiveRecord establece un alias apropiado ''access_levels_posts'' para la segunda combinación de la tabla access_levels. El problema es que el campo-scope fusionado prefija la columna con ''access_levels'' en lugar de ''access_levels_posts''. También traté de usar arel para generar el alcance:

# v3
scope :valid, -> {
  where arel_table[:valid_from].eq(nil).or(arel_table[:valid_from].lt(Time.zone.now)).and(
    arel_table[:valid_until].eq(nil).or(arel_table[:valid_until].gt(Time.zone.now))
  )
}

La consulta resultante sigue siendo la misma.

Answer 1

Mientras tanto, he podido resolver mi propio problema. Publicaré mi solución para ayudar a otros que están teniendo problemas similares.

Preámbulo: es un largo camino hacia la tierra prometida;)

Mantendré la configuración lo más corta posible:

#
# Setup
#
class Post < ActiveRecord::Base
  has_many :access_levels, -> { merge(AccessLevel.valid) }
  has_many :users, :through => :access_levels
end

class AccessLevel < ActiveRecord::Base
  belongs_to :post
  belongs_to :user

  scope :valid, -> {
    where arel_table[:valid_from].eq(nil).or(arel_table[:valid_from].lt(Time.zone.now)).and(
      arel_table[:valid_until].eq(nil).or(arel_table[:valid_until].gt(Time.zone.now))
    )
  }

  enum :level => [:publisher, :subscriber]
end

class User < ActiveRecord::Base
  has_many :access_levels, -> { merge(AccessLevel.valid) }
  has_many :users, :through => :access_levels
end

El objetivo original era llamar a algo como esto (para agregar condiciones adicionales, etc.):

Post.joins(:users).joins(:access_levels)

Eso da como resultado una consulta semánticamente incorrecta:

SELECT "posts".* FROM "posts"
  INNER JOIN "access_levels"
    ON "access_levels"."post_id" = "posts"."id"
      AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < ''2014-09-15 20:42:46.835548'')
      AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > ''2014-09-15 20:42:46.835688''))

  INNER JOIN "users"
    ON "users"."id" = "access_levels"."user_id"

  INNER JOIN "access_levels" "access_levels_posts"
    ON "access_levels_posts"."post_id" = "posts"."id"
      AND (("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < ''2014-09-15 20:42:46.836090'')
      AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > ''2014-09-15 20:42:46.836163''))

La segunda combinación utiliza un alias, pero la condición no es usar este alias.

Arel al rescate!

Construí todas las siguientes combinaciones con bare arel en lugar de confiar en ActiveRecord. Lamentablemente, parece que combinar ambos no siempre funciona como se esperaba. Pero al menos está funcionando de esa manera. Estoy usando uniones externas en este ejemplo, así que tendré que construirlas yo solo de todos modos. Además, todas esas consultas se almacenan dentro de las políticas (usando Pundit). Por lo tanto, son fácilmente comprobables y no hay un controlador de grasa o redundancia. Así que estoy bien con un código adicional.

#
# Our starting point ;)
#
scope = Post

#
# Rebuild `scope.joins(:users)` or `scope.joins(:access_levels => :user)`
# No magic here.
#
join = Post.arel_table.join(AccessLevel.arel_table, Arel::Nodes::OuterJoin).on(
  Post.arel_table[:id].eq(AccessLevel.arel_table[:post_id]).
  and(AccessLevel.valid.where_values)
).join_sources
scope = scope.joins(join)

join = AccessLevel.arel_table.join(User.arel_table, Arel::Nodes::OuterJoin).on(
  AccessLevel.arel_table[:user_id].eq(User.arel_table[:id])
).join_sources

scope = scope.joins(join)

#
# Now let''s join the access_levels table for a second time while reusing the AccessLevel.valid scope.
# To accomplish that, we temporarily swap AccessLevel.table_name
#
table_alias            = ''al''                           # This will be the alias
temporary_table_name   = AccessLevel.table_name         # We want to restore the original table_name later
AccessLevel.table_name = table_alias                    # Set the alias as the table_name
valid_clause           = AccessLevel.valid.where_values # Store the condition with our temporarily table_name
AccessLevel.table_name = temporary_table_name           # Restore the original table_name

#
# We''re now able to use the table_alias combined with our valid_clause
#
join = Post.arel_table.join(AccessLevel.arel_table.alias(table_alias), Arel::Nodes::OuterJoin).on(
  Post.arel_table[:id].eq(AccessLevel.arel_table.alias(table_alias)[:post_id]).
  and(valid_clause)
).join_sources

scope = scope.joins(join)

Después de toda la sangre, el sudor y las lágrimas, aquí está nuestra consulta resultante:

SELECT "posts".* FROM "posts" 
  LEFT OUTER JOIN "access_levels"
    ON "posts"."id" = "access_levels"."post_id"
      AND ("access_levels"."valid_from" IS NULL OR "access_levels"."valid_from" < ''2014-09-15 20:35:34.420077'')
      AND ("access_levels"."valid_until" IS NULL OR "access_levels"."valid_until" > ''2014-09-15 20:35:34.420189'') 

  LEFT OUTER JOIN "users"
    ON "access_levels"."user_id" = "users"."id" 

  LEFT OUTER JOIN "access_levels" "al"
    ON "posts"."id" = "al"."post_id"
    AND ("al"."valid_from" IS NULL OR "al"."valid_from" < ''2014-09-15 20:35:41.678492'')
    AND ("al"."valid_until" IS NULL OR "al"."valid_until" > ''2014-09-15 20:35:41.678603'')

¡Todas las condiciones ahora usan un alias apropiado!

Answer 2

Me encontré con esta pregunta cuando buscaba cosas como esta. Sé que es una respuesta tardía, pero si alguien más tropieza aquí quizás esto pueda ser de alguna ayuda. Esto funciona en Rails 4.2.2, quizás esto no podría hacerse cuando se hizo la pregunta.

Esta respuesta fue inspirada por la respuesta de @dgilperez, pero un poco simplificada. También usando el alcance correcto. Asi que aqui esta.

class Post < ActiveRecord::Base
  # the scope of the used association must be used
  has_many :access_levels, -> { merge(AccessLevel.valid(current_scope)) }
  has_many :users, :through => :access_levels
end

class AccessLevel < ActiveRecord::Base
  belongs_to :post
  belongs_to :user

  # have an optional parameter for another scope than the scope of this class
  scope :valid, ->(cur_scope = nil) {
    # ''current_scope.table'' is the same as ''current_scope.arel.source.left'',
    # and there is no need to investigate if it''s an alias or not.
    ar_table = cur_scope && cur_scope.table || arel_table
    now = Time.zone.now
    where(
      ar_table[:valid_from].eq(nil).or(ar_table[:valid_from].lt(now)).and(
      ar_table[:valid_until].eq(nil).or(ar_table[:valid_until].gt(now)))
    )
  }

  enum :level => [:publisher, :subscriber]
end

class User < ActiveRecord::Base
  # the scope of the used association must be used
  has_many :access_levels, -> { merge(AccessLevel.valid(current_scope)) }
  has_many :users, :through => :access_levels
end

Y no es necesario tenerlo en dos uniones

Post.joins(:users, :access_levels).first

Vi que también cambiaste a usar OUTER JOINs, puedes obtenerlo con:

Post.includes(:users, :access_levels).references(:users, :access_levels).first

Pero tenga en cuenta que el uso de includes no siempre utiliza una solicitud de SQL.

Answer 3

Después de mirar más de cerca este problema en una pregunta similar aquí , se me ocurrió una solución más simple y más limpia (a mis ojos) a esta pregunta. Estoy pegando aquí las partes relevantes de mi respuesta a la otra pregunta para completar, junto con su alcance.

El objetivo era encontrar una forma de acceder al objeto arel_table actual, con sus table_aliases si se están utilizando, dentro del alcance en el momento de su ejecución. Con esa tabla, podrá saber si el alcance se está utilizando dentro de un JOIN que tiene el nombre de tabla con alias (múltiples uniones en la misma tabla), o si, por otro lado, el alcance no tiene alias para el nombre de la tabla.

# based on your v2
scope :valid, -> {
  where("(#{current_table_from_scope}.valid_from IS NULL OR 
          #{current_table_from_scope}.valid_from < :now) AND 
         (#{current_table_from_scope}.valid_until IS NULL OR 
          #{current_table_from_scope}.valid_until > :now)", 
       :now => Time.zone.now) 
  }

def self.current_table_from_scope
  current_table = current_scope.arel.source.left

  case current_table
  when Arel::Table
    current_table.name
  when Arel::Nodes::TableAlias
    current_table.right
  else
    fail
  end
end

Estoy usando current_scope como el objeto base para buscar la tabla arel, en lugar de los intentos previos de usar self.class.arel_table o incluso relation.arel_table . Estoy llamando a source sobre ese objeto para obtener un Arel::SelectManager que a su vez le dará la tabla actual en el #left . En este momento hay dos opciones: que tiene allí una Arel::Table (sin alias, el nombre de la tabla está en #name ) o que tiene Arel::Nodes::TableAlias con el alias en su #right .

Si está interesado, aquí hay algunas referencias que utilicé más adelante:

• Una pregunta similar sobre SO , respondió con una tonelada de código, que podría usar en lugar de su capacidad hermosa y concisa.
• Este tema de Rails y este otro .