Drizzle Team•2y ago

Left join with JSON

I'm using PlanetScale (MySQL) and have 2 tables: orders and products. Every order contains basic info about the ordered products (see screenshot). Is there a way to do this?:

import { orders, products } from '$lib/server/db/schemas/products';

db
    .select()
    .from(orders)
    .leftJoin(products, inArray(products.id, /*orders.products.map(({ id }) => id)*/)),

import { orders, products } from '$lib/server/db/schemas/products';

db
    .select()
    .from(orders)
    .leftJoin(products, inArray(products.id, /*orders.products.map(({ id }) => id)*/)),

I would like it take the ids from the orders.products array and select those products.

4 Replies

Dan•2y ago

Can you build this query in raw SQL?

makaron pelnoziarnistyOP•2y ago

No, not really, i'm a beginner. I tried searching for this but I don't know how to put it to words. Chat gpt tried helping and came up with this. Is this any accurate? I need at least a phrase which I can google to learn this.

Dan•2y ago

Uhh, honestly I'm not sure either, I'm not very good with JSON functions You might read about using the JSON functions in your dialect in general, maybe you'll find some references

makaron pelnoziarnistyOP•2y ago

Okay thank you

Gaming

Programming

Left join with JSON