kewp Posts - Answer Overflow

kewp

Explore posts from servers

•Created by kewp on 7/26/2023 in #help

Unions in a loop

I'm trying to do a UnionAll in a loop. My issue is I don't know how to initialise the query:

    let query = ...// ???

    labels.forEach( label => {
        let codes = get_codes(label);
        query = query.unionAll(
            db.selectFrom("data")
            .select(eb => [
                eb.val(label).as('label'),
                'data.product',
                'data.period',
            ])
            .where("codes", "in", codes)
        );
    })

    return await query.execute()

    let query = ...// ???

    labels.forEach( label => {
        let codes = get_codes(label);
        query = query.unionAll(
            db.selectFrom("data")
            .select(eb => [
                eb.val(label).as('label'),
                'data.product',
                'data.period',
            ])
            .where("codes", "in", codes)
        );
    })

    return await query.execute()

Thanks for any suggestions. K

7 replies

KKysely

•Created by kewp on 5/16/2023 in #help

where clause with length

How do I have a where clause with a length, e.g. select * from data where length(prodcode) = 3

8 replies

KKysely

•Created by kewp on 5/16/2023 in #help

show generated sql

how can i see what the generated sql is for a kysely query? e.g.

result = await db.selectFrom("data")
        .select(['partner_cd', 'reporter_cd', 'product_cd', 'period'])
        .where((wb) =>
            wb.where("reporter_cd", "=", region1).where("partner_cd", "=", region2),
        )
        .orWhere((wb) =>
            wb.where("partner_cd", "=", region1).where("reporter_cd", "=", region2),
        )
        .execute()

result = await db.selectFrom("data")
        .select(['partner_cd', 'reporter_cd', 'product_cd', 'period'])
        .where((wb) =>
            wb.where("reporter_cd", "=", region1).where("partner_cd", "=", region2),
        )
        .orWhere((wb) =>
            wb.where("partner_cd", "=", region1).where("reporter_cd", "=", region2),
        )
        .execute()

5 replies

KKysely

•Created by kewp on 5/12/2023 in #help

Property does not exist on type

Hello. I'm trying to run the following query:

result = await db.selectFrom("data")
    .select(['partner_cd','reporter_cd'])
    .where(({ cmpr, or, and, not, exists, selectFrom, val }) => or([
      and([
        cmpr('reporter_cd', '=', country),
        cmpr('country_cd', '=', partner)
      ]),
      and([
        cmpr('country_cd', '=', country),
        cmpr('reporter_cd', '=', partner)
      ]),
    ]))
    .execute();

result = await db.selectFrom("data")
    .select(['partner_cd','reporter_cd'])
    .where(({ cmpr, or, and, not, exists, selectFrom, val }) => or([
      and([
        cmpr('reporter_cd', '=', country),
        cmpr('country_cd', '=', partner)
      ]),
      and([
        cmpr('country_cd', '=', country),
        cmpr('reporter_cd', '=', partner)
      ]),
    ]))
    .execute();

but I'm getting the following error:

Property 'cmpr' does not exist on type 'WhereInterface<From<DB, "data">, "data">'

Property 'cmpr' does not exist on type 'WhereInterface<From<DB, "data">, "data">'

Any help appreciated.

13 replies

Gaming

Programming