Ho do I reference a subquery using an alias in an AREL query?

Question

I am using RAILS 4 and MySQL A service has many places through service places.

I am trying to create an AREL equivalent of the SQL query below:

SELECT DISTINCT
    services . *
FROM
    services
        INNER JOIN
    (SELECT DISTINCT
        `services` . *
    FROM
        `services`
    LEFT OUTER JOIN `service_places` ON `service_places`.`service_id` = `services`.`id`
    WHERE
        `service_places`.`place_id` IN (SELECT DISTINCT
                `places`.`id`
            FROM
                `places`
            WHERE
                (`places`.`place_name` LIKE '%war%'))) s1 ON s1.id = services.id
        INNER JOIN
    (SELECT DISTINCT
        `services` . *
    FROM
        `services`
    LEFT OUTER JOIN `service_places` ON `service_places`.`service_id` = `services`.`id`
    WHERE
        `service_places`.`place_id` IN (SELECT DISTINCT
                `places`.`id`
            FROM
                `places`
            WHERE
                (`places`.`place_name` LIKE '%leam%'))) s2 ON s2.id = services.id;

Ideally this would be done by an insersect query but having done some reading I have found that although AREL offers an INTERSECT, MySql does not support it. So I created the SQL using joins which returns the data that I expect it to.

I have got some AREL code to create the two subqueries and that works fine:

s  = Service.arel_table
sp = ServicePlace.arel_table
p  = Place.arel_table

search_from = "leam"
search_to = "war"

############
# From QUERY
############
from_subquery = Place.select(p[:id]).where(p[:place_name].matches("%#{search_from}%")).uniq

from_query = Service.joins(
  s.join(sp, Arel::Nodes::OuterJoin).on(sp[:service_id].eq(s[:id]))
 .join_sql
).uniq

from_query = from_query.where(sp[:place_id].in(from_subquery.ast))

##########
# To Query
##########
to_subquery = Place.select(p[:id]).where(p[:place_name].matches("%#{search_to}%")).uniq

to_query = Service.joins(
  s.join(sp, Arel::Nodes::OuterJoin).on(sp[:service_id].eq(s[:id]))
 .join_sql
).uniq

to_query = to_query.where(sp[:place_id].in(to_subquery.ast))

My problem comes in trying to create the AREL query that incorporates these two subqueries. I tried this but it fails:

query = Service.joins(from_subquery.as('s1')).on(s1[:id].eq(s[:id]))
               .join(to_subquery.as('s2')).on(s2[:id].eq(s[:place_id]))
               .join_sql
               ).uniq

NameError: undefined local variable or method `s1' for main:Object

I am happy that the way I built this is OK because I have a similar syntax query running and that works no problem (see below):

query = Service.joins(
          s.join(sp, Arel::Nodes::OuterJoin).on(sp[:service_id].eq(s[:id]))
           .join(p, Arel::Nodes::OuterJoin).on(p[:id].eq(sp[:place_id]))
           .join_sql
          ).uniq

I know the problem is how I am referencing the sub queries in the relations but don't know what this is. I have seen reference to "create_table_alias", "alias" and "as" methods but can't find any examples that help me. It's probably something blindingly obvious as always I just can't see it.

If anyone can help I would appreciate it.

Thanks in advance.

UPDATE 01:

I found an AREL syntax creater http://www.scuttle.io/ that can be useful for generating AREL syntax. It didn't work for me in the entirety but for the smaller blocks it would. You will need to change the "likes" to "=" though because it doesn't include them otherwise. You can change the "eq" back to "matches" afterwards though.

UPDATE 02:

Tried using CTE with this code which created an Arel::SelectManager object. However, further research indicates you can't use CTE's in MYSQL. For reference the code I used is shown below.

cte_table = Arel::Table.new(:cte_table)
composed_cte = Arel::Nodes::As.new(cte_table, from_query)
s.join(cte_table).on(s[:id].eq(cte_table[:id])).project(s[Arel.star]).with(composed_cte)

Answer 1

as in Arel is mostly used to specify names for specific fields in SELECT-clause (roughly speaking, presented in Arel as project), table aliases are typically handled in a different way.

The problem is that in your working example you've built your subquery using a table object that doesn't involve an alias. But to alias that table in SQL, instead of the table object you need an aliased table object generated from the table object. This may sound more complicated that it is.

Given an aliased table you just use it almost everywhere instead of the table itself.

p  = Place.arel_table # Actual table
pa = p.alias          # Table alias

# Use an actual table object just as an entry point to Arel, not sure if necessary
from_subquery = p.
  # Now use an alias (the name `pa` will likely be different, as it is chosen by Arel,
  # but should be the same for the same alias object):
  # SELECT pa.id   FROM pa
  project(pa[:id]).from(pa).
  where(
    pa[:place_name].matches("%#{search_from}%") # Use alias for condition
  ).uniq

Once that is done, you can reference that alias inside that subquery with the same object, pa.

It's a basic example, loosely based on arel README, but it shows what you need to get rid of the error: that way you'll get the aliased table object into your scope and be able to use it freely.

Answer 2

This is an alternative approach. It has the disadvantage of using two sql queries instead of one, but I would suggest it is much more maintainable code:

places = Place.arel_table
leam = Service.joins(:places).where(places[:place_name].matches('%leam%')
wark = Service.joins(:places).where(places[:place_name].matches('%wark%')
leam.where(id: wark.pluck(:id).uniq)