Как найти пользователя, у которого есть кошка и собака?

Я хочу выполнить поиск по двум таблицам, которые имеют отношение "много к одному", например

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

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

Теперь скажем, что у меня есть такие данные, как

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

Что я хочу сделать, так это создать активный запрос записи, который вернет пользователя с кошкой и собакой (то есть пользователь 1 - Боб).

Моя попытка сделать это пока

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

Теперь я понимаю, почему это не работает - он ищет питомца, которое является и собакой, и кошкой, так что ничего не возвращает. Я не знаю, как изменить это, чтобы дать мне ответ, который я хочу, однако. У кого-нибудь есть предложения? Кажется, это должно быть легко - это не выглядит особенно необычной ситуацией.

--- редактировать ---

Просто добавив немного кода к этому вопросу, поскольку я только что открыл Squeel. Это позволяет создать такой подзапрос,

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

Это то, что найдет свой путь в моем приложении.

Ответы

Ответ 1

Используйте подвыборки для ограничения результатов:

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')

Ответ 2

Andomar - К сожалению, запись такого запроса не обязательно всегда будет работать по желанию. В частности, наличие 2 кошек приведет к появлению пользователя, и наличие 3 домашних животных - скажем, 2 кошек и собаки - приведет к их исключению.
Я мало знаю об ActiveRecord, но следующий стандартный синтаксис SQL, который будет работать:

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

Это работает иначе, чем существующие версии, подсчитывая отдельный "тип" домашнего животного ('cat' versus 'dog').

Ответ 3

Обычный подход заключается в фильтрации для кошек OR собак в предложении where. Затем вы group by на user_id и требуете, чтобы результирующая группа having count(distinct pet.id) = 2.

Я не уверен, как вы выражаете having в ActiveRecord; этот пост, похоже, содержит обходное решение.

Ответ 4

для этого вопроса существует так много способов получить решение вы можете сделать так же, как и далее...

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

Ответ 5

Это должно быть полезно. попробуйте это

выберите u. * у пользователей u, pets p1 где u.id = p1.user_id И p1.animal = "cat" AND p1.user_id in (выберите user_id от домашних животных, где животное = "собака" )

Ответ 6

Я редко отвечаю на вопросы о SO, но я попробую.:)

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
                  ));

Ответ 7

Существует несколько способов сделать это: некоторые из вышеперечисленных действий будут работать; Кроме того, здесь есть несколько иной подход, который использует по существу "взгляды" - по существу, просто наследуя от вашего общего класса "домашних животных" на два отдельных класса (кошки и собаки).

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

Ответ 8

Вот еще одно решение. Бит больше Rails дружественный..

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

Прочтите эту статью об относительных достоинствах использования JOIN vs GROUP BY + HAVING для такого решения.

Обратитесь к этому вопросу SO, который подробно обсуждает эту проблему.

Ответ 9

вы можете получить этот результат различными способами.

Надеюсь, это поможет вам. попробуйте это,

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