current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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;
share|improve this question
    
Do you have relevant models like QuestionTopic and others? –  zishe May 16 '14 at 16:42
1  
I'd start by converting it to EXISTS (...) OR EXISTS (...) form, which will probably be more efficient than a UNION. –  joop May 16 '14 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 May 16 '14 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 May 17 '14 at 0:37
    
@muistooshort Since using Arel could allow rails to utilize lazy loading. I might be incorrect. –  Jacob May 17 '14 at 0:42

1 Answer 1

up vote 0 down vote

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')
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.