Drizzle Team•2w ago

Filter based on join data

Hi, would it be possible to query a list of student and filter by the name of its coach. The data is <student>.coach.fullname where coach is added as a with join. I would like to get every student that have a certain coach.

8 Replies

Cyber GrandmaOP•7d ago

Fiddling around in drizzle studio demo I managed to do something like that:

  db.select().from(orders).fullJoin(customers, eq(orders.customerId, customers.id)).where(eq(customers.contactTitle, "Owner"))

  db.select().from(orders).fullJoin(customers, eq(orders.customerId, customers.id)).where(eq(customers.contactTitle, "Owner"))

But I couldn't find a way to do it with query builder

Cyber GrandmaOP•7d ago

In the docs https://orm.drizzle.team/docs/rqb#select-filters there is a where inside of the query but when I try it this option doesn't exist

Drizzle ORM - Query

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

Angelelz•7d ago

This option was removed from drizzle due to the complexity of the resulting query But you can achieve the result you want with a subquery Can you put the query you want in drizzle.run so I can take a look?

sukeshpabolu•7d ago

https://github.com/drizzle-team/drizzle-orm/issues/1069

GitHub

[FEATURE]: Add back filtering by nested relations in relational que...

Describe what you want Filtering by nested relations is a common thing to do and was recently removed to improve performance. I propose a syntax to add this feature back into the relational queries...

sukeshpabolu•7d ago

will this be added back with relation api v2?

Angelelz•7d ago

I believe so

sukeshpabolu•7d ago

@Cyber Grandma you can comment to this issue and for further reference on other alternatives

Cyber GrandmaOP•6d ago

Thanks 🫡 Thanks, that's what I ended up doing.

Gaming

Programming

Filter based on join data