search

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 forces inner join - is there a $leftJoinRelation? #143

Open pskfry opened 3 years ago

pskfry commented 3 years ago

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 commented 3 years ago

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 notifications@github.com 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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 notifications@github.com 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 commented 3 years ago

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 commented 2 years ago

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

sokulski commented 2 years ago

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.