Menu
I have a few massive SQL request involving join across various models in my rails application. A single request can involve 6 to 10 tables.
To run the request faster I want to use sub-queries in the joins (that way I can filter these tables before the join and reduce the columns to the ones I need). I'm trying to achieve this using ARel.
I thought I found the solution to my problem there: How to do joins on subqueries in AREL within Rails,
but things must have changed because I get undefined method '[]' for Arel::SelectManager.
Does anybody have any idea how to achieve this (without using strings) ?
437
JOIN a table with a subquery A subquery can be used with JOIN operation. In the example below, the subquery actually returns a temporary table which is handled by database server in memory. The temporary table from the subquery is given an alias so that we can refer to it in the outer select statement.
The syntax of the subquery when it is used with the ALL operator is as follows: comparison_operator ALL (subquery) The following condition evaluates to true if x is greater than every value returned by the subquery. x > ALL (subquery) For example, suppose the subquery returns three value one, two, and three.
You can use a subquery in the FROM clause of the SELECT statement as follows: SELECT * FROM (subquery) AS table_name Code language: SQL (Structured Query Language) (sql) In this syntax, the table alias is mandatory because all tables in the FROM clause must have a name.
The Arel::Table object acts like a hash which contains each column on the table. The columns given by Arel are a type of Node, which means it has several methods available on it to construct queries.
Pierre, I thought a better solution could be the following (inspiration from this gist):
a = A.arel_table
b = B.arel_table
subquery = b.project(b[:a_id].as('A_id')).where{c > 4}
subquery = subquery.as('intm_table')
query = A.join(subquery).on(subquery[:A_id].eq(a[:id]))
No particular reason for naming the alias as "intm_table", I just thought it would be less confusing.
50
OK so my main problem was that you can't join a Arel::SelectManager ... BUT you can join a table aliasing. So to generate the request in my comment above:
a = A.arel_table
b = B.arel_table
subquery = B.select(:a_id).where{c > 4}
query = A.join(subquery.as('B')).on(b[:a_id].eq(a[:id])
query.to_sql # SELECT A.* INNER JOIN (SELECT B.a_id FROM B WHERE B.c > 4) B ON A.id = B.a_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With