Drizzle Team•2y ago

How to do conditional joins with the query builder?

Hi, I'm looking for a way to do conditional join's without working with AnyPgSelect. Thanks in advance.

15 Replies

Mendy•2y ago

Are you trying to use the new relational query or the old syntax?

Dustin@WunderGraphOP•2y ago

I think the new one. db.select().from(table).innerJoin

Eternal Mori•2y ago

Here is a where clause with conditional params:

      .where(
        and(
          channel ? eq(s.tickets.channelId, channel) : undefined,
          status ? eq(s.tickets.status, status) : undefined,
          eq(s.channels.organizationId, organizationId)
        )
      )

      .where(
        and(
          channel ? eq(s.tickets.channelId, channel) : undefined,
          status ? eq(s.tickets.status, status) : undefined,
          eq(s.channels.organizationId, organizationId)
        )
      )

All equels with a undefined value are ignored

Dustin@WunderGraphOP•2y ago

I think you missed the point. I want to do conditional joins.

Mendy•2y ago

I didn’t try it, but you can pass an and function instead of eq where the first condition is the ‘magical’ sql utility and do something like this:

const result = await db.select().from(users).leftJoin(pets, 
  and(
    sql`${condition} IS TRUE`
    eq(users.id, pets.ownerId)
  )
)

const result = await db.select().from(users).leftJoin(pets, 
  and(
    sql`${condition} IS TRUE`
    eq(users.id, pets.ownerId)
  )
)

Dustin@WunderGraphOP•2y ago

I mean conditional joins like leftJoin or innerJoin not condition statements part of a single join.

Mendy•2y ago

I’m not sure I’m following.. Although my example does have a join, ultimately if condition is false the join will have no effect. Can you share a made-up example of what will be your ideal solution? Or better yet, a raw sql example.

Dustin@WunderGraphOP•2y ago

I want to do this without specifying all types on my own. This doesn't work because leftJoin and inerJoin return different types.

const query = this.db
  .select()
  .from(schema.users)
  // here
  if (myCondition) {
    query =  query.innerJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  } else {
    query =  query.leftJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  }
  const result = await query

const query = this.db
  .select()
  .from(schema.users)
  // here
  if (myCondition) {
    query =  query.innerJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  } else {
    query =  query.leftJoin(
        schema.projects,
        eq(schema.users.id, schema.projects.userId)
    )
  }
  const result = await query

Andrii Sherman•2y ago

cc @Dan Kochetov

Dan•2y ago

good question in this particular case, you can workaround like this:

const query = this.db.select().from(schema.users)[myCondition ? 'innerJoin' : 'leftJoin'](schema.projects, eq(schema.users.id, schema.projects.userId));

const query = this.db.select().from(schema.users)[myCondition ? 'innerJoin' : 'leftJoin'](schema.projects, eq(schema.users.id, schema.projects.userId));

Dustin@WunderGraphOP•2y ago

Hi @Dan Kochetov, tsc does not complain but innerJoin literal is not type-safe. Therefore according to my example above, I'd like to work with one concrete join and reassign the result back to query.

Dan•2y ago

why do you think it's not type-safe?

Dustin@WunderGraphOP•2y ago

I tested it. I don't have auto completion for the different join types.

Dan•2y ago

yes, you won't have the autocompletion for the method name, but that doesn't mean it's not type safe. if you enter an incorrect method name, it won't work.

Dustin@WunderGraphOP•2y ago

You're right, thx

Gaming

Programming

How to do conditional joins with the query builder?