Allow joins with an `and()` condition

[duailibe]

Trying to join collections using a condition with an and() is not supported right now.

Reproduction

Adding packages/db/tests/query/join.test.ts:

type Product = {
  productId: number;
  region: 'A' | 'B' | 'C';
  sku: string;
  title: string;
}

type ProductInventory = {
  inventoryId: number;
  region: 'A' | 'B' | 'C';
  sku: string;
  quantity: number;
};

test('join with and', () => {
  const productsCollection = createCollection(
    mockSyncCollectionOptions<Product>({
      id: `test-products-join`,
      getKey: (product) => product.productId,
      initialData: [
        { productId: 1, region: 'A', sku: 'sku1', title: 'A1' },
        { productId: 2, region: 'B', sku: 'sku1', title: 'B1' },
        { productId: 3, region: 'C', sku: 'sku2', title: 'C2' },
        { productId: 4, region: 'A', sku: 'sku2', title: 'A2' },
      ],
    })
  );
  const inventoriesCollection = createCollection(
    mockSyncCollectionOptions<ProductInventory>({
      id: `test-inventories-join`,
      getKey: (inventory) => inventory.inventoryId,
      initialData: [
        { inventoryId: 1, region: 'A', sku: 'sku1', quantity: 10 },
        { inventoryId: 3, region: 'C', sku: 'sku2', quantity: 30 },
      ],
    })
  );

  const joinQuery = createLiveQueryCollection({
    startSync: true,
    query: (q) =>
      q
        .from({ product: productsCollection })
        .join(
          { inventory: inventoriesCollection },
          ({ product, inventory }) =>
            and(
              eq(product.region, product.sku),
              eq(inventory.region, inventory.sku)
            ),
        )
  });

  expect(joinQuery.toArray).toHaveLength(2);
});

And it fails with:

QueryBuilderError: Join condition must be an equality expression
 ❯ BaseQueryBuilder.join src/query/builder/index.ts:277:3
    275|   > {
    276|     return this.join(source, onCallback, `inner`)
    277|   }
       |   ^
    278|
    279|   /**
 ❯ query tests/query/join.test.ts:1459:10
 ❯ buildQuery src/query/builder/index.ts:3822:45
 ❯ buildQueryFromConfig src/query/live/collection-config-builder.ts:3737:48
 ❯ new CollectionConfigBuilder src/query/live/collection-config-builder.ts:139:9
 ❯ liveQueryCollectionOptions src/query/live-query-collection.ts:269:60
 ❯ createLiveQueryCollection src/query/live-query-collection.ts:287:46

[samwillis]

I think this could be achieved now by joining on the first, then having a where clause filter out the incorrect matches:

  const joinQuery = createLiveQueryCollection({
    startSync: true,
    query: (q) =>
      q
        .from({ product: productsCollection })
        .join(
          { inventory: inventoriesCollection },
          ({ product, inventory }) =>
            eq(product.sku, inventory.sku),
        )
        .where(({ product, inventory }) =>
          eq(product.region, inventory.region)
        )
  });

Two possible routes to supporting the and in the join contain in future:

1. Automatically rewrite the query to join on the first condition, and move the other to a where clause - essentially re-writing to match that above.

2. Construct a composite key for the join: "${hash(product.sku):${hash(product.region)}"

At first glance would seem more efficient, but I suspect that constructing the composite keys and hashing has a higher overhead than the rewrite to a where clause.

Not mentioned is or, that is far more complex to support... it likely maps to parallel joins.

[duailibe]

@samwillis Yes, I forgot to mention but I'm using the first approach ATM, that was just something I've gotten used to do with SQL and tried here

Doing it automatically would be smart indeed