Drizzle Team•6mo ago

Missing nested where clause

If you see here i'm missing the ability to do a where filter on the nested rawDetails table The docs say you can do so

await db.query.posts.findMany({
  where: (posts, { eq }) => (eq(posts.id, 1)),
  with: {
    comments: {
      where: (comments, { lt }) => lt(comments.createdAt, new Date()),
    },
  },
});

await db.query.posts.findMany({
  where: (posts, { eq }) => (eq(posts.id, 1)),
  with: {
    comments: {
      where: (comments, { lt }) => lt(comments.createdAt, new Date()),
    },
  },
});

My relations below

export const rawCardsRelations = relations(rawCards, ({ one }) => ({
    rawDetails: one(rawDetails),
}))

export const rawDetailsRelations = relations(rawDetails, ({ one }) => ({
    rawCards: one(rawCards, {
        fields: [rawDetails.rawCardsId],
        references: [rawCards.id],
    }),
}))

export const rawCardsRelations = relations(rawCards, ({ one }) => ({
    rawDetails: one(rawDetails),
}))

export const rawDetailsRelations = relations(rawDetails, ({ one }) => ({
    rawCards: one(rawCards, {
        fields: [rawDetails.rawCardsId],
        references: [rawCards.id],
    }),
}))

16 Replies

Aaroned•6mo ago

@DiamondDragon you will find there is no where property because this is a one-to-one relationship. The example you cite from the documentation is a one-to-many relationship. If you want add a where clause for a one-to-one, or many-to-one relationship, you need to use exists with a subquery in the top-level where clause.

DiamondDragon•6mo ago

im trying to understand this better. How should this look? Getting an error SQL input error: no such column: raw_cards.id (at offset 636) with this but sounds like the query would looking something close to this?

const query = await db.query.rawCards.findFirst({
    with: {
        rawDetails: {
            columns: {
                zId: true,
                rawData: true,
                lastFetchedDate: true,
            },
        },
    },
    where: notExists(
        db.select().from(rawDetails).where(eq(rawDetails.rawCardsId, rawCards.id)),
    ),
})

const query = await db.query.rawCards.findFirst({
    with: {
        rawDetails: {
            columns: {
                zId: true,
                rawData: true,
                lastFetchedDate: true,
            },
        },
    },
    where: notExists(
        db.select().from(rawDetails).where(eq(rawDetails.rawCardsId, rawCards.id)),
    ),
})

Fortunately i was able to get the right query i think using a select with left join

Aaroned•6mo ago

@DiamondDragon with regards to the error: is your db in sync with your schema?

DiamondDragon•6mo ago

yeah should be. This is raw_details

Aaroned•6mo ago

@DiamondDragon and what about raw_cards?

DiamondDragon•6mo ago

@Aaroned

Aaroned•6mo ago

@DiamondDragon can you use

const sql = db.query.rawCards.findFirst({
    with: {
        rawDetails: {
            columns: {
                zId: true,
                rawData: true,
                lastFetchedDate: true,
            },
        },
    },
    where: notExists(
        db.select().from(rawDetails).where(eq(rawDetails.rawCardsId, rawCards.id)),
    ),
}).toSQL()

const sql = db.query.rawCards.findFirst({
    with: {
        rawDetails: {
            columns: {
                zId: true,
                rawData: true,
                lastFetchedDate: true,
            },
        },
    },
    where: notExists(
        db.select().from(rawDetails).where(eq(rawDetails.rawCardsId, rawCards.id)),
    ),
}).toSQL()

to see the SQL that drizzle generates

DiamondDragon•6mo ago

Query: select "id", "z_id", "county_id", "z_update_date", "z_is_new", "z_is_updated", "last_fetched_date", "created_at", "updated_at", "deleted_at", (select json_array("z_id", "raw_data", "last_fetched_date") as "data" from (select * from "raw_details" "rawCards_rawDetails" where "rawCards_rawDetails"."raw_cards_id" = "rawCards"."id" limit ?) "rawCards_rawDetails") as "rawDetails" from "raw_cards" "rawCards" where exists (select "id", "raw_cards_id", "z_id", "z_update_date", "raw_data", "last_fetched_date", "created_at", "updated_at", "deleted_at" from "raw_details" where "raw_details"."raw_cards_id" = "raw_cards"."id") limit ? -- params: [1, 1] this is using exists so should be notExist but still have that error anyways what i notice with this is it returns all the rows from rawCards when it should be just one row

const query2 = await db
    .select()
    .from(rawCards)
    .where(
        exists(
            db
                .select()
                .from(rawDetails)
                .where(eq(rawDetails.rawCardsId, 'E2LgnvUshLiP')),
        ),
    )
    .limit(2)

console.log(query2)

const query2 = await db
    .select()
    .from(rawCards)
    .where(
        exists(
            db
                .select()
                .from(rawDetails)
                .where(eq(rawDetails.rawCardsId, 'E2LgnvUshLiP')),
        ),
    )
    .limit(2)

console.log(query2)

Aaroned•6mo ago

@DiamondDragon yes I wonder if this is a bug. Your table name is different to the name of the object in the schema ("rawCards" vs "raw_cards") which is fine. The relations query then aliases raw_cards as rawCards, but the subquery doesn't have the alias, hence the error. I might try and create a simple repo and log it as a bug. yes this is correct because you've hard coded an id, and this subquery will return true for every row, hence return every row In the mean time, if you want to return the first rawCard that doesn't have a rawDetail, then yes you can use left join and test for null

DiamondDragon•6mo ago

ah yea, i understand that now . the exists in that returns true every each row then returns all rawCards. cool thx for your help and filing the issue. always interesting to find edge cases with drizzle 😄 im basically querying for data that doesnt exists on table B but table A has the id (and also if data not updated within last X days. Not sure if this is the most efficient way but seems to work well )

Aaroned•6mo ago

yeah left join will work well 👍 sorry @DiamondDragon I just realised it's likely not a bug, you probably just need to use the callback syntax:

const query = await db.query.rawCards.findFirst({
    with: {
        rawDetails: {
            columns: {
                zId: true,
                rawData: true,
                lastFetchedDate: true,
            },
        },
    },
    where: (rawCard, { notExists }) => notExists(
        db.select().from(rawDetails).where(eq(rawDetails.rawCardsId, rawCard.id)),
    ),
})

const query = await db.query.rawCards.findFirst({
    with: {
        rawDetails: {
            columns: {
                zId: true,
                rawData: true,
                lastFetchedDate: true,
            },
        },
    },
    where: (rawCard, { notExists }) => notExists(
        db.select().from(rawDetails).where(eq(rawDetails.rawCardsId, rawCard.id)),
    ),
})

DiamondDragon•6mo ago

ah yes this seems to work. why do the docs pass through in teh 2nd parameter the operators? This to work fine without passing them in. Also wish the docs explained a little better what the 1st parameter means (seems like it's the table being queried?

    where: (rawCards) =>
        notExists(
            db
                .select()
                .from(rawDetails)
                .where(eq(rawDetails.rawCardsId, rawCards.id)),
        ),

    where: (rawCards) =>
        notExists(
            db
                .select()
                .from(rawDetails)
                .where(eq(rawDetails.rawCardsId, rawCards.id)),
        ),

Aaroned•6mo ago

yes the 1st parameter fields is the table you are querying, and the 2nd parameter operators is just a shorthand to using operators without needing imports

DiamondDragon•6mo ago

quick question, im running a scraper so i am running this file every now and then which uses this query to get the records to scrape. noticing im doing a ton of reads. Im not familiar with indexes but should i create an index on a few of the columns here?

const queryCards = await db
        .select({
            id: rawCards.id,
            zMapsId: rawCards.zId,
            rawData: rawDetails.rawData,
            lastFetchedDate: rawDetails.lastFetchedDate,
        })
        .from(rawCards)
        .leftJoin(rawDetails, eq(rawCards.id, rawDetails.rawCardsId))
        .limit(189)
        .where(
            and(
                eq(rawCards.countyId, 'xx'),
                or(
                    isNull(rawDetails.rawCardsId),
                    isNull(rawDetails.rawData),
                    lt(
                        rawDetails.lastFetchedDate,
                        sub(new Date(), { days: 90 }).toISOString(),
                    ),
                ),
            ),
        )

const queryCards = await db
        .select({
            id: rawCards.id,
            zMapsId: rawCards.zId,
            rawData: rawDetails.rawData,
            lastFetchedDate: rawDetails.lastFetchedDate,
        })
        .from(rawCards)
        .leftJoin(rawDetails, eq(rawCards.id, rawDetails.rawCardsId))
        .limit(189)
        .where(
            and(
                eq(rawCards.countyId, 'xx'),
                or(
                    isNull(rawDetails.rawCardsId),
                    isNull(rawDetails.rawData),
                    lt(
                        rawDetails.lastFetchedDate,
                        sub(new Date(), { days: 90 }).toISOString(),
                    ),
                ),
            ),
        )

Aaroned•6mo ago

@DiamondDragon https://www.sqlite.org/optoverview.html this is a good page for understanding SQLite (ie. Turso libsql) query optimization and indexes. SQLite does NOT automatically create indexes on foreign keys, so if you are not sure, that is generally a good place to start. Another helpful tool for optimisation is EXPLAIN QUERY PLAN https://www.sqlite.org/eqp.html this can be run through the Turso CLI db shell command. (You will need to first obtain the SQL of the Drizzle query by using .toSQL() at the end of the query) You really want to avoid TABLE SCANS as each row is read in a table scan and counts to the Turso Rows Read quota. By adding appropriate indexes that SQLite can use for your query, the query engine will use INDEX SCANS and avoid TABLE SCANS. See also https://turso.tech/blog/tips-for-maximizing-your-turso-billing-allowances-48a0fca163e9

Tips for maximizing your Turso billing allowances

Make the most out of your Turso usage allowance, and boost the performance of your SQLite queries at the same time, using indexes and triggers.

Gaming

Programming

Missing nested where clause