在rails中使用*用户的标签查询？

How is it possible to write a query that gives the following result:

如何编写一个给出以下结果的查询:

https://*.com/users/611696/hopstream?tab=tags

Given these models and relationships:

鉴于这些模型和关系:

User:
    has_many: questions
    has_many: answers

Questions:
    belongs_to: user
    has_and_belongs_to_many: tags
    has_many: answers

Answers:
    belongs_to: user
    belongs_to: question

Tags:
    has_and_belongs_to_many: questions

It should probably be in the User model, right?...

它可能应该在用户模型中,对吧?...

class User < MyModel
   def topics
      # get user's questions' topics
      # get user's answers' questions' topics
      # merge and remove duplicates
      # sort by user's activity on topic (group by of questions and answers user has posted where the question had that tag)
   end
end

Or is there a much simpler way to accomplish this?

或者有一种更简单的方法来实现这一目标吗?

1 个解决方案

#1

In my opinion ORM of any kind is good for retrieval of records based on some (simple enough) criteria and traversing associations. For more complicated reporting stuff like this don't hesitate to use pure SQL, it's just better suited for this.

在我看来,任何类型的ORM都有利于基于一些(简单的)标准和遍历关联来检索记录。对于更复杂的报告这样的东西,不要犹豫使用纯SQL,它更适合这个。

Below is untested, but should point in the right direction:

以下是未经测试的,但应指向正确的方向:

class User < MyModel
  def topics
    connection.execute <<SQL
      SELECT tag, count(*)
      FROM questions_tags
      WHERE question_id IN (
        SELECT id
        FROM questions
        WHERE user_id = #{id}
        UNION ALL
        SELECT questions.id
        FROM questions JOIN answers ON (question.id = answers.question_id)
        WHERE answers.user_id = #{id}
      )
      GROUP BY tag;
    SQL
  end
end