[FEATURE]: Ability to use conditional OR across joined tables in the query function

[bitaccesscomau]

Describe what you want

Using the query function, you are unable to have a conditional OR across related tables.

// API endpoint things here

const offsetParam = Number(url.searchParams.get('offset')) || 0;
const limitParam = Number(url.searchParams.get('limit')) || 10;
const idParam = url.searchParams.get('id');
const handleParam = url.searchParams.get('handle');
const scannedValueParam = url.searchParams.get('scannedValue');
	
	
const result = await db.query.product.findMany({
        where: (product, { eq, and }) => and(
            idParam ? eq(product.id, idParam) : undefined,
            handleParam ? eq(product.handle, handleParam) : undefined,
	    scannedValueParam ? eq(product.handle, scannedValueParam) : undefined
        ),
        offset: offsetParam,
        limit: limitParam,
        with: {
            tag: {
		where: (tag, { eq }) => scannedValueParam ? eq(tag.tagValue, scannedValueParam) : undefined,
                columns: {
                    productId: false
                }
            }
        },
        orderBy: product.created
    });

In the above example I can only return results where the scannedValue matches both the handle and the tag, but not either one. I cannot reference the related tag table in the upper or lower where clause as would be expected if performing the query in raw SQL. The SQL-like pseudocode equivalent of what I am trying to achieve is as per below.

SELECT * FROM product
LEFT OUTER JOIN tag on product.id = tag.productId
WHERE (product.handle = scannedValue OR tag.tagValue = scannedValue)

Ideally as the relation is specified, the upper where property should have access to the related tables.

[MorelSerge]

I find that in general, it is not possible to use any joined tables in the upper where clauses. It'll always replace the table name with the root table, creating invalid queries and erroring out.

My use case is rather simple:
Query a table (e.g. user) and join in a 1-1 table (e.g. user-information), but select only those users with a condition on the joined table (e.g. user-information.is-admin = true). I can't add a where clause to the with clause, because it's a 1-1 relationship, which apparently removes that functionality. I also can't add the check in the root where clause, because that introduces the bug of using the wrong table name.

[L-Mario564]

Closing in favor of #1069.