Json object aggregate
In a multilingual app, I am using relational queries (but I could also just work with normal
I'm looking for a query that would return the data formatted as:
but I'm struggling to get anything else than:
selectselectexport const projects = pgTable('projects', {
id: nanoid('id').default(generateNanoid()).primaryKey(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
publishedAt: timestamp('published_at', { withTimezone: true }),
typeId: integer('type_id').references(() => projectTypes.id, {
onDelete: 'set null',
onUpdate: 'cascade',
}),
// ...
});
type Project = InferSelectModel<typeof projects>;
export const projectsTranslations = pgTable('projects_t',
{
id: nanoid('id').references(() => projects.id, {
onDelete: 'cascade',
onUpdate: 'cascade',
}).notNull(),
locale: locale('locale').references(() => locales.locale, {
onDelete: 'cascade',
onUpdate: 'cascade',
}).notNull(),
title: text('title').notNull(),
summary: text('summary'),
description: text('description'),
},
(table) => {
return {
pk: primaryKey(table.id, table.locale),
unq: unique().on(table.locale, table.title),
};
}
);
type ProjectTranslation = InferSelectModel<typeof projectsTranslations>;export const projects = pgTable('projects', {
id: nanoid('id').default(generateNanoid()).primaryKey(),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
publishedAt: timestamp('published_at', { withTimezone: true }),
typeId: integer('type_id').references(() => projectTypes.id, {
onDelete: 'set null',
onUpdate: 'cascade',
}),
// ...
});
type Project = InferSelectModel<typeof projects>;
export const projectsTranslations = pgTable('projects_t',
{
id: nanoid('id').references(() => projects.id, {
onDelete: 'cascade',
onUpdate: 'cascade',
}).notNull(),
locale: locale('locale').references(() => locales.locale, {
onDelete: 'cascade',
onUpdate: 'cascade',
}).notNull(),
title: text('title').notNull(),
summary: text('summary'),
description: text('description'),
},
(table) => {
return {
pk: primaryKey(table.id, table.locale),
unq: unique().on(table.locale, table.title),
};
}
);
type ProjectTranslation = InferSelectModel<typeof projectsTranslations>;I'm looking for a query that would return the data formatted as:
const selectedProjects: (Project & {translations: Record<Locale, Translation | undefined>})[] = ...const selectedProjects: (Project & {translations: Record<Locale, Translation | undefined>})[] = ...but I'm struggling to get anything else than:
const selectedProjecets: (Project & {translations: Translation[]})[] = ...const selectedProjecets: (Project & {translations: Translation[]})[] = ...