Multiple Joins "Invalid join condition"

[lautarodapin]

Stackblitz

https://stackblitz.com/edit/vitejs-vite-gnkg2sdh?file=src%2FApp.tsx

I'm having this issue when i'm trying to
This are my collections nothing fancy

    const playersCollection = createCollection(
        queryCollectionOptions({
            queryKey: ["jugadores", clubId],
            queryFn: async () => {
                const results = await call.get<FrappeResponse<Jugador[], { count: number }>>(
                    "setplus.setplus.doctype.jugador.jugador.get_filtered_jugadores",
                    {
                        club: clubId,

                        page_size: 9999999,
                        page: 0,
                    },
                );
                return results.message.data ?? [];
            },
            getKey: (item) => item.name,
            queryClient,
        }),
    );
    const clientsCollection = createCollection(
        queryCollectionOptions({
            queryClient,
            queryKey: ["clientes", clubId],
            queryFn: async () => {
                return await db.getDocList<Cliente>("Cliente", {
                    filters: [["club", "=", clubId]],
                    fields: ["*"],
                    limit: 9999999,
                });
            },
            getKey: (item) => item.name,
        }),
    );
    const balancesCollection = createCollection(
        queryCollectionOptions({
            queryClient,
            queryKey: ["balances", clubId],
            queryFn: async () => {
                return await db.getDocList<BalanceClientePorClub>("Balance Cliente por Club", {
                    filters: [["club", "=", clubId]],
                    fields: ["*"],
                    limit: 9999999,
                });
            },
            getKey: (item) => item.name,
        }),
    );

    const { data } = useLiveQuery((q) =>
        q
            .from({ player: playersCollection })
            .join({ client: clientsCollection }, ({ client, player }) => eq(client.player, player.name))
            .join({ balance: balancesCollection }, ({ balance, client }) => eq(balance.client, client.name))
            .select(({ player, client, balance }) => ({
                player,
                client,
                balance,
            })),
    );

[devjmetivier]

Having a similar issue myself. Seems to be the case that any join that's nested a layer deeper than 1 fails with this invalid join condition:

Z relates to Y relates to X <= This level (X) of joining and beyond fails.

[kevin-dp]

This should indeed be valid but it is a known bug in the query builder (cc @samwillis) which is overly conservative right now. For now, you can work around this bug by moving the collection that is common to all joins (clientsCollection in your case) into the base collection (i.e. the from):

q
  .from({ client: clientsCollection })
  .join({ player: playersCollection }, ({ client, player }) =>
    eq(player.name, client.jugador)
  )
  .join({ balance: balancesCollection }, ({ balance, client }) =>
    eq(client.name, balance.client)
  )
  .select(({ player, client }) => ({ player, client }))

Sorry for the late answer.