CODEWITHSUNDEEP
sqlrubyjoinsubquerysequel
brahn
brahn

Reputation: 12386

Sequel -- can I alias subqueries in a join?

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.

Upvotes: 10

Views: 6654

Answers (2)

brahn
brahn

Reputation: 12386

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.

Upvotes: 11

Ronald Holshausen
Ronald Holshausen

Reputation: 41

The only way I found was to use the from method on the DB, and the :table_alias on the join method, but these don't work with models so I had to use the table_name from the model class. I.e.,

1.9.3p125 :018 > @db.from(Dw::Models::Contract.table_name => 'C1')
 => #<Sequel::SQLite::Dataset: "SELECT * FROM `vDimContract` AS 'C1'">
1.9.3p125 :019 > @db.from(Dw::Models::Contract.table_name => 'C1').join(Dw::Models::Contract.table_name, {:c1__id => :c2__id}, :table_alias => 'C2')
 => #<Sequel::SQLite::Dataset: "SELECT * FROM `vDimContract` AS 'C1' INNER JOIN `vDimContract` AS 'C2' ON (`C1`.`Id` = `C2`.`Id`)">  
1.9.3p125 :020 > @db.from(Dw::Models::Contract.table_name => 'C1').join(Dw::Models::Product.table_name, {:product_id => :c1__product_id}, :table_alias => 'P1')
 => #<Sequel::SQLite::Dataset: "SELECT * FROM `vDimContract` AS 'C1' INNER JOIN `vDimProduct` AS 'P1' ON (`P1`.`ProductId` = `C1`.`ProductId`)">

The only thing I don't like about from_self is it uses a subquery:

1.9.3p125 :021 > Dw::Models::Contract.from_self(:alias => 'C1')
 => #<Sequel::SQLite::Dataset: "SELECT * FROM (SELECT * FROM `vDimContract`) AS 'C1'">

Upvotes: 4

Related Questions