21 replies

Selecting multiple relating rows

A more generic SQL question (Been out of SQL a while now

)

Given the following schema:

import { index, int, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core/index.js';

export const systems = mysqlTable('systems', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

export const stars = mysqlTable(
  'stars',
  {
    id: serial('id').primaryKey(),
    system_id: int('system_id').notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  table => ({
    systemIdIdx: index('system_id_idx').on(table.system_id),
  }),
);

import { index, int, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core/index.js';

export const systems = mysqlTable('systems', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

export const stars = mysqlTable(
  'stars',
  {
    id: serial('id').primaryKey(),
    system_id: int('system_id').notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  table => ({
    systemIdIdx: index('system_id_idx').on(table.system_id),
  }),
);

What would be the most efficient method of fetching all systems and all of their relating stars? In SQL (Not sure how), or by just fetching all rows from both tables separately and stitching them together afterwards, given all rows will be fetched regardless?

Drizzle Team•3y ago•

21 replies

devlsh

Selecting multiple relating rows

A more generic SQL question (Been out of SQL a while now

)

Given the following schema:

import { index, int, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core/index.js';

export const systems = mysqlTable('systems', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

export const stars = mysqlTable(
  'stars',
  {
    id: serial('id').primaryKey(),
    system_id: int('system_id').notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  table => ({
    systemIdIdx: index('system_id_idx').on(table.system_id),
  }),
);

import { index, int, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core/index.js';

export const systems = mysqlTable('systems', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

export const stars = mysqlTable(
  'stars',
  {
    id: serial('id').primaryKey(),
    system_id: int('system_id').notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  table => ({
    systemIdIdx: index('system_id_idx').on(table.system_id),
  }),
);

Selecting multiple relating rows

Similar Threads

Selecting multiple relating rows

Similar Threads

Similar Threads

Similar Threads