Default datasets, joins and aliases

Issues
1

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:

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:

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:

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:

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.

2

Generally, you shouldn’t use symbols but schemas and attributes.

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.

3

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:

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" ...

users.join(tasks).select_append(tasks[:bar]).order(tasks[:bar]).first
# => KeyError: :bar attribute doesn't exist in tasks schema

users.join(tasks).select_append(tasks[:foo].as(:bar)).order(tasks[:bar]).first
# => NoMethodError: undefined method `as' for #<Sequel::SQL::AliasedExpression:0x00005594bec7a5d0>

Sounds like a bug?

4

@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

5

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 :smile:

6

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

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
# => #<Sequel::DatabaseError: SQLite3::SQLException: near "AS": syntax error>
end

puts '----------------------------------------------------------------'

puts "===> users.order(users[:username].as(:name))"
begin
  order = users.order(users[:username].as(:name))
rescue => e
  puts e.inspect
# => #<NoMethodError: undefined method `as' for #<Sequel::SQL::AliasedExpression:0x0000563a074f19c8>>
end
7

Right, both seem as bugs to me, though I’m not sure how easy to fix the first one