How to fetch distinct values with arel/relational algebra

jemmons picture jemmons · Jun 28, 2010 · Viewed 11.2k times · Source

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!

Answer

maerics picture maerics · Sep 18, 2012

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.