Relations module - or condition

Ssmoke5/20/2023
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.
Ssmoke5/20/2023
Anyone that could help me out with this? Especially because this is some ‘or’ condition, not quite sure
Ssmoke5/21/2023
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)
  ),
});
ASAndrii Sherman5/21/2023
I will add all conditions to callback, just missed this before release

But you can always import or from drizzle-orm
ASAndrii Sherman5/21/2023
Should work fine
ASAndrii Sherman5/21/2023
You can see both usages here
Ssmoke5/21/2023
I understand! But is that the correct way of checking if that specific userId is connected through the join table?
Ssmoke5/21/2023
I just thought of a similar method as the some property from Prisma, not too sure though
Ssmoke5/21/2023
Because I can’t access teamMembers under the team property, like a deep condition.
Ssmoke5/22/2023
Anyone that could help with this? I’d really appreciate it!
Ssmoke5/22/2023
For example, this is how I currently get all teams from an user whether they are the owner or connected through the join table:

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.
ASAndrii Sherman5/22/2023
Was a bit busy
Will take a look now
Ssmoke5/22/2023
Of course no worries at all. I appreciate it!
ASAndrii Sherman5/22/2023
So the questions is how to make this in relational queries?
ASAndrii Sherman5/22/2023
just trying to understand exact question
Ssmoke5/22/2023
Yes exactly!
Ssmoke5/22/2023
If that’s possible
Ssmoke5/22/2023
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.
Ssmoke5/23/2023
@Andrii Sherman Did you have time to take a look into this issue? I'd appreciate it!
ASAndrii Sherman5/23/2023
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 leftJoin
Ssmoke5/23/2023
Where would in come from? Or do you mean inArray? Because I can't use team.teamMembers with inArray, that gives me a type error.
ASAndrii Sherman5/23/2023
Now I got it!

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 Queries

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 ownerId in team table, just for some other purposes
Maybe 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 queries
ASAndrii Sherman5/23/2023
Hope I got you right
Ssmoke5/23/2023
Yes I understand what you mean, that does make more sense honestly and that would also improve the performance.
Ssmoke5/23/2023
Thanks a lot, I'll just go with that approach. I appreciate it!
ASAndrii Sherman5/23/2023
Sorry to answer for so long, I was just really trying to understand the case
ASAndrii Sherman5/23/2023
Hope it will work for you
Ssmoke5/23/2023
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.
Ssmoke5/23/2023
That's why I had a direct ownerId column on the teams table.
ASAndrii Sherman5/23/2023
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 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';
ASAndrii Sherman5/23/2023
in this case it won't allow >1 owner for team
ASAndrii Sherman5/23/2023
but will allow >1 member
ASAndrii Sherman5/23/2023
and everything will be on a database level
ASAndrii Sherman5/23/2023
this is a Postgres example
ASAndrii Sherman5/23/2023
but I pretty sure you can find the same for other db's
Ssmoke5/23/2023
I see! That will definitely work
Ssmoke5/23/2023
I'll try that out, thanks a lot!
ASAndrii Sherman5/23/2023
🫡
Ssmoke5/23/2023
I assume that it is currently not possible to use that ‘or’ condition as in my previous query, using the new relations module?