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
No description
17 Replies
Angelelz
Angelelz15mo ago
This is the perfect use case for the relational query API
Hugo
HugoOP15mo 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
Angelelz15mo 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
Hugo
HugoOP15mo 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
Hugo
HugoOP15mo ago
by the way, this is my connection file, how should i pass my schemas into the connection while there is multiple schemas files
No description
Angelelz
Angelelz15mo ago
Well, either that or just aggregate the results to the shape you'd like it to be.
Angelelz
Angelelz15mo ago
Hugo
HugoOP15mo ago
well than you for this method !
Angelelz
Angelelz15mo 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
Hugo
HugoOP15mo ago
like that ?
Hugo
HugoOP15mo ago
No description
Hugo
HugoOP15mo ago
and the i can import * from index.ts
Angelelz
Angelelz15mo ago
That's one way, yep There is also an shorter syntax: `export {carts} from "./carts";
Hugo
HugoOP15mo ago
damn, okay, doing like this made me an error,
No description
Hugo
HugoOP15mo ago
sorry it's in french so i will stay with your first tips hie ! do you know why it still give me this error ?
Want results from more Discord servers?
Add your server