Function createJoinSubqueryTests

packages/db/tests/query/join-subquery.test.ts:203–871

(autoIndex: `off` | `eager`)

}

function createJoinSubqueryTests(autoIndex: `off` | `eager`): void {
  describe(`with autoIndex ${autoIndex}`, () => {
    describe(`subqueries in FROM clause with joins`, () => {
      let issuesCollection: ReturnType<typeof createIssuesCollection>
      let usersCollection: ReturnType<typeof createUsersCollection>

      beforeEach(() => {
        issuesCollection = createIssuesCollection(autoIndex)
        usersCollection = createUsersCollection(autoIndex)
      })

      test(`should join subquery with collection - inner join`, () => {
        const joinQuery = createLiveQueryCollection({
          startSync: true,
          query: (q) => {
            // Subquery: filter issues by project 1
            const project1Issues = q
              .from({ issue: issuesCollection })
              .where(({ issue }) => eq(issue.projectId, 1))

            // Join subquery with users
            return q
              .from({ issue: project1Issues })
              .join(
                { user: usersCollection },
                ({ issue, user }) => eq(issue.userId, user.id),
                `inner`,
              )
              .select(({ issue, user }) => ({
                issue_title: issue.title,
                user_name: user.name,
                issue_duration: issue.duration,
                user_status: user.status,
              }))
          },
        })

        const results = joinQuery.toArray
        expect(results).toHaveLength(3) // Issues 1, 2, 3 from project 1 with users

        const resultTitles = results.map((r) => r.issue_title).sort()
        expect(resultTitles).toEqual([`Bug 1`, `Bug 2`, `Feature 1`])

        const alice = results.find((r) => r.user_name === `Alice`)
        expect(alice).toMatchObject({
          user_name: `Alice`,
          user_status: `active`,
        })
      })

      test(`should join collection with subquery - left join`, () => {
        const joinQuery = createLiveQueryCollection({
          startSync: true,
          query: (q) => {
            // Subquery: filter active users
            const activeUsers = q
              .from({ user: usersCollection })
              .where(({ user }) => eq(user.status, `active`))