feathersjs-ecosystem / feathers-objection

Feathers database adapter for Objection.js, an ORM based on KnexJS SQL query builder for Postgres, Redshift, MSSQL, MySQL, MariaDB, SQLite3, and Oracle. Forked from feathers-knex.
MIT License
98 stars 48 forks source link

$joinRelation adds select * to the query if groupBy is not in the query #177

Open wz5899 opened 2 years ago

wz5899 commented 2 years ago

I am seeing a strange behavior in feathers-objection. If context.params.query has operator "$joinRelation" but no operator "groupBy", feathers-objection will add (*) in "select" which makes operator $select irrelevant. Is there a reason for this implementation or is this a bug? The version of the feathers-objection I use is 7.5.1.

For example: I have a model "users" that has a ManyToManyRelation roles with model "Roles"

In a find query, context.params.query: { "$select": ["email"] } This will only show the "email" and "id" fields in the model and it works correctly.

If adding $joinRelation, context.params.query: { "$select": ["email"], "$joinRelation": {"roles": true } } This will show ALL the fields in the model despite the $select: ["email"]. The SQL query generated has "select users.email, users.id, users.* from...".

I looked into feathers-objection code and found the users.* is added in groupByColumns check:

lib\index.js:
createQuery(params = {}) {
...
    const joinRelation = query && query.$joinRelation;
...
    if (joinRelation) {
      const groupByColumns = this.getGroupByColumns(q);

      if (!groupByColumns) {
        q.distinct(`${this.Model.tableName}.*`);
      }
    }
}

One workaround to show only the fields in $select when using joinRelation is adding a modifier builder.groupBy('users.id'); into the query. This works but feels awkward. Are there any cleaner ways to get around this issue?

arakir commented 1 year ago

I have similar problem with this. When using $sort with $joinRelation DB throw error not having ORDER BY column in SELECT. Can you at least add a way how to opt out of adding this distinct?