ruby-on-rails - inverse_of - rails references

seleccione todos los registros que tengan alguna condición en la asociación has_many-Ruby On Rails

Question

Tengo un model profile.rb con la siguiente asociación

class User < ActiveRecord::Base
has_one :profile
end
class Profile < ActiveRecord::Base
has_many :skills
belongs_to :user
end

Tengo un model skills.rb con la siguiente asociación

class Skill < ActiveRecord::Base
belongs_to :profile
end

Tengo las siguientes entradas en la tabla de habilidades

id: name: profile_id:
====================================================
1 accounting 1
2 martial arts 2
3 law 1
4 accounting 2
5 journalist 3
6 administration 1

y así sucesivamente, ¿cómo puedo consultar todos los perfiles con, digamos, "contabilidad" y "administración" habilidades que serán perfil con ID 1 teniendo en cuenta la recodificación anterior. hasta ahora he intentado seguir

Profile.includes(:skills).where(skills: {name: ["accounting" , "administration"]} )

pero en lugar de encontrar el perfil con id 1 - Me da [ 1, 2 ] porque el perfil con id 2 posee "accounting" skills y está realizando una "IN" operation en la base de datos

Nota: Estoy usando postgresql y la pregunta no es solo acerca de una identificación específica del perfil como se describe (que usé solo como ejemplo) - La pregunta original es obtener todos los perfiles que contienen estas dos habilidades mencionadas.

Mi join activerecord dispara la siguiente consulta en postgres

SELECT FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" IN (''Accounting'', ''Administration'')

En la parte inferior de la página, la respuesta de Vijay Agrawal es algo que ya tengo en mi aplicación y tanto el suyo como el mío, uso de consulta en IN comodín que da como resultado identificaciones de perfil que contienen cualquiera de las habilidades mientras que mi pregunta es obtener identificadores de perfil que contienen ambas habilidades. . Estoy seguro de que debe haber una manera de arreglar esto en la misma forma de consulta que aparece en la pregunta original y tengo curiosidad por aprender de esa manera. Espero obtener más ayuda con ustedes, gracias

Para mayor claridad, quiero consultar todos los perfiles con múltiples habilidades en un modelo con has_many relación con el modelo de perfil, utilizando el Profile como tabla principal, no las skills

La razón de usar Profile como tabla principal es que en la paginación no quiero obtener todas las habilidades de la tabla relacionada, digamos 20_000 o más filas y luego filtrar de acuerdo con la columna profile.state . en cambio, a cualquiera le gustaría seleccionar solo 5 records que cumplan con el profile.state , profile.user.is_active and other columns condition y relacionan las habilidades sin recuperar miles de registros irrelevantes y luego los filtran nuevamente.

Answer 1

Profile.includes(:skills).where("skills.name" => %w(accounting administration))

Para obtener más información, lea sobre encontrar a través de asociaciones de ActiveRecord .

Actualizar

Si esto no funciona para usted, es probable que no tenga su base de datos y modelos configurados correctamente, porque en una nueva aplicación de Rails esto funciona como se esperaba.

class CreateProfiles < ActiveRecord::Migration[5.1]
def change
create_table :profiles do |t|
t.timestamps
end
end
end
class CreateSkills < ActiveRecord::Migration[5.1]
def change
create_table :skills do |t|
t.string :name
t.integer :profile_id
t.timestamps
end
end
end
class Profile < ApplicationRecord
has_many :skills
end
class Skill < ApplicationRecord
belongs_to :profile
end
Profile.create
Profile.create
Skill.create(name: ''foo'', profile_id: 1)
Skill.create(name: ''bar'', profile_id: 1)
Skill.create(name: ''baz'', profile_id: 2)
Profile.includes(:skills).where("skills.name" => %w(foo))
SQL (0.3ms) SELECT DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = ''foo'' LIMIT ? [["LIMIT", 11]]
SQL (0.1ms) SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = ''foo'' AND "profiles"."id" = 1
=> #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">]>
Profile.includes(:skills).where("skills.name" => %w(bar))
SQL (0.3ms) SELECT DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = ''bar'' LIMIT ? [["LIMIT", 11]]
SQL (0.1ms) SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = ''bar'' AND "profiles"."id" = 1
=> #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">]>
Profile.includes(:skills).where("skills.name" => %w(baz))
SQL (0.3ms) SELECT DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = ''baz'' LIMIT ? [["LIMIT", 11]]
SQL (0.1ms) SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "skills" ON "skills"."profile_id" = "profiles"."id" WHERE "skills"."name" = ''baz'' AND "profiles"."id" = 2
=> #<ActiveRecord::Relation [#<Profile id: 2, created_at: "2017-07-28 21:53:34", updated_at: "2017-07-28 21:53:34">]>

Actualización 2

Bajar una respuesta porque usted cambió su pregunta más tarde es de mala calidad.

Debe cambiar las relaciones de su modelo de has_many y belongs_to a has_and_belongs_to_many . Esto le permitirá dejar de grabar una nueva habilidad cada vez; si alguien agrega la administration habilidades y más tarde alguien más agrega esa habilidad, no tiene que crear una nueva habilidad. Simplemente reutiliza la habilidad existente y la asocia con múltiples perfiles:

class Profile < ApplicationRecord
has_and_belongs_to_many :skills
end
class Skill < ApplicationRecord
has_and_belongs_to_many :profiles
end

Agregue una tabla de unión con un índice único (para que cada perfil pueda tener cada habilidad una vez y solo una vez):

class Join < ActiveRecord::Migration[5.1]
def change
create_table :profiles_skills, id: false do |t|
t.belongs_to :profile, index: true
t.belongs_to :skill, index: true
t.index ["profile_id", "skill_id"], name: "index_profiles_skills_on_profile_id_skill_id", unique: true, using: :btree
end
end
end

Crea tus modelos:

Profile.create
Profile.create
Skill.create(name: ''foo'')
Skill.create(name: ''bar'')
Skill.create(name: ''baz'')
Profile.first.skills << Skill.first
Profile.first.skills << Skill.second
Profile.second.skills << Skill.second
Profile.second.skills << Skill.third

Y luego ejecute su consulta para devolver solo el primer perfil:

skills = %w(foo bar).uniq
Profile.includes(:skills).where(''skills.name'' => skills).group(:id).having("count(skills.id) >= #{skills.size}")
SQL (0.4ms) SELECT DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN (''foo'', ''bar'') GROUP BY "profiles"."id" HAVING (count(skills.id) = 2) LIMIT ? [["LIMIT", 11]]
SQL (0.2ms) SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN (''foo'', ''bar'') AND "profiles"."id" = 1 GROUP BY "profiles"."id" HAVING (count(skills.id) = 2)
=> #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">]>

Confirmar con pruebas adicionales:

Debería devolver ambos perfiles:

skills = %w(bar).uniq
Profile.includes(:skills).where(''skills.name'' => skills).group(:id).having("count(skills.id) >= #{skills.size}")
SQL (0.4ms) SELECT DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" = ''bar'' GROUP BY "profiles"."id" HAVING (count(skills.id) >= 1) LIMIT ? [["LIMIT", 11]]
SQL (0.3ms) SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" = ''bar'' AND "profiles"."id" IN (1, 2) GROUP BY "profiles"."id" HAVING (count(skills.id) >= 1)
=> #<ActiveRecord::Relation [#<Profile id: 1, created_at: "2017-07-28 21:52:56", updated_at: "2017-07-28 21:52:56">, #<Profile id: 2, created_at: "2017-07-28 21:53:34", updated_at: "2017-07-28 21:53:34">]>

Debería devolver solo el segundo perfil:

skills = %w(bar baz).uniq
SQL (0.3ms) SELECT DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN (''bar'', ''baz'') GROUP BY "profiles"."id" HAVING (count(skills.id) >= 2) LIMIT ? [["LIMIT", 11]]
SQL (0.2ms) SELECT "profiles"."id" AS t0_r0, "profiles"."created_at" AS t0_r1, "profiles"."updated_at" AS t0_r2, "skills"."id" AS t1_r0, "skills"."name" AS t1_r1, "skills"."profile_id" AS t1_r2, "skills"."created_at" AS t1_r3, "skills"."updated_at" AS t1_r4 FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN (''bar'', ''baz'') AND "profiles"."id" = 2 GROUP BY "profiles"."id" HAVING (count(skills.id) >= 2)
=> #<ActiveRecord::Relation [#<Profile id: 2, created_at: "2017-07-28 21:53:34", updated_at: "2017-07-28 21:53:34">]>

No debe devolver ningún perfil:

skills = %w(foo baz).uniq
Profile.includes(:skills).where(''skills.name'' => skills).group(:id).having("count(skills.id) >= #{skills.size}")
SQL (0.3ms) SELECT DISTINCT "profiles"."id" FROM "profiles" LEFT OUTER JOIN "profiles_skills" ON "profiles_skills"."profile_id" = "profiles"."id" LEFT OUTER JOIN "skills" ON "skills"."id" = "profiles_skills"."skill_id" WHERE "skills"."name" IN (''foo'', ''baz'') GROUP BY "profiles"."id" HAVING (count(skills.id) >= 2) LIMIT ? [["LIMIT", 11]]
=> #<ActiveRecord::Relation []>

Answer 2

Debería hacer esto para obtener todos los profile_id que tengan habilidades de contabilidad y administración:

Skill.where(name: ["accounting", "administration"]).group(:profile_id).having("count(''id'') = 2").pluck(:profile_id)

Si necesita detalles de los perfiles, puede poner esta consulta en la cláusula where de Profile for id .

Tenga en cuenta el número 2 en la consulta, es la longitud de su matriz utilizada en la cláusula where. En este caso ["accounting", "administration"].length

ACTUALIZAR::

En función de la descripción actualizada de la pregunta, en lugar de pluck puede usar select y agregar subconsulta para asegurarse de que suceda en una consulta.

Profile.where(id: Skill.where(name: ["accounting", "administration"]).group(:profile_id).having("count(''id'') = 2").select(:profile_id))

Más sobre usted tiene control sobre clasificación, paginación y cláusula where adicional. No veo ninguna preocupación allí que se mencionan en la edición de la pregunta.

ACTUALIZACIÓN 2 ::

Otra forma de intersectar los perfiles con ambas habilidades (probablemente sea menos eficiente que la solución anterior):

profiles = Profile
["accounting", "administration"].each do |name|
profiles = profiles.where(id: Skill.where(name: name).select(:profile_id))
end

Answer 3

Solución dependiente de PostgreSQL :

where_clause = <<~SQL
ARRAY(
SELECT name FROM skills WHERE profile_id = profiles.id
) @> ARRAY[?]
SQL
Profile.where(where_clause, %w[skill1 skill2])

Funciona, pero tiene sentido cambiar la estructura de la base de datos para acelerar. Hay dos opciones:

• has_and_belongs_to_many añade consistencia (las tablas de skills se convierten en el diccionario) y la capacidad de usar índices
• skills como array | Columna de profile de jsonb : agrega búsqueda rápida por índice sin sub-selecciones o uniones.

Answer 4

El problema con la siguiente consulta

Profile.includes(:skills).where(skills: { name: ["accounting" , "administration"] } ) es que crea una consulta con el operador IN (''Accounting'', ''Administration'') como IN (''Accounting'', ''Administration'')

Ahora, según el estándar SQA, coincidirá con todos los registros que coincidan con cualquier valor y no con todos los valores de la matriz.

Aquí hay una solución más simple

skills = ["accounting" , "administration"]
Profile.includes(:skills).where(skills: { name: skills }).group(:profile_id).having("count(*) = #{skills.length}")

PS Esto supone que tendrás al menos una habilidad. Ajuste having condición según su uso

Answer 5

Utilizaría EXISTS con una EXISTS correlacionada , como esta:

required_skills = %w{accounting administration}
q = Profile.where("1=1")
required_skills.each do |sk|
q = q.where(<<-EOQ, sk)
EXISTS (SELECT 1
FROM skills s
WHERE s.profile_id = profiles.id
AND s.name = ?)
EOQ
end

Hay otras ideas en esta pregunta similar, pero creo que en su caso, varias cláusulas EXISTS son las más rápidas y las más simples.

(Por cierto, en Rails 4+ puedes comenzar con Profile.all lugar de Profile.where("1=1") , porque all devuelve una Relation , pero en los viejos tiempos solía devolver una matriz).