$joinRelation forces inner join - is there a $leftJoinRelation?

[pskfry]

hello all - first of all thank you for the work you do it is greatly appreciated.

the issue i'm having is pretty straightforward. i have a complex graph query that utilizes $joinRelation to join a few nested relations which is working great.

the only issue i'm having is that it's using an inner join under the hood and i'd very much prefer it to be using a left join. is this possible? for the record, what i'm doing cannot be accomplished with $eager alone - i need $joinRelation because i need to also filter based on the relation's value. here's what the query looks like:

Person {
  name: string;
  job: Job; // Model.BelongsToOneRelation
}

Job {
  title: string;
  skill: Skill; // Model.BelongsToOneRelation
}

Skill {
  name: string;
}

api.services("persons").find({
  query: {
    $eager: "[job.[skill]]"
    $joinRelation: "[job.[skill]]"
  }
});

So currently, if a given Person does not have a Job, or they have a Job and that Job does not have a Skill, that Person will not show up in the query results. The reason I need $joinRelation is so I can do this (which works great!):

api.services("persons").find({
  query: {
    $eager: "[job.[skill]]",
    $joinRelation: "[job.[skill]]",
    "job:skill.name": userInputtedSkill
  }
});

I tried $leftJoinRelation with no luck - I also tried eagerOptions in the service options but Typescript wouldn't accept the property. Any help is greatly appreciated!

[dekelev]

Instead of adding the $joinRelation, you can use $eager with $modifyEager or $eager with modifier. The modifiers is ObjectionJS model method that can filter the eager results by modifying the Knex query builder object before it's applied.

Check the docs for syntax examples.

On Wed, Mar 10, 2021, 7:04 AM pskfry [email protected] wrote:

hello all - first of all thank you for the work you do it is greatly appreciated.

the issue i'm having is pretty straightforward. i have a complex graph query that utilizes $joinRelation to join a few nested relations which is working great.

the only issue i'm having is that it's using an inner join under the hood and i'd very much prefer it to be using a left join. is this possible? for the record, what i'm doing cannot be accomplished with $eager alone - i need $joinEager because i need to also filter based on the relation's value. here's what the query looks like:

Person { name: string; job: Job; // Model.BelongsToOneRelation }

Job { title: string; skill: Skill; // Model.BelongsToOneRelation }

Skill { name: string; }

api.services("persons").find({ query: { $eager: "[job.[skill]]" $joinRelation: "[job.[skill]]" } });

So currently, if a given Person does not have a Job, or they have a Job and that Job does not have a skill, that Person will not show up in the query results. The reason I need $joinRelation is so I can do this:

api.services("persons").find({ query: { $eager: "[job.[skill]]", $joinRelation: "[job.[skill]]", "job:skill.name": userInputtedSkill } });

Is this doable?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/feathersjs-ecosystem/feathers-objection/issues/143, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABB5E3PKMJPXBJOA5I5DRQLTC3VTRANCNFSM4Y5FMQLA .

[jf250315]

thanks for the speedy response - i will try this tonight. i tried using $modifyEager first before $joinRelation but couldn't get it to work - but i know more about how to write these queries now so i'll try it again and let you know.

[pskfry]

well - i just spent a few hours trying to get modifiers to work the way i want them to, but it just messed with the pagination mechanism so much that i think i need to do some minor refactoring.

i suppose this is why the objection documentation warns us that graph queries are not a silver bullet!

[dekelev]

Right, pagination issue regarding modifiers is a known issue. It was discussed here in previous GitHub issues.

On Thu, Mar 11, 2021, 3:56 AM pskfry [email protected] wrote:

well - i just spent a few hours trying to get modifiers to work the way i want them to, but it just messed with the pagination mechanism so much that i think i need to do some minor refactoring.

i suppose this is why the objection documentation warns us that graph queries are not a silver bullet!

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/feathersjs-ecosystem/feathers-objection/issues/143#issuecomment-796367718, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABB5E3JVC3S4NEQPYIKYJQDTDAINBANCNFSM4Y5FMQLA .

[dekelev]

Checkout modifierFiltersResults - https://github.com/feathersjs-ecosystem/feathers-objection#params-operators

On Thu, Mar 11, 2021, 9:42 AM Dekel @.***> wrote:

Right, pagination issue regarding modifiers is a known issue. It was discussed here in previous GitHub issues.

On Thu, Mar 11, 2021, 3:56 AM pskfry @.***> wrote:

well - i just spent a few hours trying to get modifiers to work the way i want them to, but it just messed with the pagination mechanism so much that i think i need to do some minor refactoring.

i suppose this is why the objection documentation warns us that graph queries are not a silver bullet!

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/feathersjs-ecosystem/feathers-objection/issues/143#issuecomment-796367718, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABB5E3JVC3S4NEQPYIKYJQDTDAINBANCNFSM4Y5FMQLA .

[wz5899]

I added the $leftJoinRelation and submitted a pull request #182 .

[sokulski]

well - i just spent a few hours trying to get modifiers to work the way i want them to, but it just messed with the pagination mechanism so much that i think i need to do some minor refactoring.

Did you ever end up finding a way to make this work?

@wz5899's $leftJoinRelation seems promising. I've been wracking my brain trying to get $modifyEager to work, but I can only seem to get it to modify which children appear in the relationship, not which parents.