Topics

Drizzle Team•17mo ago

Nested joins / where on relation

Hey guys! I have a question regarding filtering a query. Basically, I need to filter the query by order relation - order.date is the field I want to filter by. However, I've noticed that filtering by relations is not yet supported. How do I write the query with joins? I can't figure out how to created nested joins (subOrders -> order and subOrders -> product -> kitchens -> kitchen etc.) This is my query:

const suborders = await db.query.subOrders.findMany({
    where: and(isNull(subOrders.kitchenId)),
    columns: {
      id: true,
      quantity: true,
    },
    with: {
      order: true,
      product: {
        columns: {
          id: true,
          performancePoints: true,
          prepTime: true,
        },
        with: {
          kitchens: {
            with: {
              kitchen: {
                columns: {
                  id: true,
                  performancePoints: true,
                },
              },
            },
          },
          kitchenDependentProperties: {
            columns: {
              kitchenId: true,
              productionPrice: true,
              purchasePrice: true,
            },
          },
        },
      },
    },
  });

const suborders = await db.query.subOrders.findMany({
    where: and(isNull(subOrders.kitchenId)),
    columns: {
      id: true,
      quantity: true,
    },
    with: {
      order: true,
      product: {
        columns: {
          id: true,
          performancePoints: true,
          prepTime: true,
        },
        with: {
          kitchens: {
            with: {
              kitchen: {
                columns: {
                  id: true,
                  performancePoints: true,
                },
              },
            },
          },
          kitchenDependentProperties: {
            columns: {
              kitchenId: true,
              productionPrice: true,
              purchasePrice: true,
            },
          },
        },
      },
    },
  });

46 Replies

PiotrekOP•17mo ago

I tried with something like

const suborders = await db
    .select({
      id: subOrders.id,
      quantity: subOrders.quantity,
    })
    .from(subOrders)
    .leftJoin(orders, and(eq(subOrders.orderId, orders.id)))
    .leftJoin(products, eq(subOrders.productId, products.id))
// how do I add nested join to product?    
.where(and(isNull(subOrders.kitchenId), sql`<GET_ORDER_DATE_SOMEHOW> = ${tommorrow}`));

const suborders = await db
    .select({
      id: subOrders.id,
      quantity: subOrders.quantity,
    })
    .from(subOrders)
    .leftJoin(orders, and(eq(subOrders.orderId, orders.id)))
    .leftJoin(products, eq(subOrders.productId, products.id))
// how do I add nested join to product?    
.where(and(isNull(subOrders.kitchenId), sql`<GET_ORDER_DATE_SOMEHOW> = ${tommorrow}`));

Asseater Peter•17mo ago

Jesus Christ That's a block Give me a sec Wait what? filtering by relations should work, no? Yeah, it definitely does...

PiotrekOP•17mo ago

It does? I want to be able to do something that works like that:

 where: and(
      isNull(subOrders.kitchenId),
      eq(subOrders.order.date, tommorrow)
    ),

 where: and(
      isNull(subOrders.kitchenId),
      eq(subOrders.order.date, tommorrow)
    ),

of course this thing does not work ^ and I don't know how to implement it also, there's no where on the with.order object - it's a parent-child relation in one-to-many, not the reverse

Mendy•17mo ago

Previously with RQB you could filter by a relation one level deep. If I’m not mistaken, this feature was removed in the latest update - to help with typescript performance. AFAIK this is achievable with the regular query builder, I suggest you search for other similar questions here.

PiotrekOP•17mo ago

Thanks for help! could you send me a link to the thread? I cannot find it... Also, if I would be able to filter with the regular qb - how to create nested joins? As you can see I have to have a lot of layers of relations and I can't find an example using the same with the .select() api

Mendy•17mo ago

Well tbh I almost never touched the regular qb for select ops. As for the thread, search for comments posted by me you’ll find it in no time.

PiotrekOP•17mo ago

ok thanks 🙂

Asseater Peter•17mo ago

You can definitely filter within relations in 28.1 (Which I think is latest) @peterpkp123 From the docs:

await db.query.posts.findMany({
    where: (posts, { eq }) => (eq(posts.id, 1)),
    with: {
        comments: {
            where: (comments, { lt }) => lt(comments.createdAt, new Date()),
        },
    },
});

await db.query.posts.findMany({
    where: (posts, { eq }) => (eq(posts.id, 1)),
    with: {
        comments: {
            where: (comments, { lt }) => lt(comments.createdAt, new Date()),
        },
    },
});

Mendy•17mo ago

It’s outdated. See here https://discord.com/channels/1043890932593987624/1052239146699210863/1137795951491289110

Asseater Peter•17mo ago

I have used it today tho, it definitely works...

PiotrekOP•17mo ago

It works in a very wierd way - if the with object contains many children it works, if it's a parent in one-to-many relation it does not

Asseater Peter•17mo ago

Just verified. it works Well, if it's a one-to-one the parent has an "assigned" relation so I can see why it wouldn't

PiotrekOP•17mo ago

PiotrekOP•17mo ago

it's a one-to-many though not one-to-one

Asseater Peter•17mo ago

Yeah, but you wrote the or incorrectly

PiotrekOP•17mo ago

it's order, just covered by the error message haha

Asseater Peter•17mo ago

Oh kekw Could you show me the code without the error message?

PiotrekOP•17mo ago

Here you go, the entire block once again:

const suborders = await db.query.subOrders.findMany({
    where: and(
      isNull(subOrders.kitchenId),
    ),
    columns: {
      id: true,
      quantity: true,
    },
    with: {
      order: {
        where: (table, { eq }) => eq(table.date, tommorrow),
      },
      product: {
        columns: {
          id: true,
          performancePoints: true,
          prepTime: true,
        },
        with: {
          kitchens: {
            with: {
              kitchen: {
                columns: {
                  id: true,
                  performancePoints: true,
                },
              },
            },
          },
          kitchenDependentProperties: {
            columns: {
              kitchenId: true,
              productionPrice: true,
              purchasePrice: true,
            },
          },
        },
      },
    },
  });

const suborders = await db.query.subOrders.findMany({
    where: and(
      isNull(subOrders.kitchenId),
    ),
    columns: {
      id: true,
      quantity: true,
    },
    with: {
      order: {
        where: (table, { eq }) => eq(table.date, tommorrow),
      },
      product: {
        columns: {
          id: true,
          performancePoints: true,
          prepTime: true,
        },
        with: {
          kitchens: {
            with: {
              kitchen: {
                columns: {
                  id: true,
                  performancePoints: true,
                },
              },
            },
          },
          kitchenDependentProperties: {
            columns: {
              kitchenId: true,
              productionPrice: true,
              purchasePrice: true,
            },
          },
        },
      },
    },
  });

Mendy•17mo ago

Ok that’s weird. Are you sure you’re on the latest version? Maybe I misinterpreted the release note 🤷‍♂️

Asseater Peter•17mo ago

Really weird that that errors out for you, as this code is currently working for me with no problems:

db.query.UserTable.findMany({
      with: {
        sent: {
          where: (message, { eq }) => eq(message.id, 4),
          orderBy: (message, { desc }) => desc(message.timestamp),
          limit: 1,
        },
        received: {
          where: (message, { eq }) => eq(message.id, 4),
          orderBy: (message, { desc }) => desc(message.timestamp),
          limit: 1,
        },
      },
    })

db.query.UserTable.findMany({
      with: {
        sent: {
          where: (message, { eq }) => eq(message.id, 4),
          orderBy: (message, { desc }) => desc(message.timestamp),
          limit: 1,
        },
        received: {
          where: (message, { eq }) => eq(message.id, 4),
          orderBy: (message, { desc }) => desc(message.timestamp),
          limit: 1,
        },
      },
    })

Oh, can you even add order where you did? Could you try doing order: true and see if it still yells at you? I think it's potentially a schema/relations issue

mr_pablo•17mo ago

shouldnt

order: {
        where: (table, { eq }) => eq(table.date, tommorrow),
      },

order: {
        where: (table, { eq }) => eq(table.date, tommorrow),
      },

be

order: {
        where: (order, { eq }) => eq(order.date, tommorrow),
      },

order: {
        where: (order, { eq }) => eq(order.date, tommorrow),
      },

you need the correct table name

PiotrekOP•17mo ago

this works, and your example also works for me - sent and received are arrays and not single objects

mr_pablo•17mo ago

im filtering by a nested relation too, in 0.28.1

PiotrekOP•17mo ago

nah it also fails, it's just a function arg if the relation is an entity not entity[] it does not have filters i think

mr_pablo•17mo ago

i think the notes are on about the top level where using a related table column maybe?

Asseater Peter•17mo ago

My package.json:

"dependencies": {
    "@planetscale/database": "^1.10.0",
    "@t3-oss/env-core": "^0.6.0",
    "dotenv": "^16.3.1",
    "drizzle-orm": "^0.28.1",
    "mysql2": "^3.6.0",
    "zod": "^3.21.4"
  }

"dependencies": {
    "@planetscale/database": "^1.10.0",
    "@t3-oss/env-core": "^0.6.0",
    "dotenv": "^16.3.1",
    "drizzle-orm": "^0.28.1",
    "mysql2": "^3.6.0",
    "zod": "^3.21.4"
  }

So, are you sure you're using a one-to-many?

PiotrekOP•17mo ago

yep i'm sure, it works without the filtering as expected

Asseater Peter•17mo ago

So what's this about single objects, I'm kinda confused When you use order: true, does it return an object or an array of objects? Because if it's an array it shouldn't be a problem... Could you maybe show me the relation setup?

PiotrekOP•17mo ago

an object and that's the problem cause it looks like you can filter by arrays but not objects

Asseater Peter•17mo ago

Yeah Because a "where" goes over shit, and when you only have one object there's not much to go over. Just add a line checking it after you get everything from the db\

PiotrekOP•17mo ago

like technically it works, i have it like that, but it means i basically have to download much more stuff from the db than i would normally do even on my test dev db i'm getting 20 entities instead of4 for now it's not THAT important but would be nice to have some sort of filtering

Asseater Peter•17mo ago

Wait what, it seems like order is its own thing no? Like, products and shit aren't in order so it shouldn't matter that much

PiotrekOP•17mo ago

i have orders and suborders, basically meaning that one suborder - one type of product order is a group of suborders for a selected day and i want to get only suborders that would be part of an order for JUST the next day so yeah, it means a lot actually actually i can do it in a really stupid way thinking about it

Asseater Peter•17mo ago

So I guess what I'm ordering is why this would be structured this way? You could 100% Just query for orders where the date is the next day and then just go from there

PiotrekOP•17mo ago

i can make a query to orders table instead and merge the array afterwards

Asseater Peter•17mo ago

Like, why are you querying from suborders when you could query from orders

PiotrekOP•17mo ago

yeah just realised lmao

Asseater Peter•17mo ago

Yeah, that other way is just... wrong

PiotrekOP•17mo ago

i just need suborders as the result but i can do it in code

Asseater Peter•17mo ago

Well, that's a different issue Also, the initial way would mean if you had 2 orders for the next day you'd only get 1 at most.... Like, there's a good reason why where doesn't work from the one side. There's just always a better way...

PiotrekOP•17mo ago

well i had it in prisma written exactly like that and it worked... maybe something weird under the hood tho not sure thanks for help though!

Asseater Peter•17mo ago

Definitely something weird under the hood! Since suborder.order could only refer to one order, in theory you should only be querying that one for the day Anytime!

PiotrekOP•17mo ago

This is my Prisma code if you're interested 😉

const suborders = await prisma.subOrder.findMany({
    where: {
      kitchenId: null,
      order: {
        date: tommorrow,
      },
    },
    select: {
      quantity: true,
      product: {
        select: {
          id: true,
          kitchens: {
            select: {
              id: true,
              performancePoints: true,
            },
          },
          kitchenDependentProperties: {
            select: {
              kitchenId: true,
              productionPrice: true,
              purchasePrice: true,
            },
          },
          performancePoints: true,
          prepTime: true,
        },
      },
    },
  });

const suborders = await prisma.subOrder.findMany({
    where: {
      kitchenId: null,
      order: {
        date: tommorrow,
      },
    },
    select: {
      quantity: true,
      product: {
        select: {
          id: true,
          kitchens: {
            select: {
              id: true,
              performancePoints: true,
            },
          },
          kitchenDependentProperties: {
            select: {
              kitchenId: true,
              productionPrice: true,
              purchasePrice: true,
            },
          },
          performancePoints: true,
          prepTime: true,
        },
      },
    },
  });

Mendy•17mo ago

@yuval59 there’s something off with your setup. Drizzle has indeed deprecated this feature. You’re welcome to see for yourself: https://codesandbox.io/p/sandbox/beautiful-tharp-8z4s98?file=%2Flib%2Fdrizzle.ts%3A14%2C4

beautiful-tharp-8z4s98

CodeSandbox brings instant cloud development environments that keep you in flow.

Mendy•17mo ago

.

mr_pablo•17mo ago

@mendy.l odd, i am able to do this in 0.28.1

db.query.competitions.findMany({
  with: {
    matches: {
      where: between(
        schema.matches.kickOffUTC,
        placeholder('startOfToday'),
        placeholder('endOfToday'),
      ),
      orderBy: asc(schema.matches.kickOffUTC),
      limit: 1,
    },
  },
})

db.query.competitions.findMany({
  with: {
    matches: {
      where: between(
        schema.matches.kickOffUTC,
        placeholder('startOfToday'),
        placeholder('endOfToday'),
      ),
      orderBy: asc(schema.matches.kickOffUTC),
      limit: 1,
    },
  },
})

this 100% works with mysql that is The change states this code won't work

const usersWithPosts = await db.query.users.findMany({
  where: (table, { sql }) => (sql`json_array_length(${table.posts}) > 0`),
  with: {
    posts: true,
  },
});

const usersWithPosts = await db.query.users.findMany({
  where: (table, { sql }) => (sql`json_array_length(${table.posts}) > 0`),
  with: {
    posts: true,
  },
});

its the top level "where" using a related table column value that shouldn't work, nothing about "where" clauses in the nested table joins

Drizzle Team Join

The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!

9.8KMembers

View on Discord

Want results from more Discord servers?

Add your server