Kysely•6mo ago

Join + Nested Object

I everyone 👋 . Before I ask my question, I know that there's already another question with the exact same title. I went through it hoping to find a solution there but to no avail. I'm trying to do the exact same thing as him (join two tables but join one as a property under the other table. The two tables in question here are lodges and lodge_conditions. I just wanna make a query such that I'll get back something of the type

Selectable<LodgesTable> & { conditions: Selectable<LodgeConditionsTable> }

Selectable<LodgesTable> & { conditions: Selectable<LodgeConditionsTable> }

without having to perform 2 separate queries. Here's what I've tried so far:

import db from "@homeease/server/db";
import { jsonObjectFrom } from 'kysely/helpers/mysql'


const main = async () => {
  // don't want this because it merges the tables into a single object
  // const lodges = await db.selectFrom("lodges")
  //   .selectAll()
  //   .leftJoin('lodge_conditions as conditions', 'lodges.conditions_id', 'conditions.id')
  //   .limit(10)
  //   .execute()

  const lodges = await db
    .selectFrom("lodges")
    .selectAll()
    .select((eb) => [
      "id",
      jsonObjectFrom(
        eb
          .selectFrom('lodge_conditions')
          .select([
            'lodge_conditions.id',
            'lodge_conditions.light',
            'lodge_conditions.water',
            'lodge_conditions.transport_fare_to_school',
            'lodge_conditions.network_coverage',
          ])
          .whereRef('lodges.conditions_id', '=', 'lodge_conditions.id')
      )
        .as('conditions')
    ])
    .limit(10)
    .execute()

  console.log("lodges:", lodges)
}

main()

import db from "@homeease/server/db";
import { jsonObjectFrom } from 'kysely/helpers/mysql'


const main = async () => {
  // don't want this because it merges the tables into a single object
  // const lodges = await db.selectFrom("lodges")
  //   .selectAll()
  //   .leftJoin('lodge_conditions as conditions', 'lodges.conditions_id', 'conditions.id')
  //   .limit(10)
  //   .execute()

  const lodges = await db
    .selectFrom("lodges")
    .selectAll()
    .select((eb) => [
      "id",
      jsonObjectFrom(
        eb
          .selectFrom('lodge_conditions')
          .select([
            'lodge_conditions.id',
            'lodge_conditions.light',
            'lodge_conditions.water',
            'lodge_conditions.transport_fare_to_school',
            'lodge_conditions.network_coverage',
          ])
          .whereRef('lodges.conditions_id', '=', 'lodge_conditions.id')
      )
        .as('conditions')
    ])
    .limit(10)
    .execute()

  console.log("lodges:", lodges)
}

main()

But it's giving me the following error:

Error: Unknown column 'lodges.conditions_id' in 'where clause'

Error: Unknown column 'lodges.conditions_id' in 'where clause'

Database: MariaDB (MySQL) 10.6.1

export type LodgesTable = {
  id: string
  conditions_id: string
}

export type LodgeConditionsTable = {
  id: string
  network_coverage: "POOR" | "AVERAGE" | "GOOD"
}

export type LodgesTable = {
  id: string
  conditions_id: string
}

export type LodgeConditionsTable = {
  id: string
  network_coverage: "POOR" | "AVERAGE" | "GOOD"
}

Solution:

Hey 👋 This is not supported in MariaDB, or old MySQL versions. Look into the source code of the helper for inspiration. Try a CTE or subquery that creates a record set of conditions_id and JSON object as columns and join with it on conditions_id and select just the JSON object column....

Jump to solution

2 Replies

Solution

Igal•6mo ago

Adophilus•6mo ago

okay

Gaming

Programming

Join + Nested Object