1"use strict";(globalThis.webpackChunkkysely_site=globalThis.webpackChunkkysely_site||[]).push([[93],{6013(e,n,i){i.r(n),i.d(n,{assets:()=>c,contentTitle:()=>a,default:()=>p,frontMatter:()=>o,metadata:()=>s,toc:()=>d});const s=JSON.parse('{"id":"recipes/deduplicate-joins","title":"Deduplicate joins","description":"Prevent duplicate joins in dynamic Kysely queries by configuring DeduplicateJoinsPlugin globally or for an individual query.","source":"@site/docs/recipes/0008-deduplicate-joins.md","sourceDirName":"recipes","slug":"/recipes/deduplicate-joins","permalink":"/docs/recipes/deduplicate-joins","draft":false,"unlisted":false,"editUrl":"https://github.com/kysely-org/kysely/tree/master/site/docs/recipes/0008-deduplicate-joins.md","tags":[],"version":"current","sidebarPosition":8,"frontMatter":{"description":"Prevent duplicate joins in dynamic Kysely queries by configuring DeduplicateJoinsPlugin globally or for an individual query."},"sidebar":"tutorialSidebar","previous":{"title":"Working with schemas","permalink":"/docs/recipes/schemas"},"next":{"title":"Dealing with the Type instantiation is excessively deep and possibly infinite error","permalink":"/docs/recipes/excessively-deep-types"}}');var t=i(1058),r=i(4801);const o={description:"Prevent duplicate joins in dynamic Kysely queries by configuring DeduplicateJoinsPlugin globally or for an individual query."},a="Deduplicate joins",c={},d=[];function l(e){const n={a:"a",code:"code",h1:"h1",header:"header",p:"p",pre:"pre",...(0,r.R)(),...e.components};return(0,t.jsxs)(t.Fragment,{children:[(0,t.jsx)(n.header,{children:(0,t.jsx)(n.h1,{id:"deduplicate-joins",children:"Deduplicate joins"})}),"\n",(0,t.jsx)(n.p,{children:"When building dynamic queries, you sometimes end up in situations where the same join\ncould be added twice. Consider this query:"}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{className:"language-ts",children:"async function getPerson(\n id: number,\n withPetName: boolean,\n withPetSpecies: boolean\n) {\n return await db\n .selectFrom('person')\n .selectAll('person')\n .$if(withPetName, (qb) =>\n qb\n .innerJoin('pet', 'pet.owner_id', 'person.id')\n .select('pet.name as pet_name')\n )\n .$if(withPetSpecies, (qb) =>\n qb\n .innerJoin('pet', 'pet.owner_id', 'person.id')\n .select('pet.species as pet_species')\n )\n .where('person.id', '=', id)\n .executeTakeFirst()\n}\n"})}),"\n",(0,t.jsxs)(n.p,{children:["We have two optional selections ",(0,t.jsx)(n.code,{children:"pet_name"})," and ",(0,t.jsx)(n.code,{children:"pet_species"}),". Both of them require\nthe ",(0,t.jsx)(n.code,{children:"pet"})," table to be joined, but we don't want to add an unnecessary join if both\n",(0,t.jsx)(n.code,{children:"withPetName"})," and ",(0,t.jsx)(n.code,{children:"withPetSpecies"})," are ",(0,t.jsx)(n.code,{children:"false"}),"."]}),"\n",(0,t.jsxs)(n.p,{children:["But if both ",(0,t.jsx)(n.code,{children:"withPetName"})," and ",(0,t.jsx)(n.code,{children:"withPetSpecies"})," are ",(0,t.jsx)(n.code,{children:"true"}),", we end up with two identical\njoins which will cause an error in the database."]}),"\n",(0,t.jsxs)(n.p,{children:["To prevent the error from happening, you can install the\n",(0,t.jsx)(n.a,{href:"https://kysely-org.github.io/kysely-apidoc/classes/DeduplicateJoinsPlugin.html",children:"DeduplicateJoinsPlugin"}),".\nYou can either install it globally by providing it in the configuration:"]}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{className:"language-ts",children:"const db = new Kysely<Database>({\n dialect,\n plugins: [new DeduplicateJoinsPlugin()],\n})\n"})}),"\n",(0,t.jsx)(n.p,{children:"or you can use it when needed:"}),"\n",(0,t.jsx)(n.pre,{children:(0,t.jsx)(n.code,{className:"language-ts",children:"async function getPerson(\n id: number,\n withPetName: boolean,\n withPetSpecies: boolean\n) {\n return await db\n .withPlugin(new DeduplicateJoinsPlugin())\n .selectFrom('person')\n .selectAll('person')\n .$if(withPetName, (qb) =>\n qb\n .innerJoin('pet', 'pet.owner_id', 'person.id')\n .select('pet.name as pet_name')\n )\n .$if(withPetSpecies, (qb)
1=>\n qb\n .innerJoin('pet', 'pet.owner_id', 'person.id')\n .select('pet.species as pet_species')\n )\n .where('person.id', '=', id)\n .executeTakeFirst()\n}\n"})}),"\n",(0,t.jsxs)(n.p,{children:["You may wonder why this is a plugin and not the default behavior? The reason is that it's surprisingly\ndifficult to detect if two joins are identical. It's trivial for simple joins like the ones in the\nexample, but becomes quite complex with arbitrary joins with nested subqueries etc. There may be\ncorner cases where the ",(0,t.jsx)(n.code,{children:"DeduplicateJoinsPlugin"})," fails and we don't want it to affect people that\ndon't need this deduplication (most people)."]}),"\n",(0,t.jsxs)(n.p,{children:["See ",(0,t.jsx)(n.a,{href:"/docs/recipes/conditional-selects",children:"this recipe"}),"\nif you are wondering why we are using the ",(0,t.jsx)(n.code,{children:"$if"})," method."]})]})}function p(e={}){const{wrapper:n}={...(0,r.R)(),...e.components};return n?(0,t.jsx)(n,{...e,children:(0,t.jsx)(l,{...e})}):l(e)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.