Drizzle Team•8mo ago

How to fetch with multiple ids

    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id IN (3,4) AND event_id = ${eventId}`);

    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id IN (3,4) AND event_id = ${eventId}`);

This query gives me:

RangeError: Only finite numbers (not Infinity or NaN) can be passed as arguments

RangeError: Only finite numbers (not Infinity or NaN) can be passed as arguments

Hows everyone fetching with multiple ids?

3 Replies

goofysystem•8mo ago

So this works as expected

    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (3, 4) and event_id = ${eventId}`,
    );

    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (3, 4) and event_id = ${eventId}`,
    );

and this does not work :/

const ticketListingIds = [3,4]
    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (${ticketListingIds.join(', ')}) and event_id = ${eventId}`,
    );

const ticketListingIds = [3,4]
    const ticketListingRows = await orderDb.run(
        sql`select * from ticket_listing where id in (${ticketListingIds.join(', ')}) and event_id = ${eventId}`,
    );

The first code block works now. I just need to make everything lowercase. But now...

// Returns two rows
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (3,4) and event_id = ${eventId}`);

// Returns no rows
const ticketListingIds = [3,4];    
const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (${ticketListingIds.join(',')}) and event_id = ${eventId}`);

// Returns two rows
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (3,4) and event_id = ${eventId}`);

// Returns no rows
const ticketListingIds = [3,4];    
const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in (${ticketListingIds.join(',')}) and event_id = ${eventId}`);

The solution:

    const ticketListingIds = [3,4];
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in ${sql.raw(`(${ticketListingIds.join(',')})`)} and event_id = ${eventId}`);

    const ticketListingIds = [3,4];
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(sql`id in ${sql.raw(`(${ticketListingIds.join(',')})`)} and event_id = ${eventId}`);

Angelelz•8mo ago

You can use the inArray function provided by drizzle. Try to avoid using sql.raw as much as possible This is how this can be written:

const ticketListingIds = [3,4];
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(and(inArray(ticketListing.id, ticketListingIds), eq(ticketListing.eventId, eventId));

const ticketListingIds = [3,4];
    const ticketListingRows = await orderDb
        .select()
        .from(ticketListing)
        .where(and(inArray(ticketListing.id, ticketListingIds), eq(ticketListing.eventId, eventId));

goofysystem•8mo ago

@Angelelz thank you!

Gaming

Programming

How to fetch with multiple ids