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.

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 or from drizzle-orm

Should 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:

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

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 leftJoin

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.

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

Hope 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 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?