Drizzle Team•2y ago

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.

19 Replies

smokeOP•2y ago

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)
  ),
});

const userId = "foo";

await db.query.teams.findMany({
  where: (team, { eq, or }) => or(
    eq(team.ownerId, userId),
    in(team.teamMembers, userId)
  ),
});

Andrii Sherman•2y ago

I will add all conditions to callback, just missed this before release But you can always import or from drizzle-orm Should work fine

Andrii Sherman•2y ago

https://orm.drizzle.team/docs/rqb#select-filters

Relational queries – DrizzleORM

Drizzle ORM | %s

Andrii Sherman•2y ago

You can see both usages here

smokeOP•2y ago

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)))

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.

Andrii Sherman•2y ago

Was a bit busy Will take a look now

smokeOP•2y ago

Of course no worries at all. I appreciate it!

Andrii Sherman•2y ago

So the questions is how to make this in relational queries? just trying to understand exact question

smokeOP•2y ago

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!

Andrii Sherman•2y ago

await db.query.teams.findMany({
  where: (team, { eq, or }) => or(
    eq(team.ownerId, userId),
    in(team.teamMembers, userId)
  ),
});

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

smokeOP•2y ago

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.

Andrii Sherman•2y ago

Now I got it! You just need to make this query

import { or } from "drizzle-orm"

await db.query.teams.findMany({
  with: {
    members: true
  },
});

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

smokeOP•2y ago

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!

Andrii Sherman•2y ago

Sorry to answer for so long, I was just really trying to understand the case Hope it will work for you

smokeOP•2y ago

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.

Andrii Sherman•2y ago

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';

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

smokeOP•2y ago

I see! That will definitely work I'll try that out, thanks a lot!

Andrii Sherman•2y ago

🫡

smokeOP•2y ago

I assume that it is currently not possible to use that ‘or’ condition as in my previous query, using the new relations module?

Gaming

Programming

Relations module - or condition