Converting a Postgres query to Rails ActiveRecord?

Question

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;

I'd start by converting it to EXISTS (...) OR EXISTS (...) form, which will probably be more efficient than a UNION. — joop
– joop, Commented May 16, 2014 at 16:49
Maybe this talk will help you understand Arel: youtube.com/watch?v=ShPAxNcLm3o It explains how to convert any SQL query to Arel AST — Hnatt
– Hnatt, Commented May 16, 2014 at 19:23
Why do you want to convert it? One piece of SQL will probably be a lot clearer than an incomprehensible mess of Arel calls. — mu is too short
– mu is too short, Commented May 17, 2014 at 0:37
@muistooshort Since using Arel could allow rails to utilize lazy loading. I might be incorrect. — Jacob
– Jacob, Commented May 17, 2014 at 0:42

Ismael · Accepted Answer · 2014-05-17 00:36:18Z

2

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

answered May 17, 2014 at 0:36

Ismael

16.7k6 gold badges63 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting a Postgres query to Rails ActiveRecord?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related