New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Using Arel::Nodes::DistinctOn in select with PostgreSQL generates syntax error #302

Closed
mxhold opened this Issue Aug 15, 2014 · 1 comment

Comments

Projects
None yet
1 participant
@mxhold

mxhold commented Aug 15, 2014

The following select:

Country.select(Arel::Nodes::DistinctOn.new(Country.arel_table[:id])).select(Arel.star).to_sql
 => "SELECT DISTINCT ON ( \"countries\".\"id\" ), * FROM \"countries\""

results in a syntax error when run:

PG::Error: ERROR:  syntax error at or near ","
LINE 1: SELECT DISTINCT ON ( "countries"."id" ), * FROM "countries"
                                               ^

It works without that comma:

Country.select("DISTINCT ON ( \"countries\".\"id\" ) *")
  Country Load (4.0ms)  SELECT DISTINCT ON ( "countries"."id" ) * FROM "countries"
 => #<ActiveRecord::Relation [#<Country id: 1, name: "Andorra", iso_abbreviation: "AD">, ... ]>

This happens using Arel 5.0.1.20140414130214 and Postgres 9.2.4.3 (14).

Sorry if this is just because I'm not using it right. I haven't found any documentation for using Arel::Nodes::DistinctOn but thought it would be nice to use Arel to do the DISTINCT ON instead of using a string.

@mxhold mxhold changed the title from Using Arel::Nodes::DistinctOn with PostgreSQL generates syntax error to Using Arel::Nodes::DistinctOn in select with PostgreSQL generates syntax error Aug 15, 2014

@mxhold

This comment has been minimized.

mxhold commented Aug 30, 2014

After a little investigation, I confirmed that this is just because I was using it wrong.

By including it in the select, it was getting added to the projections just like a normal column select which is why it was getting joined with the comma. I didn't notice it then but that also meant it wouldn't get put in the first position like a normal DISTINCT.

As far as I can tell, there is no way to use Arel::Nodes::DistinctOn by building a query from a Table. Instead you have to manually build up the select statement and set the quantifier like this:

select_statement = Arel::Nodes::SelectStatement.new
select_statement.cores.first.set_quantifier = Arel::Nodes::DistinctOn.new(Arel::Table.new(:countries)[:id])
select_statement.cores.first.projections << Arel.star
select_statement.to_sql
# => "SELECT DISTINCT ON ( \"countries\".\"id\" ) *"

This is not the most convenient but it works.

I think it would be nice to be able to set the DISTINCT ON in a similar way to setting a normal DISTINCT. Something like:

table = Arel::Table.new(:users)
table.project(Arel.star).distinct_on(table[:id]).to_sql
# => "SELECT DISTINCT ON ( \"users\".\"id\" ) * FROM \"users\""

I will add a pull request that implements this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment