Drizzle Team•6d ago

Exclude results with empty relation

I tried using isNotNull to filter out the results that doesn't has any value in availabilities relation, but didn't worked that way, I'm sure I'm missing an easier way of doing it.

6 Replies

GTOP•6d ago

( If u have any improvements to point out, I'm open to ideas tho ) By this I must run an subquery, that's right? If so, which would be the ways around for "Using the core API."? this one query is also pretty odd, is this the right way to do it? Seems like it should be more smth like this ( sure, the last one doesn't work as expected )

TOSL•6d ago

I might be wrong here. It's late I don't want to test this right now and I don't know your schema so it probably be pointless anyway but here is what I think. I think you should handle the conditions prior to the query. If employeeQuery.time is null or undefined you should be handing that early, at least that's what I would do. then you can just

with: {
  availabilities: {
    // I think just a between() can work here but you can look into that yourself
    where: and(conditions) // for and() all conditions must return true

with: {
  availabilities: {
    // I think just a between() can work here but you can look into that yourself
    where: and(conditions) // for and() all conditions must return true

If you don't want to do that or if it doesn't work, then try switching to the callback syntax because I think (again I'm not testing this) a lot of your problem stems from trying to shove a ternary in there.

availabilities: (
                employeeQuery.time ? {
                    where: and(
                        gte(availabilityTable.endTime, employeeQuery.time.endTime),
                        lte(availabilityTable.startTime, employeeQuery.time.startTime),
                        eq(availabilityTable.day, employeeQuery.day)
                    )
                } : true),

to

availabilities: {
  where: (employees => employeeQuery.time ? and(conditions) : undefined)
}

availabilities: (
                employeeQuery.time ? {
                    where: and(
                        gte(availabilityTable.endTime, employeeQuery.time.endTime),
                        lte(availabilityTable.startTime, employeeQuery.time.startTime),
                        eq(availabilityTable.day, employeeQuery.day)
                    )
                } : true),

to

availabilities: {
  where: (employees => employeeQuery.time ? and(conditions) : undefined)
}

if the where clause is undefined Drizzle will skip it and all results should be returned. Also put your subqueries in a variable

// it's harder than it should be to read this

notInArray(employeesTable.id, database.select({ data: employeesTable.id }).from(allocationsTable)
                .leftJoin(employeesTable, eq(employeesTable.id, allocationsTable.employeeId)).where(
                    eq(allocationsTable.eventId, employeeQuery.event.eventId)
                )

// you can just do this

const subquery = database.select({ data: employeesTable.id })
.from(allocationsTable)
.leftJoin(employeesTable, eq(employeesTable.id, allocationsTable.employeeId)).where(eq(allocationsTable.eventId,employeeQuery.event.eventId)

notInArray(employeesTable.id, subquery)

// it's harder than it should be to read this

notInArray(employeesTable.id, database.select({ data: employeesTable.id }).from(allocationsTable)
                .leftJoin(employeesTable, eq(employeesTable.id, allocationsTable.employeeId)).where(
                    eq(allocationsTable.eventId, employeeQuery.event.eventId)
                )

// you can just do this

const subquery = database.select({ data: employeesTable.id })
.from(allocationsTable)
.leftJoin(employeesTable, eq(employeesTable.id, allocationsTable.employeeId)).where(eq(allocationsTable.eventId,employeeQuery.event.eventId)

notInArray(employeesTable.id, subquery)

GTOP•5d ago

Thanks for the response, I actually did some of what y'v said or kinda tried. Also realized I didn't really gave much info abt the issue neither had explained it. - Time ternary I ended up placing in an if stmt cause even though it can be undefined, day always is defined. Not sure if using the array is the most particle way of doing it; - U were right abt the subquery, but I couldn't place it outside cause event might be undefined, so I switched to the callback version; The thing is that, even though I tried "prettifying it", I couldn't make it ignore the results where availabilities relation is empty. As mentioned earlier, by this link I came to the conclusion the way of doing it would be to add #filter(employees => employees.availabilities.lenght != 0), but by that, the returned result might be less than PAGE_SIZE sometimes, that's what I was trying to avoid and couldn't find an way using the core API - as mentioned in the link.

GTOP•5d ago

The other code I sent seems to be in the same problem described in the link mentioned. I had to do all those sub-queries because I'm unable to reference the "root event" inside the where in the relation.
Even though I could also pretiffy it after taking another look into it and seeing your message, I'm still using the same sub-query and I'm unable to reference the "root event".

Drizzle ORM - DrizzleORM v0.28.0 release

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

TOSL•5d ago

You're query looks fine, mostly, the only thing I see is that you don't need .execute() relation query API executes automatically when you await it. Can you make a simple recreation with https://drizzle.run/ might help your issue get solved

Drizzle Run

GTOP•5d ago

Oh, didn't knew abt that I'm being verbose everywhere then I thought sub-queries for simple fields would be smth considered bad in some sense

Gaming

Programming

Exclude results with empty relation

Did you find this page helpful?