result contains only 1 row with withGraphJoined (but correct with withGraphFetched)

[salisbury-espinosa]

Let's take 2 models for standard postgres tables as an example

1. information_schema.table_constraints
2. information_schema.key_column_usage

Objections models:

class TableConstraints extends Model {
  keyColumnUsage: KeyColumnUsage[] | null | undefined;

  static get tableName() {
    return 'information_schema.table_constraints';
  }

  static get relationMappings() {
    return {
      keyColumnUsage: {
        relation: Model.HasManyRelation,
        modelClass: KeyColumnUsage,
        join: {
          from: 'information_schema.key_column_usage.constraint_name',
          to: 'information_schema.table_constraints.constraint_name',
        },
        // filter: (query: QueryBuilder<TableConstraints>) =>
        //   query.whereRaw(
        //     `information_schema.table_constraints.table_schema = information_schema.key_column_usage.table_schema
        //     AND
        //     information_schema.table_constraints.table_name = information_schema.key_column_usage.table_name`,
        //   ),
      },
    };
  }
}

class KeyColumnUsage extends Model {
  static get tableName() {
    return 'information_schema.key_column_usage';
  }
}

Usage:

const q = TableConstraints.query()
    .withGraphJoined('keyColumnUsage', { joinOperation: 'join' });

const rowset = await q;
  
console.log('===>', rowset.length); // ===> 1

as a result there is always exactly 1 line from the request!!!

But SQL code that is compiled with Objection.js is valid:

select "information_schema"."table_constraints"."constraint_catalog" as "constraint_catalog", "information_schema"."table_constraints"."constraint_schema" as "constraint_schema", "information_schema"."table_constraints"."constraint_name" as "constraint_name", "information_schema"."table_constraints"."table_catalog" as "table_catalog", "information_schema"."table_constraints"."table_schema" as "table_schema", "information_schema"."table_constraints"."table_name" as "table_name", "information_schema"."table_constraints"."constraint_type" as "constraint_type", "information_schema"."table_constraints"."is_deferrable" as "is_deferrable", "information_schema"."table_constraints"."initially_deferred" as "initially_deferred", "information_schema"."table_constraints"."enforced" as "enforced", "information_schema"."table_constraints"."nulls_distinct" as "nulls_distinct", "key_column_usage"."constraint_catalog" as "keyColumnUsage:constraint_catalog", "key_column_usage"."constraint_schema" as "keyColumnUsage:constraint_schema", "key_column_usage"."constraint_name" as "keyColumnUsage:constraint_name", "key_column_usage"."table_catalog" as "keyColumnUsage:table_catalog", "key_column_usage"."table_schema" as "keyColumnUsage:table_schema", "key_column_usage"."table_name" as "keyColumnUsage:table_name", "key_column_usage"."column_name" as "keyColumnUsage:column_name", "key_column_usage"."ordinal_position" as "keyColumnUsage:ordinal_position", "key_column_usage"."position_in_unique_constraint" as "keyColumnUsage:position_in_unique_constraint" from "information_schema"."table_constraints" inner join "information_schema"."key_column_usage" as "key_column_usage" on "key_column_usage"."constraint_name" = "information_schema"."table_constraints"."constraint_name"

and it gives a lot of results in the console!
but if you replace withGraphJoined with withGraphFetched, everything becomes correct and the query returns many records