Using Sequel I'd like to join two subqueries together that share some column names, and then table-qualify those columns in the select.
I understand how to do this if the two datasets are just tables. E.g. if I have a users
table and an items
table, with items belonging to users, and I want to list the items' names and their owners' names:
@db[:items].join(:users, :id => :user_id).
select{[items__name, users__name.as(user_name)]}
produces
SELECT "items"."name", "users"."name" AS "user_name"
FROM "items"
INNER JOIN "users" ON ("users"."id" = "items"."user_id")
as desired.
However, I'm unsure how to do this if I'm joining two arbitrary datasets representing subqueries (call them my_items
and my_users
)
The syntax would presumably take the form
my_items.join(my_users, :id => :user_id).
select{[ ... , ... ]}
where I would supply qualified column names to access my_users.name
and my_items.name
. What's the appropriate syntax to do this?
A partial solution is to use t1__name
for the first argument, as it seems that the dataset supplied to a join is aliased with t1
, t2
, etc. But that doesn't help me qualify the item name, which I need to supply to the second argument.
I think the most desirable solution would enable me to provide aliases for the datasets in a join, e.g. like the following (though of course this doesn't work for a number of reasons)
my_items.as(alias1).join(my_users.as(alias2), :id => :user_id).
select{[alias1__name, alias2__name ]}
Is there any way to do this?
Thanks!
Update
I think from_self
gets me part of the way there, e.g.
my_items.from_self(:alias => :alias1).join(my_users, :id => :user_id).
select{[alias1__name, t1__name]}
seems to do the right thing.
An Alias is a shorthand for a table or column name. Aliases reduce the amount of typing required to enter a query. Complex queries with aliases are generally easier to read. Aliases are useful with JOINs and aggregates: SUM, COUNT, etc.
Joins and subqueries are often used together in the same query. In many cases, you can solve a data retrieval problem by using a join, a subquery, or both.
(Aliases are also useful for subqueries. An example of this is shown in this article that fully explains subqueries.) Let's look at how we can write a query that will JOIN a table to itself.
No, you cannot do that. The alias is not bound until later in the processing. You can use "Nombre" in an ORDER BY, but not in a WHERE clause and certainly not in a JOIN clause.
OK, thanks to Ronald Holshausen's hint, got it. The key is to use .from_self
on the first dataset, and provide the :table_alias
option in the join:
my_items.from_self(:alias => :alias1).
join(my_users, {:id => :user_id}, :table_alias => :alias2).
select(:alias1__name, :alias2__name)
yields the SQL
SELECT "alias1"."name", "alias2"."name"
FROM ( <my_items dataset> ) AS "alias1"
INNER JOIN ( <my_users dataset> ) AS "alias2"
ON ("alias2"."id" = "alias1"."user_id")
Note that the join hash (the second argument of join
) needs explicit curly braces to distinguish it from the option hash that includes :table_alias
.
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