Nicolas Bourdin Posts - Answer Overflow

Nicolas Bourdin

Explore posts from servers

•Created by Nicolas Bourdin on 10/9/2024 in #help

select expression helper

I got this selectExpression that i use in many file .select([ 'e.id', eventStartDate('e'), eventEndDate('e'), 'e.title', 'e.type', 'e.status', 'e.banner', 'e.event_end_date as end_date', 'e.location', 'e.duration_in_minute', 'e.participant_count_limit', 'e.registration_date_limit', 'e.provider', coordinates('e'), sql<boolean>is_full_of_participants(e).as('isFullOfParticipants'), sql<string[]>get_event_sports(e.id).as('sports'), ]) how can i create a helper function in order to reuse it ?

9 replies

KKysely

•Created by Nicolas Bourdin on 7/2/2024 in #help

Create conditional cte insert query

Hello Is it possible to create a condition insert query with conditional cte like this :

    let query = db.with('new_group', (db) =>
      db
        .insertInto('group')
        .values({
          organization__id: orgId,
          name: name,
          avatar: avatarUrl,
          visibility: visibility,
          is_default: false,
        })
        .returningAll()
    )

    if (userId) {
      query.with('new_user', (db) =>
        db.insertInto('group_organization_user').values({
          user__id: userId,
          organization__id: orgId,
          group__id: query.selectFrom('new_group').select('id'),
        })
      )
    }

    if (!!data.sports) {
      query.with('sports', (query) =>
        query.insertInto('tag_organization_group').values(
          data.sports!.map((s) => ({
            organization__id: orgId,
            group__id: query.selectFrom('new_group').select('id'),
            tag__id: s,
          }))
        )
      )
    }

    const newGroup = await query.selectFrom('new_group').selectAll().executeTakeFirstOrThrow()

    return newGroup

2 replies

KKysely

•Created by Nicolas Bourdin on 6/6/2024 in #help

clone select query

Hello I need to 2 queries in parallel like this (select data + count)

let baseQuery = db.selectFrom('event')

    if (title) {
      baseQuery = baseQuery.where('title', 'ilike', '%' + title + '%')
    }

    const [data, count] = await Promise.all([
      baseQuery.select(['event.id', 'event.title']).execute(),
      baseQuery.select(({ fn }) => fn.count('event.id').as('total')).executeTakeFirstOrThrow(),
    ])

    return { data, count }

Is it possible to "clone" the baseQuery in order to make the same query for retrieving the data and the count ?

7 replies

Gaming

Programming