Skip to content

Arel: Add support for FILTER clause (SQL:2003)#40491

Merged
rafaelfranca merged 1 commit intorails:mainfrom
Envek:arel/filter-support
Oct 14, 2021
Merged

Arel: Add support for FILTER clause (SQL:2003)#40491
rafaelfranca merged 1 commit intorails:mainfrom
Envek:arel/filter-support

Conversation

@Envek
Copy link
Contributor

@Envek Envek commented Oct 30, 2020 •
edited
Loading

Reopened from rails/arel#518 due to the numerous requests of users (also in rails/arel#460).

Allows to write following Ruby code:

Model.all.pluck(
  Arel.star.count.as('records_total').to_sql,
  Arel.star.count.filter(Model.arel_table[:some_column].not_eq(nil)).as('records_filtered').to_sql,
)

to get following SQL:

SELECT
  COUNT(*) AS records_total  
  COUNT(*) FILTER (WHERE some_column IS NOT NULL) AS records_filtered
FROM models

Database support:

  1. PostgreSQL 9.4+ (december 2014, release notes)
  2. SQLite 3.30+ (october 2019, release notes)

See:

@Envek Envek mentioned this pull request Oct 30, 2020
Closed
Base automatically changed from master to main January 14, 2021 17:02
@rails-bot
Copy link

rails-bot bot commented Apr 14, 2021

This pull request has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
Thank you for your contributions.

@rails-bot rails-bot bot added the stale label Apr 14, 2021
@Envek
Copy link
Contributor Author

Envek commented Apr 14, 2021

Oh bot, just look at these reactions under first comment! How you even could think about closing this pull request? Heartless piece of code!

@rails-bot rails-bot bot removed the stale label Apr 14, 2021
sunny
sunny reviewed Jul 11, 2021
View reviewed changes
activerecord/test/cases/arel/nodes/filter_test.rb Outdated
Comment on lines 11 to 13

This comment was marked as resolved.

Sign in to view

activerecord/test/cases/arel/nodes/filter_test.rb Outdated
Comment on lines 19 to 21

This comment was marked as resolved.

Sign in to view

activerecord/test/cases/arel/nodes/filter_test.rb Outdated
Comment on lines 29 to 31

This comment was marked as resolved.

Sign in to view

@Envek
Arel: Add support for FILTER clause (SQL:2003)
a738295 
Currently supported by PostgreSQL 9.4+ and SQLite 3.30+

See:

 - http://modern-sql.com/feature/filter
 - https://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES
 - https://sqlite.org/lang_aggfunc.html#aggfilter

Example:

    Model.all.pluck(
      Arel.star.count.as('records_total').to_sql,
      Arel.star.count.filter(Model.arel_table[:some_column].not_eq(nil)).as('records_filtered').to_sql,
    )
@Envek
Copy link
Contributor Author

Envek commented Jul 20, 2021

@sunny thanks for catching this weird naming of test examples (most probably I copied them from some project, but it was long time ago).

Applied your suggestions and rebased on top of fresh main branch.

simi
simi approved these changes Aug 29, 2021
View reviewed changes
Copy link
Contributor

@simi simi left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I really like this extension of Arel. As a followup we can open another case to expose it via ActiveRecord public API. Also it is not supported by MySQL, but it can be implemented using CASE (https://modern-sql.com/feature/filter#conforming-alternatives). It could be added to MySQL adapter later as well if needed.

@benedikt
Copy link

benedikt commented Oct 13, 2021

I'd love to see this merged!

@rafaelfranca rafaelfranca self-assigned this Oct 14, 2021
@rafaelfranca rafaelfranca merged commit 21c6c22 into rails:main Oct 14, 2021
@Envek Envek deleted the arel/filter-support branch October 15, 2021 10:54
@jrochkind jrochkind mentioned this pull request Oct 18, 2021
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

2 more reviewers

@sunny sunny sunny left review comments

@simi simi simi approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

@rafaelfranca rafaelfranca

Labels

activerecord

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

5 participants

@Envek @benedikt @sunny @simi @rafaelfranca