Aggregations Arel, Earl, External Join now?

Question

I have a model Factthat has_many :votes. Voices also have a field user_id. I would like to express the following within the framework of the model Fact: Give me all the facts that have 0 votes with user_idequal to X.

I am not familiar enough with Arel to understand how I can handle this. Ideas?

Answer 1

I decided to solve this problem in the following area:

  scope :not_voted_on_by_user, lambda {|user_id| select("distinct `facts`.*").joins("LEFT JOIN `votes` ON `facts`.id = `votes`.fact_id").where(["votes.user_id != ? OR votes.user_id IS NULL",user_id])}

Answer 2

It works:

class Fact < ActiveRecord::Base
  scope :by_user, lambda { |id| joins(:user).where('users.id == ?', id).readonly(false)    }
  scope :vote_count, lambda { |count| where('? == (select count(fact_id) from votes where votes.fact_id == facts.id)', count)}
end

Fact.by_user(1).vote_count(0)

The vote_count scope is a bit sqly, but you can link these crawlers as you like, you can also see the underlying sql:

Fact.by_user(1).vote_count(0).to_sql

, :

f = Arel::Table.new(:facts)
v = Arel::Table.new(:votes)
u = Arel::Table.new(:users)

sql.

sql = f.join(u).on(f[:user_id].eq(1)).where('0 == (select count(fact_id) from votes where votes.fact_id == facts.id)').to_sql

: f[:user_id].eq(1)

, :

Fact.find_by_sql(sql)

, , , ( " 0 ==..." ). , Rails3 Arel - http://m.onkey.org/active-record-query-interface