1

Arel: Add support for FILTER clause (SQL:2003) by Envek · Pull Request #40491 ·...

 2 years ago
source link: https://github.com/rails/rails/pull/40491
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

New issue

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

Merged

rafaelfranca merged 1 commit into

rails:main

from

Envek:arel/filter-support

2 days ago

Merged

Conversation

Copy link

Contributor

Envek commented on Oct 30, 2020 •

edited

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)

Copy link

rails-bot bot commented on Apr 14

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.

Copy link

Contributor

Author

Envek commented on Apr 14

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

_(table[:id].count.filter(table[:gdp_per_capita].gteq(40_000)).to_sql).must_be_like %{

COUNT("users"."id") FILTER (WHERE "users"."gdp_per_capita" >= 40000)

}

This comment has been hidden.

_(table[:id].count.filter(table[:gdp_per_capita].gteq(40_000)).as("foo").to_sql).must_be_like %{

COUNT("users"."id") FILTER (WHERE "users"."gdp_per_capita" >= 40000) AS foo

}

This comment has been hidden.

_(table[:id].count.filter(table[:gdp_per_capita].gteq(40_000)).over(window).to_sql).must_be_like %{

COUNT("users"."id") FILTER (WHERE "users"."gdp_per_capita" >= 40000) OVER (PARTITION BY "users"."year")

}

This comment has been hidden.

Copy link

Contributor

Author

Envek commented on Jul 20

@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.

Copy link

Contributor

@simi simi left a comment

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.

Copy link

Contributor

benedikt commented 4 days ago

I'd love to see this merged!

rafaelfranca

merged commit 21c6c22 into

rails:main 2 days ago

3 of 4 checks passed

Envek

deleted the arel/filter-support branch

2 days ago

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

Projects

None yet

Milestone

No milestone

Linked issues

Successfully merging this pull request may close these issues.

None yet

5 participants

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK