absent-sapphire

Are `multiple joins` same as SQL multiple joins ?

I have an issue with multiple joins, I need some joins between a main table and other 4 tables but it seems that the output on TanStack DB is different from the same query on SQL. These are my collection and SQL query: TanStack DB Collection

loader: async ({ params }) => {
    const { id } = params;
    // main table
    const player = createPlayers(id);
    // other tables
    const qualification = createResults(id, "qualification");
    const institute = createResults(id, "institute");
    const area = createResults(id, "area");
    const national = createResults(id, "national");
    // live collection with joins
    const collection = createLiveQueryCollection((q) => {
      return q
        .from({ p: player })
        .leftJoin({ qualification }, ({ p, qualification }) => eq(p.id, qualification.id))
        .leftJoin({ institute }, ({ p, institute }) => eq(p.id, institute.id))
        .leftJoin({ area }, ({ p, area }) => eq(p.id, area.id))
        .leftJoin({ national }, ({ p, national }) => eq(p.id, national.id))
        .select(({ p, qualification, institute, area, national }) => ({
          id: p.id,
          category: p.category,
          qualification,
          institute,
          area,
          national,
        }));
    });

loader: async ({ params }) => {
    const { id } = params;
    // main table
    const player = createPlayers(id);
    // other tables
    const qualification = createResults(id, "qualification");
    const institute = createResults(id, "institute");
    const area = createResults(id, "area");
    const national = createResults(id, "national");
    // live collection with joins
    const collection = createLiveQueryCollection((q) => {
      return q
        .from({ p: player })
        .leftJoin({ qualification }, ({ p, qualification }) => eq(p.id, qualification.id))
        .leftJoin({ institute }, ({ p, institute }) => eq(p.id, institute.id))
        .leftJoin({ area }, ({ p, area }) => eq(p.id, area.id))
        .leftJoin({ national }, ({ p, national }) => eq(p.id, national.id))
        .select(({ p, qualification, institute, area, national }) => ({
          id: p.id,
          category: p.category,
          qualification,
          institute,
          area,
          national,
        }));
    });

SQL Query

SELECT
  player.id AS player_id,
  player.category AS player_category,
  "result-qualification".score AS qualification_score,
  "result-area".score AS area_score,
  "result-national".score AS national_score  
FROM player
LEFT JOIN "result-qualification" ON player.id = "result-qualification".id
LEFT JOIN "result-institute" ON player.id = "result-institute".id
LEFT JOIN "result-area" ON player.id = "result-area".id
LEFT JOIN "result-national" ON player.id = "result-national".id

SELECT
  player.id AS player_id,
  player.category AS player_category,
  "result-qualification".score AS qualification_score,
  "result-area".score AS area_score,
  "result-national".score AS national_score  
FROM player
LEFT JOIN "result-qualification" ON player.id = "result-qualification".id
LEFT JOIN "result-institute" ON player.id = "result-institute".id
LEFT JOIN "result-area" ON player.id = "result-area".id
LEFT JOIN "result-national" ON player.id = "result-national".id

I obtain from SQL 67 rows and 138 from the collection, where each row is duplicated a variable number of times (with missing part of relation). How do multiple joins really work here ?

7 Replies

compatible-crimson•3d ago

This does indeed look wrong - joins are intended to follow sql semantics. Could you file an issues with the schemas and query. We'll look into it asap.

absent-sapphireOP•3d ago

ok now I create a sample, and open an issue

compatible-crimson•3d ago

Thanks, can you confirm you would expect at most 1 row from the joined collections, and so there should only be 1 row out for every row in form the player collection?

absent-sapphireOP•3d ago

yep, also uniqueBy(results, row => row.id) gives the same number of elements of the SQL query

compatible-crimson•3d ago

Ok, thanks. We'll look at this as a priority in the next few days.

absent-sapphireOP•3d ago

GitHub

Multiple joins not follow SQL semantics · Issue #438 · TanStack/db

compatible-crimson•14h ago

We've tested with an older version of db from before we made a spesific change, and the results match SQLite. This means it's a regression that we had seen evidence of, but didn't have a perfect reproduction of. We are working on it right now and will have an update asap.

Gaming

Programming

Are `multiple joins` same as SQL multiple joins ?

Did you find this page helpful?