Aliased column issue

[atrost]

I am experimenting with subqueries using AREL and the arel-helpers gem.

Started off with a simple use case where I need to reference an aliased column in a WHERE clause that is calculated in a SELECT clause. As explained in this SO post https://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause
one way to work around this is to use a subquery. So I have a similar SQL which I parse into an AREL equivalent:

SELECT net_total FROM ( SELECT ( booked - booked * agency_commission) AS net_total FROM campaigns) AS t WHERE net_total > '400';

scuttle translated to:

t = Arel::Table.new('t') Campaign.select(:net_total).from( Campaign.select( Arel::Nodes::Group.new(:booked - :booked * :agency_commission).as('net_total') ).as('t') ).where(t[:net_total].gt('400'))

When I run this code in my controller it throws this error:

undefined method `as' for #Arel::Nodes::Group:0x00007fcf6c689078

It doesn't find the as method on Arel::Nodes::Group.

Any idea what might be wrong?

The app uses Rails 5.1 and activerecord 5.1.4.
Thanks

[camertron]

Hey @atrost,

Yeah, it looks like Arel::Nodes::Group doesn't support aliasing. I looked through Rails master and it appears they're planning to add it either in Rails 5.3 or Rails 6, not sure which is coming first. See below for a workaround.

I also noticed within the Group you have a couple of bare subtracted symbols. Scuttle did this because you didn't give it fully-qualified table_name.column_names, and it isn't (yet) smart enough to know you're referring to the table that comes after the "FROM" part.

Here's a translated query that should work:

t = Arel::Table.new('t')
campaign = Campaign.arel_table

Campaign.select(:net_total).from(
  Campaign.select(
    Arel::Nodes::As.new(
      Arel::Nodes::Group.new(
        Campaign[:booked] - Campaign[:booked] * Campaign[:agency_commission]
      ),
      Nodes::SqlLiteral.new('net_total')
    )
  ).as('t')
).where(t[:net_total].gt('400'))

Hope that helps :)

[atrost]

Hi @camertron,
Thanks for looking into it. Yes, this one worked. Right regrading the bare subtracted symbols. I am just starting with this so I used a contrived example just to get a handle on basic AREL stuff and to get something working. My real-life query has joins inside nested selects and things of that nature. I will continue working on this and will post the actual query that I am trying to translate. Thanks for the great tool!