Drizzle Team•14mo ago

Select Entry in db with relational tables

Hi, i'm using this request to get a store corresponding to the cuid, an di want to get also all his related categories from store_categories tabe

    const result = await db.select()
    .from(stores)
    .where(eq(stores.cuid_store, cuid_store))
    .innerJoin(stores_categories, eq(stores.id_store, stores_categories.id_store))

    const result = await db.select()
    .from(stores)
    .where(eq(stores.cuid_store, cuid_store))
    .innerJoin(stores_categories, eq(stores.id_store, stores_categories.id_store))

but it returns multiple times the store object for eact category. i would like to have instead

stores:{
      id_store: 11,
      cuid_store: 'rjkghtj06ajbai7',
      id_currency: 1,
      name: 'OobaOoba',
      baseline: 'Les deserts du futur',
      realtime_position: null,
      last_position_updated: 2023-09-21T13:54:26.000Z,
      profile_picture: 'public/upload/stores/rjkghtj06ajbai7/OobaOoba_cover.jpg',
      featured_label: 'Populaire',
      siret: '81359940400023',
      ape: '5610C',
      wrapping_enabled: false,
      wrapping_price: '0',
      cutleries_enabled: false,
      cutleries_price: '0',
      date_add: null,
      date_updated: null
},
stores_categories[6,12,4]

stores:{
      id_store: 11,
      cuid_store: 'rjkghtj06ajbai7',
      id_currency: 1,
      name: 'OobaOoba',
      baseline: 'Les deserts du futur',
      realtime_position: null,
      last_position_updated: 2023-09-21T13:54:26.000Z,
      profile_picture: 'public/upload/stores/rjkghtj06ajbai7/OobaOoba_cover.jpg',
      featured_label: 'Populaire',
      siret: '81359940400023',
      ape: '5610C',
      wrapping_enabled: false,
      wrapping_price: '0',
      cutleries_enabled: false,
      cutleries_price: '0',
      date_add: null,
      date_updated: null
},
stores_categories[6,12,4]

where stores_categories are the categrories ids

17 Replies

Angelelz•14mo ago

This is the perfect use case for the relational query API

Angelelz•14mo ago

https://orm.drizzle.team/docs/rqb

Drizzle Queries - DrizzleORM

Drizzle ORM | %s

HugoOP•14mo ago

hi, i tried like that but should i create a new db connection in my server component where i already have a db connection on a db/index.js because when i tried this, i got errors

Angelelz•14mo ago

No need for a new connection. If you get errors when attempting to use the relational api, it's most likely because you're not passing the schema to the DB object, or not passing it properly Just follow the instructions in the docs

HugoOP•14mo ago

i try to follow the docs, but if i have my db connection withour passing schema in it inside a index.ts file for main db connection, i have to create a new one inside this file where i whant to get this formated data

HugoOP•14mo ago

by the way, this is my connection file, how should i pass my schemas into the connection while there is multiple schemas files

Angelelz•14mo ago

Well, either that or just aggregate the results to the shape you'd like it to be.

Angelelz•14mo ago

There is an example in the docs: https://orm.drizzle.team/docs/joins#aggregating-results

Joins [SQL] - DrizzleORM

Drizzle ORM | %s

HugoOP•14mo ago

well than you for this method !

Angelelz•14mo ago

two options, either import them each and create a schema object with all the tables and relations from all your schemas, or create an index.ts in the folder where all your schemas are and export from there all your schemas

Angelelz•14mo ago

See here: https://orm.drizzle.team/docs/joins#aggregating-results

Joins [SQL] - DrizzleORM

Drizzle ORM | %s

HugoOP•14mo ago

like that ?

HugoOP•14mo ago