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?

15 Replies

Andrii Sherman•2y ago

You can easily do it with SQL. Just use join so something like

select * from systems
left join stars on stars.system_id = systems.id

select * from systems
left join stars on stars.system_id = systems.id

in this case you'll get a list of rows with system+star for this system And then aggregate this result. We have a small example for aggregation, but you are free to do any you want https://github.com/drizzle-team/drizzle-orm/blob/main/docs/joins.md#aggregating-results In this case you are having only 1 database request and then just js mapping If you have a lot of data in database, I would suggest to paginate through data you have. I mean if you care about memory and have like 100k systems In this case you can do same select, but also adding .where for systems

select * from systems
left join stars on stars.system_id = systems.id
where systems.id >= 0 and systems.id < 100

-- then next 100(or 1000, or any number you want)

select * from systems
left join stars on stars.system_id = systems.id
where systems.id >= 100 and systems.id < 200

-- and so on until response won't be empty

select * from systems
left join stars on stars.system_id = systems.id
where systems.id >= 0 and systems.id < 100

-- then next 100(or 1000, or any number you want)

select * from systems
left join stars on stars.system_id = systems.id
where systems.id >= 100 and systems.id < 200

-- and so on until response won't be empty

Not sure if it's the best practice, but should work

OYΞDOP•2y ago

Ah, ok, I was using a leftJoin already, but it was coming back as an object with the row instead of an array - if there was only a single row, would it negate the array and just return that one row?

Andrii Sherman•2y ago

Drizzle return same array of rows, but those rows are mapped to objects so you should get an array with system+star object in each element of array

[{systems:{}, stars:{}}, {systems:{}, stars:{}}]

[{systems:{}, stars:{}}, {systems:{}, stars:{}}]

and then you can use aggregation example I sent you, so it will map result to systems + array of stars in a system

OYΞDOP•2y ago

Awesome, that tripped me up haha - thanks dude RE: pagination, it's like ~50k rows I'm background caching, since they have to all be loaded to render a WebGL Galaxy Map in the browser 😅

Andrii Sherman•2y ago

how many stars per system? approximately?

OYΞDOP•2y ago

Approximately 1 per system, but that's not a rule (star systems can have two or more stars)

Andrii Sherman•2y ago

oh, then 50k is fine I mean also with cache it's just you'll wait for 50-100k rows of data for 1 roundtrip but all depends

OYΞDOP•2y ago

Yeah caching should be fine, just didn't want to be dumb about how I was going about filling the cache I think it's best I just separate to at least fetch systems and stars separately and then aggregate like you said - definitely better than a single roundtrip

OYΞDOP•2y ago

Works great! Thanks again @Andrii Sherman

Unknown User•2y ago

Message Not Public

OYΞDOP•2y ago

The schema I gave in the initial post isn't the full schema, part of the galaxy generation I wrote involves a post-process of figuring out connections between systems in 2D space, they're stored in the DB as json('connections').$type<string[]>().notNull().default([]) (Galaxy generation is somewhat accurate; types of stars, planets/moons/asteroids in those systems, resources on those orbitals themselves)

rphlmr ⚡•2y ago

https://tenor.com/view/gaters-gonne-gate-stargate-sg1-gif-9470007

Tenor

rphlmr ⚡•2y ago

that's really impressive 😲

OYΞDOP•2y ago

Thanks! It's a web-based game, galaxy generation works pretty well 🙂

Dan•2y ago

This looks very cool! You def should post it to #showcase once you're ready 🙂

Gaming

Programming

Selecting multiple relating rows