How can I do a join query for the sole purpose of filtering?

Essentially my question is how can I do this better in drizzle.

const myTeams = await ctx.db
      .select()
      .from(teams)
      .innerJoin(
        members,
        and(eq(members.teamId, teams.id), eq(members.userId, ctx.user.id)),
      );

    return myTeams.map((t) => t.team);

const myTeams = await ctx.db
      .select()
      .from(teams)
      .innerJoin(
        members,
        and(eq(members.teamId, teams.id), eq(members.userId, ctx.user.id)),
      );

    return myTeams.map((t) => t.team);

I really want to just write

SELECT T.*
FROM teams T, members M
WHERE T.id = M.teamId
AND M.userId = ?

SELECT T.*
FROM teams T, members M
WHERE T.id = M.teamId
AND M.userId = ?

11 Replies

KiKoOP•12mo ago

is this multiple tables with from a thing in drizzle?

Angelelz•12mo ago

Drizzle doesn't support selecting from multiple tables yet. You'll need to use a join You can select only from the table that you need.

KiKoOP•12mo ago

How would you rewrite this to do the same thing in a drizzle friendly way, this is how I was taught in school... also is there a shorthand to return only the properties on teams and avoid the .map? I know I could write out all of the properties manually

Angelelz•12mo ago

You can select whatever you want https://orm.drizzle.team/docs/select

SQL Select - Drizzle ORM

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

Angelelz•12mo ago

You already have the query written, is the innerJoin query that you have, isn't it?

KiKoOP•12mo ago

is the innerjoin the best way to do it? that's what I ultimately landed on but is there a correct way to do it also I dont see anything in the docs that includes a team.* select for example

Angelelz•12mo ago

You could do a subquery, but in terms of what's better, you'll need to evaluate with some tests I guess There's no team.* but there's getTableColumns() helper function that will get you something equivalent

Angelelz•12mo ago

https://orm.drizzle.team/docs/goodies#get-typed-table-columns

Goodies - Drizzle ORM

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

KiKoOP•12mo ago

That looks good thanks is there a plan to support multiple tables in the future? or is that a task too difficult for typescript

Angelelz•12mo ago

I don't know about the plan, but usually the join is the canonical way of joining tables It's what relational dbs do IMO, using a subquery could even read better:

db.select()
  .from(teams)
  .where(
    inArray(teams.id, db
      .select({ teamId: members.teamId })
      .from(members)
      .where(eq(members.userId, ctx.user.id))
    )

db.select()
  .from(teams)
  .where(
    inArray(teams.id, db
      .select({ teamId: members.teamId })
      .from(members)
      .where(eq(members.userId, ctx.user.id))
    )

KiKoOP•12mo ago

ahhh, thats cool Thank you very much

Gaming

Programming

How can I do a join query for the sole purpose of filtering?