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!

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. — Jörn Horstmann, Jun 28 '10 at 21:42

maerics · Answer 1 · 2012-09-18T21:34:56.893

16

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.

edited Sep 18 '12 at 21:34

answered Sep 18 '12 at 21:29

maerics

151,642
46
269
291

With Arel version, `5.0.1`, this no longer works :(. – Kurt Mueller Jan 19 '15 at 18:47
3

Arel 6, everything works perfectly. It's also chainable, as it's returning SelectManager. – Slotos May 29 '15 at 11:24
arel 6.0.3 and this saved my day, though i used `distinct_on` – Ondřej Želazko Aug 10 '17 at 15:08

rafb3 · Answer 2 · 2014-05-29T21:39:43.197

15

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

edited May 29 '14 at 21:39

answered May 29 '14 at 21:33

rafb3

1,694
12
12

1

This doesn't strictly answer the question but it does happen to answer _my_ question: how do you express `count(DISTINCT attr)` using Arel. – spume May 12 '20 at 12:34

score 7 · Answer 3 · edited Jun 09 '14 at 18:35

7

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.

edited Jun 09 '14 at 18:35

Малъ Скрылевъ

16,187
5
56
69

answered Aug 19 '10 at 18:47

numbers1311407

33,686
9
90
92

1

This is true. This answer was for Arel 1.x and will no longer work. – numbers1311407 Nov 09 '11 at 03:44
1

Do you know what's the alternative? – Pedro Rolo Nov 09 '11 at 10:53

score 3 · Answer 4 · answered Feb 01 '11 at 00:04

3

If you are doing this using a scope:

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

answered Feb 01 '11 at 00:04

Midwire

1,090
8
25

Bryan Ash · Accepted Answer · 2014-11-22T20:39:54.357

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.

edited Nov 22 '14 at 20:39

answered Jun 29 '10 at 00:28

Bryan Ash

4,385
3
41
57

Not exactly algebraic, but hard to argue with it's efficiency ;-) – jemmons Jul 06 '10 at 22:32
2

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. – Adam Lassek Apr 25 '11 at 21:55
7

This is not AREL, and thus it doesn't answer the question. – Pedro Rolo Sep 30 '11 at 15:19

score 0 · Answer 6 · edited Oct 29 '12 at 19:06

0

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

edited Oct 29 '12 at 19:06

Nathaniel Ford

20,545
20
91
102

answered Mar 25 '12 at 14:05

Alfat Saputra Harun

1,061
2
10
18

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. – maerics Sep 19 '12 at 04:50

How to fetch distinct values with arel/relational algebra

6 Answers6