Adophilus Posts - Answer Overflow

Adophilus

Explore posts from servers

•Created by Adophilus on 4/2/2024 in #help

Is it possible to get the total count while fetching rows in a single query?

I have a query which looks like this:

const { items, count } = await db
  .selectFrom(eb => eb
    .selectFrom("ads")
    .selectAll()
    .$if(filters?.is_active !== undefined, eb => eb.where("is_active", "=", filters?.is_active as AdsTable["is_active"]))
    .$if(filters?.is_expired !== undefined, eb => eb.where(sql`expires > now()`))
    .offset((paginationOptions.page - 1) * paginationOptions.perPage)
    .limit(paginationOptions.perPage)
    .as("items"))
  .select(() => [
    sql<number>`cast(count(1) as int)`.as("count"),
    sql<Selectable<AdsTable>[]>`coalesce(json_agg(items), '[]')`.as("items")
  ])
  .executeTakeFirstOrThrow()

const { items, count } = await db
  .selectFrom(eb => eb
    .selectFrom("ads")
    .selectAll()
    .$if(filters?.is_active !== undefined, eb => eb.where("is_active", "=", filters?.is_active as AdsTable["is_active"]))
    .$if(filters?.is_expired !== undefined, eb => eb.where(sql`expires > now()`))
    .offset((paginationOptions.page - 1) * paginationOptions.perPage)
    .limit(paginationOptions.perPage)
    .as("items"))
  .select(() => [
    sql<number>`cast(count(1) as int)`.as("count"),
    sql<Selectable<AdsTable>[]>`coalesce(json_agg(items), '[]')`.as("items")
  ])
  .executeTakeFirstOrThrow()

I noticed that the 'count' only counts the result of the query and not all the rows in the table. Is it possible to make it to count all the rows in the table? I'm using PG

9 replies

KKysely

•Created by Adophilus on 3/6/2024 in #help

Is the Kysely main site down?

3 replies

KKysely

•Created by Adophilus on 12/29/2023 in #help

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"
}

5 replies

KKysely

•Created by Adophilus on 9/9/2023 in #help

MySQL Returning Statement Alternative

A while back I read online that MySQL does not support RETURNING statements at all. If that's the case how do I handle a situation in which I insert a new row and then get the inserted row back? Do I depend on the row being inserted to have some unique identifier I can use to fetch it after insertion or is there another way (in the event in which the only unique id is the primary key -- which is autogenerated)

21 replies

KKysely

•Created by Adophilus on 9/7/2023 in #help

Issue running migrations against MySQL database

20 replies

KKysely

•Created by Adophilus on 8/30/2023 in #help

Issue Getting TypeScript to work with nested query

20 replies

Gaming

Programming