Mark Mark - 6 months ago 21
MySQL Question

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

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

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)