Joins with a hierarchical projection (includes)

[samwillis]

The data shown in a user interface if often hierarchical (projects -> issues -> comments)

The joins supported by Tanstack DB are SQL like joins where the resulting rows are flat, however users will often what to project to a hierarchical structure to render in the UI.

The proposed syntax is to use subqueries in the select clause:

const projects = new Query
  .from({ project: projectsCllctn })
  .where(....)
  .select(({ project }) => ({
    project,
    issues: new Query
      .from({ issue: issuesCllctn })
      .where(({ issue }) => 
        eq(issue.projectId, project.id)
      )
      .select(({ issue }) => ({
        issue,
        comments: new Query
          .from({ comment: commentsCllctn })
          .where(({ comment }) =>
            eq(comment.issueId, issue.id)
          )
      })
   })

[eigilnikolajsen]

Any progress on this one?:)

[mrthinger]

also looking forward to this

[marbemac]

Agree, game changer - coming from Zero, one of the biggest pain points atm as we try to adopt tanstack db is the ergonomics around joining and working with the typical hierarchal data patterns present in most apps. Zero had that nailed.

[ouvreboite]

Aggregating relation data in a single query makes sense when fetching data remotely (you don’t want to have to do N network trips). But for a (mostly) local first framework, wouldn’t the idiomatic solution be to have each component query the data it need (ie a single entity + relationships ids)

So instead of having a single aggregated query

sequenceDiagram
    participant Project as "<Project projectId />"
    participant Issue as "<Issue issue />"
    participant Comment as "<Comment comment />"

    Project->>Project: useJoinedQuery(projectId) → project (with issues and comments)
     Project->>Issue: for each project.issues
     Issue->>Comment: for each issue.comments

Loading

We would have each component fetch the domain object it needs (mostly from a local cache so not more network trip involved)

sequenceDiagram
    participant Project as "<Project projectId />"
    participant Issue as "<Issue issueId />"
    participant Comment as "<Comment commentId />"

    Project->>Project: useQuery(projectId) → project (with issueIds)
    Project->>Issue: for each project.issueIds

    Issue->>Issue: useQuery(issueId) → issue (with commentIds)
    Issue->>Comment: for each issue.commentIds

    Comment->>Comment: useQuery(commentId) → comment

Loading

[mhsnook]

I love this, came here because I was looking for the same thing. Relatedly, if we could do something like the above, I'd also like to be able to make array columns out of the results, like:

import { count, avg, group_array } from '@tanstack/db'

const departmentStats = createCollection(liveQueryCollectionOptions({
  query: (q) =>
    q
      .from({ user: usersCollection })
      .joinMany({ issues: issuesCollection }, ({ user, issues }) => eq(user.id, issues.user_id))
      .groupBy(({ user }) => user.departmentId)
      .fn.select(({ user, issues }) => ({
        user,
        issueTopics: distinct(issues.map(i => i.topic)), // an array of issue topics
        issues, // the array of full objects
      }))
})

I can obviously derive this info in my component or in my custom hook after the useLiveQuery comes back with results and just memoise that transformation, but one of the great benefits with TanStack/db seems to be the ability to reshape and recombine data in ways that maintain both reactivity and composability with other operations also managed by the db library.

---

edit: TBF I would be fine doing what @ouvreboite suggests, but...

But for a (mostly) local first framework, wouldn’t the idiomatic solution be to have each component query the data it need (ie a single entity + relationships ids)

How are you accessing these relationship IDs? In most relational databases, for a one-to-many relationship, it's the many items that contain the foreign key to the one, not the other way around. So how are you accessing the IDs of the many?

[alexandretrotel]

Interesting about this too!

[evanheckert]

Exciting commentary about this here from @samwillis :

The intention with the includes is that each includes subquery becomes a ChildCollection prop on the parent row. When a child changes it won't trigger a change and rerender the parent. You pass the childCollection to a useLiveQuery itself to extract the data and gain the reactivity.
So for the vehicleDates subquery you would do useLiveQuery(vehicle.vehicleDates)
You will have minimal rerenders but with a single persisted query at the root.
We will also have an asArray operator that materialises the subquery to an array rather than a collection, triggering a change to the parent. This is useful if you have say a list of tags you want to render and don't want the overhead of having a child component to render them.
Internally the includes become a single branch in the query engine, it's not running the subquery for each time, it runs it once for all rows and fanning the results out to them.

This will HUGELY reduce friction moving away from graphql queries (and I presume more, but said friction is what I'm working around at the moment).

[Cluster444]

How are you accessing these relationship IDs? In most relational databases, for a one-to-many relationship, it's the many items that contain the foreign key to the one, not the other way around. So how are you accessing the IDs of the many?

I have done this in the parent component

SELECT id FROM children WHERE parent_id = ${parent_id}

Then a component that passes the child id through so the child can then

SELECT * FROM children WHERE id = ${child_id}

[HexiDev]

Was wondering if this will be implemented in the V1 release?

[MentalGear]

Second that, include should be in v1 release !

[v-anton]

I'm curious if this is already in the works, and if any help is needed