Drizzle TeamDT
Drizzle Team•3y ago
hachoter

I need some guidance on joins

Hi I am using joins because it makes the most sense in my application, I was gonna try out the relations api but I want to work on my sql skills as well, so I have this schema and I am trying to understand how to get all teh tables returned in a single query
here is the schema
export const items = pgTable('items', {
  id: uuid('id').primaryKey()
});

export const productOptions = pgTable('product_options', {
  id: uuid('id').primaryKey(),
  productId: uuid('product_id').references(() => items.id)
});

export const productOptionChoices = pgTable('product_option_choices', {
  id: uuid('id').primaryKey(),
  productOptionId: uuid('product_option_id').references(() => productOptions.id)
});

I stripped all the extra columns so there might be a syntax error in there, sorry in advance
What I am trying to get is something like this
{
  id:'id',
  productoptions:[
    {
      id:'id',
      productoptionChoices:[
          {id:'id'}
      ]
    }
  ]
}

And of course all the other fields
So far I have this query which at least in typescript looks ok (my db is not set up so I can't test at the moment) but I don't know how to get the nested tables
client
    .select()
    .from(schema.items)
    .leftJoin(schema.productOptions, eq(schema.productOptions.productId, schema.items.id))

I probably need another left join but where do I put that?
Was this page helpful?