Drizzle Team•3y 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•3y 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

liv•3y ago

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

Andrii Sherman•3y 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

liv•3y 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•3y ago

sorry, was a bit busy to get through all threads

liv•3y ago

that’s ok

Andrii Sherman•3y ago

we will get more examples in docs for such cases sure

liv•3y ago

whenever you have time i’d love that thanks

NoahhOP•3y ago

Thanks @Andrew Sherman ! Do you think that filtering like this would come to the RQB at any point? Does it seem like a valid use-case for Drizzle?

liv•3y ago

is this in the docs somewhere now btw?

Gaming

Programming

Filtering findMany using related records

Did you find this page helpful?