Share queries hard to build with plain ActiveRecord

rubyonrails-talk
1

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:

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
2
  • Custom join table aliases are a bit clunky

Not sure whether this is something AR should be expected to be able to handle though.

Model.
  select("subquery.column").
  left_join("(#{SomeCustomQuery}) AS subquery ON subquery.key = model.id")
3

Here are a couple I have they’re quite nasty since they’re on top of an EAV table (note: there are some variable that I have left out of scope):

attr     = EntryRecord.arel_table[field.value_attribute]
entry_id = EntryRecord.arel_table[:entry_id]
field_id = EntryRecord.arel_table[:field_id]

EntryRecord.where(attr.matches("%#{value}%").and(entry_id.eq_any(entries.ids).and(field_id.eq(field.id)))).pluck(:entry_id)

Here’s another, I would love go get rid of the "...#{field.id}..."

entries.joins("left join entry_records on entry_records.entry_id = entries.id and entry_records.field_id = #{field.id}")
       .order(field.value_attribute => direction)

Thanks