CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordruby-on-rails-4
Jacob
Jacob

Reputation: 6487

Converting a Postgres query to Rails ActiveRecord?

Is it possible to write the following query in Rail's ActiveRecord format? I've tried a billion different ways with arel but can't get the same results.

SELECT topic_id, count(*) as total
FROM questions_topics
WHERE question_id IN (
  SELECT id
  FROM questions
  WHERE user_id = 1000
  UNION ALL
  SELECT questions.id
  FROM questions JOIN answers ON (questions.id = answers.question_id)
  WHERE answers.user_id = 1000
)
GROUP BY topic_id
ORDER BY total DESC;

Upvotes: 1

Views: 967

Answers (1)

Ismael
Ismael

Reputation: 16730

Well, this should work. Not exactly the same thing but should give same results.

QuestionTopic.where(
  "question_id IN (?) OR question_id IN (?)",
  Question.where(user_id: 1000).select(:id),
  Answer.where(user_id: 1000).select(:question_id)
).group('topic_id')
 .order('total desc')
 .select('topic_id, count(*) as total')

Upvotes: 2

Related Questions