Drizzle Team•2y ago

Filtering findMany using related records

I have a restaurants table that has a one-to-one relation with an addresses table. I want to pull all of the restaurants and their address within specific lat/long bounds, but I'm struggling to figure out how to do that. A simplified version of what I want/have so far

await this.drizzleService.db.query.restaurants.findMany({
  where: (table) => and(between(/* what could I use here to specify table.address.latitude */, minLat, maxLat)),
  with: {
    address: true
  }
});

await this.drizzleService.db.query.restaurants.findMany({
  where: (table) => and(between(/* what could I use here to specify table.address.latitude */, minLat, maxLat)),
  with: {
    address: true
  }
});

I'm trying to figure out if this is somethin Drizzle can do or if I need to break into using sql`` to do it

10 Replies

Andrii Sherman•2y ago

if you need to filter by address I guess you need to write this where inside address

await this.drizzleService.db.query.restaurants.findMany({
  with: {
    address: {
        where: (t) => and([eq(t.lat, 13.3), eq(t.lon, 13.3)]),
    }
  }
});

await this.drizzleService.db.query.restaurants.findMany({
  with: {
    address: {
        where: (t) => and([eq(t.lat, 13.3), eq(t.lon, 13.3)]),
    }
  }
});

didn't check in idea just typed here if I got you right ofc

finn•2y ago

wouldn't this still return all restaurants, but only return the address for those that satisfy the bounds?

Andrii Sherman•2y ago

oh, you are right. In you need to query by relations I guess it's not possible yet, so you need to write where statement as sql we have only an example for size https://orm.drizzle.team/docs/rqb#select-filters and not for more complex queries I guess for this case you can use core api + aggregation like this https://orm.drizzle.team/docs/joins#aggregating-results

finn•2y ago

i’d really appreciate some more complex examples (as i imagine OP would). i’ve been trying to figure it out in my thread

Andrii Sherman•2y ago

sorry, was a bit busy to get through all threads

finn•2y ago

that’s ok

Andrii Sherman•2y ago

we will get more examples in docs for such cases sure

finn•2y ago

whenever you have time i’d love that thanks