sql - presentar - perro y gato juego

¿Cómo encuentro al usuario que tiene tanto un gato como un perro?

Question

Quiero hacer una búsqueda en 2 tablas que tienen una relación de varios a uno, por ejemplo

class User << ActiveRecord::Base
  has_many :pets
end

class Pet << ActiveRecord::Base
  belongs_to :users
end

Ahora digamos que tengo algunos datos como ese

users

id        name
1         Bob
2         Joe
3         Brian

pets

id        user_id  animal
1         1        cat
2         1        dog
3         2        cat
4         3        dog

Lo que quiero hacer es crear una consulta de registro activa que devuelva a un usuario que tenga tanto un gato como un perro (es decir, usuario 1 - Bob).

Mi intento de esto hasta ahora es

User.joins(:pets).where(''pets.animal = ? AND pets.animal = ?'',''dog'',''cat'')

Ahora entiendo por qué esto no funciona: está buscando una mascota que sea tanto un perro como un gato, por lo que no devuelve nada. Sin embargo, no sé cómo modificar esto para darme la respuesta que deseo. ¿Alguien tiene alguna sugerencia? Parece que debería ser fácil, no parece ser una situación especialmente inusual.

---editar---

Solo agregué un poco de coda a esta pregunta ya que acabo de descubrir a Squeel . Esto le permite crear una subconsulta como tal;

User.where{id.in(Pet.where{animal == ''Cat''}.select{user_id} & id.in(Pet.where{animal == ''Dog''}.select{user_id}))

Esto es lo que encontrará en mi aplicación.

Answer 1

puedes obtener este resultado de varias maneras.

Espero que te ayude probar esto,

SELECT id FROM (SELECT user_id AS id FROM users
INNER JOIN pets ON pets.user_id=users.id
GROUP BY pets.user_id,pets.animal
HAVING COUNT(pets.user_id)>0 AND (pets.animal=''cat'' OR pets.animal=''dog'')
)AS s GROUP BY id HAVING COUNT(id)>1

Answer 2

El enfoque habitual es filtrar gatos o perros en la cláusula where . Luego, user_id en user_id y exija que el grupo resultante having count(distinct pet.id) = 2 .

No estoy seguro de cómo se expresa having en ActiveRecord; esta publicación parece contener una solución.

Answer 3

Use sub-selects para restringir los resultados:

User.joins(:pets).where(
  ''id IN (SELECT user_id FROM pets WHERE animal = ?) AND
   id IN (SELECT user_id FROM pets WHERE animal = ?)'',
  ''cat'', ''dog'')

Answer 4

Esto debería ser útil. prueba esto

seleccione u. * de los usuarios u, mascotas p1 donde u.id = p1.user_id AND p1.animal = "cat" AND p1.user_id in (seleccione user_id de mascotas donde animal = ''perro'')

Answer 5

Raramente respondo preguntas sobre SO, pero lo intentaré. :)

SELECT name
FROM users
WHERE id IN (SELECT a.user_id
             FROM (
                    (SELECT user_id FROM pets WHERE animal = ''cat'') a
                    INNER JOIN
                    (SELECT user_id FROM pets WHERE animal = ''dog'') b
                    ON a.user_id = b.user_id
                  ));

Answer 6

Hay varias maneras de hacerlo: algunas de las anteriores funcionarán; también, aquí hay un enfoque ligeramente diferente que utiliza esencialmente ''vistas'', esencialmente, heredando de su clase genérica de ''mascotas'' en dos clases separadas (gatos y perros).

SELECT
   id,
   name
FROM
      users
   INNER JOIN
      (
      SELECT DISTINCT
         user_id as belongs_to
      FROM
         pets
      WHERE
         animal = ''dog''
      ) dog
   ON users.id = dog.belongs_to
   INNER JOIN
      (
      SELECT DISTINCT
         user_id as belongs_to
      FROM
         pets
      WHERE
         animal = ''cat''
      ) cat
   ON users.id = cat.belongs_to

Answer 7

para esta pregunta hay tantas maneras de obtener una solución que puede hacer lo siguiente también ...

select dog_table.user_name from 
(
    select * 
    FROM users,pets  
    where pets.user_id = users.id 
    and  pets.animal = ''dog''
) dog_table,
(
    select * 
    FROM users,pets  
    where pets.user_id = users.id 
    and  pets.animal = ''cat''
) cat_table
where dog_table.user_id = cat_table.user_id

Answer 8

Andomar: desafortunadamente, escribir la consulta de esa manera no siempre funcionará como se desee. Específicamente, tener 2 gatos hará que aparezca el usuario, y tener 3 mascotas, por ejemplo, 2 gatos y un perro, hará que se excluyan.
No sé mucho sobre ActiveRecord, pero la siguiente es la sintaxis SQL estándar que funcionaría:

SELECT users.id
FROM Users
JOIN (SELECT user_id 
      FROM Pets
      WHERE animal IN (''dog'', ''cat'')
      GROUP BY user_id
      HAVING COUNT(DISTINCT animal)) Pets
  ON Pets.user_id = Users.id

Esto funciona de manera diferente a las versiones existentes contando el "tipo" distinto de mascota ( ''cat'' versus ''dog'' ).

Answer 9

Aquí hay otra solución. Poco más Rails friendly ..

User.all(:select => "DISTINCT users.*",
 :joins=>[:pets, :pets],
 :conditions => ["pets.animal = ? AND pets_users.animal = ?", "cat", "dog"])

Lea este artículo sobre los méritos relativos de usar JOIN vs GROUP BY + HAVING para dicha solución.

Refiérase a esta pregunta SO que discute este problema en detalle.