As follow-up of What has happened to Arel, discussion happening at Where with block by brunvez · Pull Request #39445 · rails/rails · GitHub and overall movement of extending ActiveRecord API to be able to build more advanced queries I think it would be great to start collecting queries impossible or hard to build with current plain ActiveRecord API (without using sql fragments or arel directly).
Feel free to share your queries!
Few items from my personal list:
WITH support (I’m using GitHub - kmurph73/ctes_in_my_pg: ActiveRecord support for PostgreSQL's CTEs for now)
API for advanced SELECT statements (calculations, function usage, operators), I’m attaching example where SQL fragment is needed
def self.only_with_royalty where('(revenues.royalty * COALESCE(exchange_rates.rate, 0) != 0)') end
- SELECT statement aliases
def self.with_currency select('accounts.currency AS account_currency') end