Nested and related data?

[ianwalter]

I am having trouble figuring out how to store and retrieve nested data with TinyBase, for example:

[{
  "id": "ghi",
  "title": "Hello World",
  "tags": [
    { "id": "abc", "label": "Test" },
    { "id": "def", "label": "Example" }
  ]
}]

I thought of using the Relationships API, but that seems to only be one-to-one and not one-to-many?

[jamesgpearce]

@ianwalter sorry for the delay! Somehow I wasn't getting notifications for GitHub discussions.

Yes, this is true - relationships are one-one or many-one, and so currently for a tagging system you would need to do something pretty grungy like a JSON-encoded array or imperatively work your way through a many-many join table. Not ideal.

I'm making decent progress on a more expressive query engine (in a personal fork at https://github.com/jamesgpearce/tinybase/tree/v1.4.0) that allows for responsive views that straddle multiple tables. It's not quite ready to be used - give me a week? - and so far it supports star-like joins, grouping etc. eg:

queries.setQueryDefinition('query1', 'table1', ({select, join}) => {
  select('cell1').as('t1.c1');
  select('table2', 'cell1').as('t2.c1');
  join('table2', 'cell3');
});

I feel like I should keep your many-many use case in mind. It's a very good one - though I want to make sure there aren't too many opportunities for cartesian explosions! - let me see what I can do.

[ianwalter]

Hey no problem. I ended up JSON-encoding like you suggest and it has been working fine. I'll remember to check back occasionally and try out any new or proposed changes. Thank you for your response!

[jamesgpearce]

One other idea is to actually allow arrays of string/number/boolean values to be valid in cells, though that's got some deeper API implications (like allowing push and pop as well as set and delete etc). As you can tell the wheels of my dusty old brain are turning.

[dave-irvine]

Hi @jamesgpearce , has this type of thing ever been resolved? I see you're well beyond the 1.4.0 tag you mentioned, but I still can't find a way to make a one-to-many query work? I tried checking the Movie Database demo, with the Movie->Cast relationship being a one-to-many, but this seems to only work under the fixed "3 cast members" limit. Is there any way I can achieve a true one-to-many join?

[jamesgpearce]

Ouch, how did I missed the question for months. Sorry @dave-irvine! I still think we need this, but it's going to be a big & hard thing to pull off (probably with some breaking changes since it will need an array type in cells and the current typing assumptions are pretty deep). One thing you have inspired me to do is publish a roadmap discussion though, and this will go on it.

[dave-irvine]

Hi @jamesgpearce, popping back in to see if you had any luck adding this? I can see you've made lots of progress with Tinybase as a whole, but this was the main thing blocking me.