How to fetch distinct values with arel/relational algebra

Question

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!

Senzer

I don't know arel but from my reading of "Database in Depth" by C.J.Date, in relational algebra the result of a query is a set of tuples. So if arel is following this theory than distinct should be the default. – Luben

Answer 1

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.

Jailbird

Not exactly algebraic, but hard to argue with it's efficiency ;-) – Senzer

There is a fatal problem with this approach: if you have more than one scope with a select statement, chaining them together can cause invalid SQL. – Scalpel

This is not AREL, and thus it doesn't answer the question. – Enrobe

Answer 2

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.

Tupungato

With Arel version, 5.0.1, this no longer works :(. – Unbridled

Arel 6, everything works perfectly. It's also chainable, as it's returning SelectManager. – Avalanche

arel 6.0.3 and this saved my day, though i used distinct_on – Armagnac

Answer 3

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`)"

Foret

This doesn't strictly answer the question but it does happen to answer my question: how do you express count(DISTINCT attr) using Arel. – Appurtenance

Answer 4

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.

Urban

This is true. This answer was for Arel 1.x and will no longer work. – Urban

Do you know what's the alternative? – Enrobe

Answer 5

If you are doing this using a scope:

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

Cotto

Answer 6

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

Dynameter

This is a nice idea in theory but it is patently false in reality. Arel queries will return duplicate entries from any projection unless constrained by the "distinct" method explicitly. – Tupungato