Drizzle Team•2y ago

Many-to-Many joins results in weird values

Hey, I am new to Prisma, switching here from prisma so I can use a typesafe orm with D1 properly. I am trying to return a many-to-many joined result:

  const teamedUser = await db.select().from(usersToTeams)
    .where(eq(usersToTeams.user, user.id))
    .innerJoin(users, eq(usersToTeams.user, users.id))
    .innerJoin(teams, eq(usersToTeams.team, teams.id))
    .all();

  const teamedUser = await db.select().from(usersToTeams)
    .where(eq(usersToTeams.user, user.id))
    .innerJoin(users, eq(usersToTeams.user, users.id))
    .innerJoin(teams, eq(usersToTeams.team, teams.id))
    .all();

This already returns a correct structure:

[
  {
    usersToTeams: { user: 4, team: 2 },
    users: {
      id: 2,
      email: '<email>',
      password: '<password-hash>',
      name: '<this field is undefined, but weirdly it puts in the users email>'
    },
    teams: { id: undefined, name: undefined }
  }
]

[
  {
    usersToTeams: { user: 4, team: 2 },
    users: {
      id: 2,
      email: '<email>',
      password: '<password-hash>',
      name: '<this field is undefined, but weirdly it puts in the users email>'
    },
    teams: { id: undefined, name: undefined }
  }
]

The weird things are: - the name field of the user is not set (it's undefined in reality, if I use sqlite to look at the row), but my (maybe wrong) query fills it with the user's email?? - the teams object has only undefined values in it even though id as well as name are set - I'd like to not select the password field of the user, how can I do that? I tried looking up partial selects in combination with joined fields, but nothing that I tried worked I'd really appreciate help 🙂 drizzle really looks cool, I just have to learn a few more things, I guess. PS: if that matters, I am using the sqlite / D1 variant of drizzle 🙂

15 Replies

Andrii Sherman•2y ago

I guess it’s related to https://github.com/drizzle-team/drizzle-orm/issues/555

GitHub

[BUG]: Broken shifted columns with leftJoin and same column name (o...

What version of drizzle-orm are you using? 0.25.4 What version of drizzle-kit are you using? 0.17.6 Describe the Bug I got broken results with leftJoin with same columns. Details My schema (partial...

Andrii Sherman•2y ago

https://github.com/cloudflare/workers-sdk/issues/3160

GitHub

🐛 BUG: when trying to left join 2 tables that have same name column...

Which Cloudflare product(s) does this pertain to? D1 What version of Wrangler are you using? 2.19.0 What operating system are you using? macOS Describe the Bug As per issue #555 we can see, that d1...

divby0OP•2y ago

I'll ask about this on the cloudflare discord then, thanks a lot, Andrew! @Andrew Sherman The data is transferred over JSON from D1 back to your worker, and it's impossible for there to be 2 identical JSON keys in an object. I'd recommend you use SELECT "users".id as user_id, "posts".id as post_id or something like that instead This is what a cloudflare dev said. It makes sense, SQL does return a flat key value array, right? So it's normal that there can't be two with the same name, it's just that we think it would be possible due to using nested jsons.

Andrii Sherman•2y ago

@Dan Kochetov @alexblokh Yes, we are getting row array of arrays and then map them to the keys better-sqlite is working this way, and all other drivers I guess even d1 was working this way some time ago

divby0OP•2y ago

As someone with more experience, What's your recommendation? Should I just change my schema and rename the columns or do you think this will resolve soon?

alexblokh•2y ago

well, give me a sec

divby0OP•2y ago

Okay, tell me if I can help somehow or if you have anything 🙂

alexblokh•2y ago

import { eq, sql, getTableColumns} from "drizzle-orm";


const {id: userId, ...userRest} = getTableColumns(user)
const {id: teamId, ...teamRest} = getTableColumns(team)

console.log(db.select({
  user: {
    userId: sql`${userId}`.as("user_id"),
    ...userRest
  },
  team: {
    teamId: sql`${teamId}`.as("team_id"),
    ...teamRest
  }
})
  .from(usersToTeams)
  .innerJoin(user, eq(usersToTeams.userId, user.id))
  .innerJoin(team, eq(usersToTeams.teamId, team.id))
  .toSQL());

import { eq, sql, getTableColumns} from "drizzle-orm";


const {id: userId, ...userRest} = getTableColumns(user)
const {id: teamId, ...teamRest} = getTableColumns(team)

console.log(db.select({
  user: {
    userId: sql`${userId}`.as("user_id"),
    ...userRest
  },
  team: {
    teamId: sql`${teamId}`.as("team_id"),
    ...teamRest
  }
})
  .from(usersToTeams)
  .innerJoin(user, eq(usersToTeams.userId, user.id))
  .innerJoin(team, eq(usersToTeams.teamId, team.id))
  .toSQL());

well, that should work but that should definitely be fixed on the wrangler side

divby0OP•2y ago

Thanks so much Alex! I'm trying to understand it and try it and I'll come back to report I was able to get it working with that, I could even reduce the usage of this elongated syntax to only one of the "ambidiguous" fields, so I only needed to modify how I selected the team object, not the user object. The one thing I am not sure about is, what does the "user_id" string referring to, is it just an arbitrary unique placeholder that's used to write down the column value?

alexblokh•2y ago

as("user_id") is for "user"."id" as user_id in SQL

divby0OP•2y ago

Sorry for late response: I am not that sure if this is something that cloudflare will change. It seem like the dev was certain that better-sqlite3 wouldn't have this problem for example

divby0OP•2y ago

You seem to be certain tho that drizzle is doing it like better-sqlite3. And I am not at all educated about the inner workings of this so I can't really argue, but maybe it would make sense for someone from the drizzle team to write into the d1 channel on cloudflares discord to clarify

alexblokh•2y ago

please give me a link to the thread

Andrii Sherman•2y ago

@alexblokh https://discord.com/channels/595317990191398933/992060581832032316/1105479516991791124

divby0OP•2y ago

I saw they responded this: https://discord.com/channels/595317990191398933/992060581832032316/1107666967030145075 Does that make sense and can we as drizzle-users try that out or is that something you guys would need to change?

Gaming

Programming

Many-to-Many joins results in weird values