1"use strict";(globalThis.webpackChunkkysely_site=globalThis.webpackChunkkysely_site||[]).push([[309],{6015(e,n,t){t.r(n),t.d(n,{assets:()=>l,contentTitle:()=>c,default:()=>h,frontMatter:()=>r,metadata:()=>s,toc:()=>a});const s=JSON.parse('{"id":"recipes/conditional-selects","title":"Conditional selects","description":"Select columns conditionally with the Kysely $if method while preserving type inference for optional fields in query results.","source":"@site/docs/recipes/0005-conditional-selects.md","sourceDirName":"recipes","slug":"/recipes/conditional-selects","permalink":"/docs/recipes/conditional-selects","draft":false,"unlisted":false,"editUrl":"https://github.com/kysely-org/kysely/tree/master/site/docs/recipes/0005-conditional-selects.md","tags":[],"version":"current","sidebarPosition":5,"frontMatter":{"description":"Select columns conditionally with the Kysely $if method while preserving type inference for optional fields in query results."},"sidebar":"tutorialSidebar","previous":{"title":"Splitting query building and execution","permalink":"/docs/recipes/splitting-query-building-and-execution"},"next":{"title":"Expressions","permalink":"/docs/recipes/expressions"}}');var i=t(1058),o=t(4801);const r={description:"Select columns conditionally with the Kysely $if method while preserving type inference for optional fields in query results."},c="Conditional selects",l={},a=[];function d(e){const n={a:"a",admonition:"admonition",code:"code",em:"em",h1:"h1",header:"header",p:"p",pre:"pre",...(0,o.R)(),...e.components};return(0,i.jsxs)(i.Fragment,{children:[(0,i.jsx)(n.header,{children:(0,i.jsx)(n.h1,{id:"conditional-selects",children:"Conditional selects"})}),"\n",(0,i.jsx)(n.p,{children:"Sometimes you may want to select some fields based on a runtime condition.\nSomething like this:"}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-ts",children:"async function getPerson(id: number, withLastName: boolean) {}\n"})}),"\n",(0,i.jsxs)(n.p,{children:["If ",(0,i.jsx)(n.code,{children:"withLastName"})," is true the person object is returned with a ",(0,i.jsx)(n.code,{children:"last_name"}),"\nproperty, otherwise without it."]}),"\n",(0,i.jsx)(n.p,{children:"Your first thought can be to simply do this:"}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-ts",children:"async function getPerson(id: number, withLastName: boolean) {\n let query = db.selectFrom('person').select('first_name').where('id', '=', id)\n\n if (withLastName) {\n // \u274c The type of `query` doesn't change here\n query = query.select(['last_name', sql.val('person_with_last_name' as const).as('kind')])\n }\n\n // \u274c Wrong return type { first_name: string, kind: 'person' }\n return await query.select(sql.val('person' as const).as('kind')).executeTakeFirstOrThrow()\n}\n"})}),"\n",(0,i.jsxs)(n.p,{children:["While that ",(0,i.jsx)(n.em,{children:"would"})," compile, the result type would be ",(0,i.jsx)(n.code,{children:"{ first_name: string, kind: 'person' }"}),"\nwithout the ",(0,i.jsx)(n.code,{children:"last_name"})," column and ",(0,i.jsx)(n.code,{children:"kind"}),' being "person_with_last_name", which is wrong.\nWhat happens is that the type of ',(0,i.jsx)(n.code,{children:"query"})," when created is something, let's say ",(0,i.jsx)(n.code,{children:"A"}),".\nThe type of the query with ",(0,i.jsx)(n.code,{children:"last_name"})," selection is ",(0,i.jsx)(n.code,{children:"B"})," which extends ",(0,i.jsx)(n.code,{children:"A"})," but also contains\ninformation about the new selection. When you assign an object of type ",(0,i.jsx)(n.code,{children:"B"})," to ",(0,i.jsx)(n.code,{children:"query"})," inside\nthe ",(0,i.jsx)(n.code,{children:"if"})," statement, the type gets downcast to ",(0,i.jsx)(n.code,{children:"A"}),"."]}),"\n",(0,i.jsx)(n.admonition,{type:"info",children:(0,i.jsxs)(n.p,{children:["You ",(0,i.jsx)(n.em,{children:"can"})," write code like this to add conditional ",(0,i.jsx)(n.code,{children:"where"}),", ",(0,i.jsx)(n.code,{children:"groupBy"}),", ",(0,i.jsx)(n.code,{children:"orderBy"})," etc.\nstatements that don't change the type of the query builder, but it doesn't work\nwith ",(0,i.jsx)(n.code,{children:"select"}),", ",(0,i.jsx)(n.code,{children:"returning"}),", ",(0,i.jsx)(n.code,{children:"innerJoin"})," etc. that ",(0,i.jsx)(n.em,{children:"do"})," change the type of the\nquery builder."]})}),"\n",(0,i.jsx)(n.p,{children:"In this simple case you could implement the method like this:"}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-ts",children:'async function getPerson(id: number, withLastName: boolean) {\n const query = db\n .selectFrom("person")\n .select("first_name")\n .where("id", "=", id);\n\n if (withLastName) {\n // \u2705 The return type is { first_name: string, last_name: string, kind: \'person_with_last_name\' }\n return await query\n .select([\n "last_name",\n sql.val("person_with_last_name").as("kind"),\n ])\n .executeTakeFirstOrThrow();\n }\n\n // \u2705 The return type is { first_name: string, kind: \'person\' }\n return await query\n .select(sql.val("person").as("kind"))\n .executeTakeFirstOrThrow();\n}\n'})}),"\n",(0,i.jsx)(n.p,{children:"This works fine when you have one single condition. As soon as you have two or more\nconditions the amount of code explodes if you want to keep things type-safe. You need\nto create a separate branch for every possible combination of selections or otherwise\nthe types won't be correct."}),"\n",(0,i.jsxs)(n.p,{children:["This is where the ",(0,i.jsx)(n.a,{href:"https://kysely-org.github.io/kysely-apidoc/interfaces/SelectQueryBuilder.html#_if",children:"$if"}),"\nmethod can help you:"]}),"\n",(0,i.jsx)(n.pre,{children:(0,i.jsx)(n.code,{className:"language-ts",children:'async function getPerson(id: number, withLastName: boolean) {\n // \u2705 The return type is { first_name: string, last_name?: string }\n return await db\n .selectFrom("person")\n .select("first_name")\n .$if(withLastName, (qb) => qb.select("last_name"))\n .where("id", "=", id)\n .executeTakeFirstOrThrow();\n}\n'})}),"\n",(0,i.jsxs)(n.p,{children:["Any selections added inside the ",(0,i.jsx)(n.code,{children:"$if"})," callback will be added as optional fields to the\noutput type since we can't know if the selections were actually made before running\nthe code."]}),"\n",(0,i.jsxs)(n.p,{children:["A downside of ",(0,i.jsx)(n.code,{children:"$if"})," is that, unlike the imperative example, it cannot result in discriminated\nunion return types - ",(0,i.jsx)(n.code,{children:"kind"})," would be a union of ",(0,i.jsx)(n.code,{children:"'person' | 'person_with_last_name'"}),"."]})]})}function h(e={}){const{wrapper:n}={...(0,o.R)(),...e.components};return n?(0,i.jsx)(n,{...e,children:(0,i.jsx)(d,{...e})}):d(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.