Drizzle Team•2y ago

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)
});

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'}
      ]
    }
  ]
}

{
  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))

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?

5 Replies

hachoterOP•2y ago

on a side note I see that the results are not gonna be in the shape that I want it, it's an internal tool so minor inconvenience but how could I change that? preferably without a performance hit, I don't want to loop over potentially thousands of rows within js

Lautaro_dapin•2y ago

no way of doinng that, thats how sql works, its just a row, youu will have to do a reduce or map after that

Lautaro_dapin•2y ago

and this is new https://orm.drizzle.team/docs/rqb#querying maybe that helps

Relational queries – DrizzleORM

Drizzle ORM | %s

hachoterOP•2y ago

Does this add additional queries?

Lautaro_dapin•2y ago

the doc doesn't specified that, but it seems that is oonly a layer on top of the oooriginal lib, so it should you could add a logger on your db and see it for yourself

Gaming

Programming

I need some guidance on joins