How to fetch distinct values with arel/relational algebra

I'm doing开发者_运维问答 my best to bend my brain around arel and the relational algebra behind it, but how to represent a SELECT DISTINCT is consistently eluding my comprehension. Can anyone explain how to arel:

SELECT DISTINCT title FROM posts; 

Many thanks!

Using pure Arel (not Rails/ActiveRecord) there is a "distinct" method:

Arel::VERSION # => '3.0.2'
posts = Arel::Table.new(:posts)
posts.project(posts[:title])
posts.distinct
posts.to_sql # => 'SELECT DISTINCT "posts"."title" FROM "posts"'

Curiously, the "distinct" method is not chainable, per the other Arel methods.

The Arel way to do it is:

t = Arel::Table.new(:foo)
count_distinct = t[:field].count(true)
count_distinct.to_sql # => "COUNT(DISTINCT `foo`.`field`)"

The previous answer is the Rails way, no? Not the Arel way.

This works for arel 1.x:

posts = Table(:posts)
posts.project(Arel::Distinct.new(posts[:title]))

I'd guess there's another "more correct" way to do this via the API but I haven't figured that out yet.

If you are doing this using a scope:

  scope :recent, lambda {|count|
    select("DISTINCT posts.*").
    joins(:whatever).
    limit(count).
    order("posts.updated_at DESC")
  }

Post.select('DISTINCT title')

Update 1:

At the time of the post, this was not available in Arel. These days, ActiveRecord::QueryMethods has the uniq method (http://apidock.com/rails/ActiveRecord/QueryMethods/uniq), so you'd want:

Post.select(:title).uniq

Update 2: Looks like Arel now supports this behavior. @maerics has the correct answer. I'd delete this if it wasn't the accepted answer.

Since AREL always uses SET in it's operation, duplicate row results will be deleted automatically. Just use a normal Project (Phi) operation.