DT
Join ServerDrizzle Team
help
Relations module - or condition
Hi there!
I have a teams table, an users table, and a team_members table that connects these two. A team also has an ownerId column.
Currently I have a query that fetches all the teams of an userId whether they are connected through the ownerId column or the join table.
I was wondering if this is possible using the new relations module? Since this is a many-to-many, but also an one-to-many, with an 'or' condition.
I have a teams table, an users table, and a team_members table that connects these two. A team also has an ownerId column.
Currently I have a query that fetches all the teams of an userId whether they are connected through the ownerId column or the join table.
I was wondering if this is possible using the new relations module? Since this is a many-to-many, but also an one-to-many, with an 'or' condition.
Anyone that could help me out with this? Especially because this is some ‘or’ condition, not quite sure
I expect something like this, similar to the
some
property from Prisma, but this currently does not exist in the relational queries:const userId = "foo";
await db.query.teams.findMany({
where: (team, { eq, or }) => or(
eq(team.ownerId, userId),
in(team.teamMembers, userId)
),
});
I will add all conditions to callback, just missed this before release
But you can always import
But you can always import
or
from drizzle-ormShould work fine
You can see both usages here
I understand! But is that the correct way of checking if that specific userId is connected through the join table?
I just thought of a similar method as the some property from Prisma, not too sure though
Because I can’t access teamMembers under the team property, like a deep condition.
Anyone that could help with this? I’d really appreciate it!
For example, this is how I currently get all teams from an user whether they are the owner or connected through the join table:
But I would like to use the new relations module for this.
const userId = "foo";
.leftJoin(teamMembers, eq(team.id, teamMembers.teamId))
.where(or(eq(team.ownerId, userId), eq(teamMembers.userId, userId)))
But I would like to use the new relations module for this.
Was a bit busy
Will take a look now
Will take a look now
Of course no worries at all. I appreciate it!
So the questions is how to make this in relational queries?
just trying to understand exact question
Yes exactly!
If that’s possible
I want to fetch all the teams for a specific userId, whether they are connected through the direct ownerId column or through the team_members join table.
@Andrii Sherman Did you have time to take a look into this issue? I'd appreciate it!
await db.query.teams.findMany({
where: (team, { eq, or }) => or(
eq(team.ownerId, userId),
in(team.teamMembers, userId)
),
});
and how does this not working for you? seems to be the exact query
you need to connect
teamMembers
to team
by one-many, same as I see in your query with leftJoinWhere would
in
come from? Or do you mean inArray
? Because I can't use team.teamMembers
with inArray
, that gives me a type error.Now I got it!
You just need to make this query
where
I guess you need to store all members of a team in a teamMembers table. Even if it's an owner. You can still have an
Maybe
With this schema design you don't need to do extra
You just need to make this query
import { or } from "drizzle-orm"
await db.query.teams.findMany({
with: {
members: true
},
});
where
members
is a relation defined by relations
function for Relational QueriesI guess you need to store all members of a team in a teamMembers table. Even if it's an owner. You can still have an
ownerId
in team table, just for some other purposesMaybe
teamMembers
table will have a type
columns which will be "member" | "owner"
With this schema design you don't need to do extra
or
statements, that will slow down your queriesHope I got you right
Yes I understand what you mean, that does make more sense honestly and that would also improve the performance.
Thanks a lot, I'll just go with that approach. I appreciate it!
Sorry to answer for so long, I was just really trying to understand the case
Hope it will work for you
No worries at all, I understand! That will definitely work for me. I thought of that approach first but that would technically allow multiple users to be an owner of a team but I think that will be fine.
That's why I had a direct ownerId column on the teams table.
You can do either a code level check to not allow more than 1 owner
So when adding user to a team, you can always check if team has or has not an ownerId value
you can also do a db level constraint. Something like a composite
We don't have such complex indexes in drizzle yet, but you can add this index manually on a migration(or in database itself)
So when adding user to a team, you can always check if team has or has not an ownerId value
you can also do a db level constraint. Something like a composite
unique
for 2 fields (teamId + type('owner'))We don't have such complex indexes in drizzle yet, but you can add this index manually on a migration(or in database itself)
CREATE UNIQUE INDEX some_idx_name ON team_members (team_id, type) WHERE type = 'owner';
in this case it won't allow >1 owner for team
but will allow >1 member
and everything will be on a database level
this is a Postgres example
but I pretty sure you can find the same for other db's
I see! That will definitely work
I'll try that out, thanks a lot!
🫡
I assume that it is currently not possible to use that ‘or’ condition as in my previous query, using the new relations module?