Nested and related data?

[cubecull]

Sorry to open this as an issue but I noticed you've previously said you don't get notifications for Discussions.

Has there been any further movement on supporting one->many queries? Or a pattern I should be following?

_Originally posted in https://github.com/tinyplex/tinybase/discussions/18

[jamesgpearce]

Do you mean one-to-many (which hopefully can be modeled via the relationships or queries module) or many-to-many?

[cubecull]

I do indeed mean 1->many, so it sounds like I've missed an example or I'm misunderstanding something. Specifically I'm asking about how to use https://tinybase.org/api/queries/type-aliases/definition/join/ to achieve one->many queries.

I tried following the Movie Database demo as the closest example, but of course here the relationship is a many->many.

My workaround currently is to store the rowId of the source table row in the matching row of the target table and then execute two queries and join the data together manually. Not sure if this is the intended solution but it feels sub-optimal.

store.setTablesSchema({
  packets: {
    id: { type: 'string' },
    name: { type: 'string' },
  },
  byteRanges: {
    id: { type: 'string' },
    packetId: { type: 'string' },
    name: { type: 'string' },
  },
});

queries.setQueryDefinition('viewingPacket', 'packets', ({ select, where }) => {
  select('id');
  select('name');
  where('id', packetId);
});

queries.setQueryDefinition('viewingPacketByteRanges', 'byteRanges', ({ select, where }) => {
  select('id');
  select('packetId');
  select('name');
  where('packetId', packetId);
});

[jamesgpearce]

Ok so I see the challenge here: you are using a cell called 'Id' rather than the native rowId (which is used in join conditions).

It's not possible to create a join based on columns in both tables. Instead you need to create an expression based on columns in one table (the 'root' table) that identifies the rowId to join to.

Is it possible for you to use id also as the rowId?

[jamesgpearce]

...Not to say your requirement isn't a valid one - just seeing if that helps you out for now.