Invalid SQL queries when joining query with Array of referenced Objects

[BlobCodes]

I'm trying to query a User and its groups.
This generates an invalid SQL Query:

SELECT * FROM users INNER JOIN groups AS groups ON groups.id IN users.group_ids

..resulting in runtime errors.
This is a minimal representation of what I'm trying to do (and how you can reproduce the error):

require "sqlite3"
require "onyx/sql"
require "onyx-sql/converters/sqlite3"

class User
  include Onyx::SQL::Model

  schema users do
    pkey id : Int32, converter: SQLite3::Any(Int32)
    type groups : Array(Group), key: "group_ids", converter: SQLite3::Any(Group)
  end
end

class Group
  include Onyx::SQL::Model

  schema groups do
    pkey id : Int32, converter: SQLite3::Any(Int32)
  end
end

puts Onyx::SQL.query(
  User.select("*").join(groups: true) do |x|
  end
)

This results in the following errors:
SQLite3 Error:

Unhandled exception: no such table: users.group_ids (SQLite3::Exception)
  from lib/sqlite3/src/sqlite3/statement.cr:81:5 in 'check'
  from lib/sqlite3/src/sqlite3/statement.cr:4:5 in 'initialize'
  from lib/sqlite3/src/sqlite3/statement.cr:2:3 in 'new'
  from lib/sqlite3/src/sqlite3/connection.cr:24:5 in 'build_prepared_statement'
  from lib/db/src/db/connection.cr:7:15 in 'fetch_or_build_prepared_statement'
  from lib/db/src/db/session_methods.cr:58:9 in 'build'
  from lib/db/src/db/pool_prepared_statement.cr:37:16 in 'build_statement'
  from lib/db/src/db/pool_prepared_statement.cr:53:22 in 'initialize'
  from lib/db/src/db/pool_prepared_statement.cr:11:5 in 'new'
  from lib/db/src/db/database.cr:89:7 in 'build_prepared_statement'
  from lib/db/src/db/database.cr:7:15 in 'fetch_or_build_prepared_statement'
  from lib/db/src/db/session_methods.cr:23:9 in 'build'
  from lib/db/src/db/query_methods.cr:38:7 in 'query'
  from lib/onyx-sql/src/onyx-sql/repository/query.cr:9:11 in 'query'
  from lib/onyx-sql/src/onyx-sql/repository/query.cr:23:7 in 'query'
  from lib/onyx-sql/src/onyx-sql/repository/query.cr:34:7 in 'query'
  from lib/onyx/src/onyx/sql.cr:21:5 in 'query'
  from src/test.cr:24:1 in '__crystal_main'
  from /usr/lib/crystal/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/lib/crystal/crystal/main.cr:86:7 in 'main'
  from /usr/lib/crystal/crystal/main.cr:106:3 in 'main'
  from __libc_start_main
  from _start
  from ???

PG error:

Unhandled exception: syntax error at or near "users" (PQ::PQError)
  from lib/pg/src/pq/connection.cr:204:7 in 'handle_error'
  from lib/pg/src/pq/connection.cr:0:9 in 'handle_async_frames'
  from lib/pg/src/pq/connection.cr:163:7 in 'read'
  from lib/pg/src/pq/connection.cr:158:7 in 'read'
  from lib/pg/src/pq/connection.cr:314:31 in 'expect_frame'
  from lib/pg/src/pq/connection.cr:313:5 in 'expect_frame'
  from lib/pg/src/pg/statement.cr:18:5 in 'perform_query'
  from lib/db/src/db/statement.cr:103:14 in 'perform_query_with_rescue'
  from lib/db/src/db/statement.cr:88:7 in 'query'
  from lib/db/src/db/pool_statement.cr:39:30 in 'query'
  from lib/db/src/db/query_methods.cr:38:7 in 'query'
  from lib/onyx-sql/src/onyx-sql/repository/query.cr:9:11 in 'query'
  from lib/onyx-sql/src/onyx-sql/repository/query.cr:23:7 in 'query'
  from lib/onyx-sql/src/onyx-sql/repository/query.cr:34:7 in 'query'
  from lib/onyx/src/onyx/sql.cr:21:5 in 'query'
  from src/bin/coleus.cr:73:1 in '__crystal_main'
  from /usr/lib/crystal/crystal/main.cr:97:5 in 'main_user_code'
  from /usr/lib/crystal/crystal/main.cr:86:7 in 'main'
  from /usr/lib/crystal/crystal/main.cr:106:3 in 'main'
  from __libc_start_main
  from _start
  from ???

Here is a SQLite3 DB to test it:
test.db.zip

Just install the shards sqlite3 and onyx-sql and run DATABASE_URL="sqlite3://./test.db" crystal src/test.cr to test it yourself.

[vladfaust]

Hey, @BlobCodes,

You see, there is really no SQL standard to join array references. SQLite doesn't support arrays at all, and PG, in contrary, has more than one way to do it. I'm planning on removing the ability to join array refs in an upcoming release to make Onyx::SQL more SQL.

However, IIRC PG join should work in this case, looks like the query generated by it is flawed. I'll take a deeper look into it.

Don't forget that you can do Onyx::SQL.query(User, "SELECT * FROM users ...")) as a workaround. Onyx::SQL would never be a replacement for SQL itself. Its query builder is just a helper. It's better to rely on raw SQL in complex cases. Also remember that Query(T).build would output an SQL string which you can them modify as you want.