# Default datasets, joins and aliases
**URL:** https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299
**Category:** Issues
**Created:** [January 10, 2019, 4:37pm UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299 "2019-01-10T16:37:31Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@waiting-for-dev](https://discourse.rom-rb.org/u/waiting-for-dev)
#### Post date: [January 10, 2019, 4:37pm UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299/1 "2019-01-10T16:37:32Z")
I’m playing with rom SQL joins for first time in order to use them later in a real application.
As example, I’m wondering how one should proceed to order by a joined table field.
If I try:
```ruby
users.join(:tasks).order(:task_field)
```
It doesn’t work. This is because `users` relation default dataset specifies the list of `users` columns in the `SELECT` clause instead of using the wildcard `*` , while `join` doesn’t modify it:
```auto
SELECT `users`.`id` AS `id`, `users.name` AS `name` ..... INNER JOIN `tasks`...
```
So, this has been my first surprise… If I’m not missing something, in order to have access to joined columns you have to manually change the `SELECT` clause. Something like the following will work:
```ruby
users.join(:tasks).select.order(:task_field)
```
Without arguments, `select` will change the default dataset `SELECT` clause for a mere `*`. It will load all `tasks` columns, without qualifying, and then it will be possible to order by any of them:
However, as the qualified default dataset `SELECT` is lost in favour of a single `*`, all my configured aliases are falling by the wayside.
I have tried to use `select_append` instead of `select`, to do something like:
```ruby
users.join(:tasks).select_append(:task_field).order(:task_field)
```
But it doesn’t work as it is looking for `:task_field` attribute in `users` relation.
I’m also wondering how `join` relates with `combine` relations method. `combine` doesn’t seem to change at all the dataset generated sql, so I guess it plays at a different level, surely performing n + 1 queries.
In short, I guess I have several related questions:
- Is having to use `join(:table).select` the recommended way to perform joins while still being able to access joined table columns?
- If so, how can we do to keep aliases?
- Does `combine` play in another league performing n + 1 queries instead of joins?
Thanks for any feedback.
---
### Author:  [@flash-gordon](https://discourse.rom-rb.org/u/flash-gordon)
#### Post date: [January 10, 2019, 5:33pm UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299/2 "2019-01-10T17:33:41Z")
Generally, you shouldn’t use symbols but schemas and attributes.
```auto
users.join(tasks).select_append(tasks[:foo]).order(tasks[:bar])
```
^this is _the_ recommended way of using rom-sql. This way you can have aliases with `tasks[:foo].as(:baz)`.
`combine` would execute two queries in your case, it never falls back to n + 1. Practically speaking, `combine` is often faster than using joins.
---
### Author:  [@waiting-for-dev](https://discourse.rom-rb.org/u/waiting-for-dev)
#### Post date: [January 10, 2019, 5:54pm UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299/3 "2019-01-10T17:54:59Z")
Thanks @flash-gordon for your help.
Ok, it makes much more sense using schemas. Thanks also for the information for `combine` method.
However, it doesn’t seem to play well when canonical `:foo` is aliased to `:bar` in `tasks` relation:
```ruby
users.join(tasks).select_append(tasks[:foo]).order(tasks[:foo]).first
# => Sequel::DatabaseError: PG::SyntaxError: ERROR: syntax error at or near "AS"
# => LINE 1: ... ORDER BY "tasks"."foo" AS "bar" ...
```
```ruby
users.join(tasks).select_append(tasks[:bar]).order(tasks[:bar]).first
# => KeyError: :bar attribute doesn't exist in tasks schema
```
```ruby
users.join(tasks).select_append(tasks[:foo].as(:bar)).order(tasks[:bar]).first
# => NoMethodError: undefined method `as' for #
```
Sounds like a bug?
---
### Author:  [@flash-gordon](https://discourse.rom-rb.org/u/flash-gordon)
#### Post date: [January 10, 2019, 6:18pm UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299/4 "2019-01-10T18:18:37Z")
@waiting-for-dev possibly, I’m not sure. Do these columns actually exist? And why `tasks[:foo]` is a `Sequel::SQL::AliasedExpression`? I need to see a reproducing snippet before claiming it a bug
---
### Author:  [@waiting-for-dev](https://discourse.rom-rb.org/u/waiting-for-dev)
#### Post date: [January 10, 2019, 6:21pm UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299/5 "2019-01-10T18:21:51Z")
Yes, they exist. I just translated them to `:foo` and `:bar` from my working example. I’ll dig into it a bit more and prepare a snippet to reproduce it easily.
Thanks for your help 😄
---
### Author:  [@waiting-for-dev](https://discourse.rom-rb.org/u/waiting-for-dev)
#### Post date: [January 11, 2019, 8:17am UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299/6 "2019-01-11T08:17:36Z")
Ok, I reproduced it.
About the second scenario (`tasks[:bar]` returning `KeyError`), it is expected behaviour. `Schema#[]` is meant to work with canonical names. It would be nice to have an equivalent method also accepting aliased names, in order to decouple from the database layer. I’ll try a PR with it, but it’s not a bug.
However, following snippet reproduces the other two scenarios. It seems two separated bugs. If you think they are, I can open one or two bug reports (as you like) in `rom` or `rom-sql`.
The code is also here: [https://github.com/waiting-for-dev/rom\_bug\_order\_by\_aliased](https://github.com/waiting-for-dev/rom_bug_order_by_aliased)
```ruby
require "rom"
require "rom-sql"
require "dry/types"
module Types
include Dry::Types.module
end
rom = ROM.container(:sql, 'sqlite::memory') do |conf|
conf.default.create_table(:users) do
primary_key :id
column :username, String, null: false
end
conf.relation(:users) do
schema(infer: true) do
attribute :username, Types::String.meta(alias: :name)
end
end
end
users = rom.relations[:users]
puts "===> users.order(users[:username])"
order = users.order(users[:username])
puts order.dataset.sql
# => SELECT `users`.`username` AS 'name', `users`.`id` FROM `users` ORDER BY `users`.`username` AS 'name'
begin
order.to_a
rescue => e
puts e.inspect
# => #
end
puts '----------------------------------------------------------------'
puts "===> users.order(users[:username].as(:name))"
begin
order = users.order(users[:username].as(:name))
rescue => e
puts e.inspect
# => #>
end
```
---
### Author:  [@flash-gordon](https://discourse.rom-rb.org/u/flash-gordon)
#### Post date: [January 11, 2019, 10:55am UTC](https://discourse.rom-rb.org/t/default-datasets-joins-and-aliases/299/7 "2019-01-11T10:55:14Z")
Right, both seem as bugs to me, though I’m not sure how easy to fix the first one