associations and custom schema generates wrong query

[sl-ffx]

Hi,

The bookshelf-jsonapi-params generates a query which failes on postgres. Maybe it's a special case, because we are using the postgres schema.
The problem is on these lines. The joinTableName also contains the schema. Because the alias ("as") does not distinguish the schema it interprets the name as "roles_custom.roles_users". But the third argument distinguish the schema and interprets the name as "roles_custom"."roles_users"."role_id".
That's why postgres throws the error missing FROM-clause entry for table "roles_users".

My question: Is the alias in the join statement needed?
If I remove the aliases the code works as expected.

Here is an example to reproduce

const knex = require('knex')
const Bookshelf = require('bookshelf')
const bookshelfJsonApiParams = require('bookshelf-jsonapi-params')

const db = knex({
  client: 'pg',
  connection: {
    database: 'test',
    host: 'localhost',
    password: 'test',
    port: 5432,
    user: 'test'
  }
})

const bookshelf = new Bookshelf(db)

bookshelf.plugin(bookshelfJsonApiParams, {
  pagination: {
    limit: 25
  }
})

class Roles extends bookshelf.Model {
  get tableName() {
    return 'custom.roles'
  }

  users() {
    return this.belongsToMany('User', 'custom.roles_users', 'user_id', 'role_id')
  }
}

class Users extends bookshelf.Model {
  get tableName() {
    return 'custom.users'
  }

  roles() {
    return this.belongsToMany('Role', 'custom.roles_users', 'role_id', 'user_id')
  }
}

bookshelf.model('Role', Roles)
const User = bookshelf.model('User', Users)

new User()
  .fetchJsonApi({
    filter: {
      'roles.id': '1'
    },
    include: ['roles']
  })
  .then(console.log)
  .catch(console.error)

Here is the full error

error: select "custom"."users".* from "custom"."users" left outer join "custom"."roles_users" as "roles_custom.roles_users" on "custom"."users"."id" = "roles_custom"."roles_users"."role_id" left outer join "custom"."roles" as "roles" on "roles_custom"."roles_users"."user_id" = "roles"."id" where "roles"."id" in ($1) limit $2 - missing FROM-clause entry for table "roles_users"
    at Parser.parseErrorMessage (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/operator/Projects/redbull/canvases-api/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:315:20)
    at addChunk (internal/streams/readable.js:309:12)
    at readableAddChunk (internal/streams/readable.js:284:9)
    at Socket.Readable.push (internal/streams/readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 123,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '145',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3301',
  routine: 'errorMissingRTE'
}

[alechirsch]

The alias was necessary to differentiate between different relationships. If you have multiple relationships that use the same mapping table, this would cause incorrect results. This is an edge case where you are using schema. Is there a particular reason why you are specifying the schema? You can set the schema that you want to use in your knexfile (searchPath) if you are using something other than public.

[sl-ffx]

Ok. That sounds logical.

I know this sounds very ugly, but we have a project with multiple seperated applications in it and they all use the same database.
We use the schema so every applications have their own tables without thinking about unique table names.
To use the searchPath in the knexfile I would have to create a knex instance for every application which I want to prevent because of the migrations and seeds.

[alechirsch]

We could make a change to replace . with _ for the alias? That may solve for your issue. Feel free to submit a PR for that.

[alechirsch]

@sl-ffx Do you want to make this change or should I do it?

[sl-ffx]

Can you make this change?
I'm not able to run the tests. It always stuck in the before hook in the common.js and I don't want to push anything without testing it.

[alechirsch]

To run the tests you need to create a postgres database. The details for the connection are in the test file. You can also make a PR and I can run the tests.

…

On Wed, Jul 14, 2021, 11:52 PM Stefan Laimer ***@***.***> wrote: Can you make this change? I'm not able to run the tests. It always stuck in the before hook in the common.js and I don't want to push anything without testing it. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#75 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABUMB55CRD3WWLBGC4H5KXTTXZZR3ANCNFSM5ALC3AYQ> .

[sl-ffx]

Yea.. I used an existing postgres database and created the role + database. I don't even get any outputs on setting debug: true in the knex config.

Ok. If you can run the tests for me I'm going to create a PR.

[sl-ffx]

Created PR #76