Does anyone know how I might achieve something like this using Propel?

U017Y69D9U4

Afternoon all! Does anyone know how I might achieve something like this using Propel?

SELECT *
FROM spy_sales_order sso
LEFT JOIN spy_customer sc ON LOWER(sso.email)=LOWER(sc.email)
WHERE sc.customer_reference != sso.customer_reference
AND sso.email is not null;

Specifically the LOWER() in the ON criteria.

new SpySalesOrderQuery())
            ->addJoin('spy_sales_order.email', 'spy_customer.email', Criteria::LEFT_JOIN)
            ->where('spy_customer.customer_reference != spy_sales_order.customer_reference')
            ->where('spy_sales_order.email is not null')

Doesn't appear to allow you to do anything like that 🤔

U01K43ADW5N

You could use the function setJoinCondition to add your condition
something like

new SpySalesOrderQuery())
            ->addJoin('spy_sales_order.email', 'spy_customer.email', Criteria::LEFT_JOIN)
            ->setJoinCondition('spy_sales_order.email', 'LOWER(spy_sales_order.email) = LOWER(spy_customer.email)')
            ->where('spy_customer.customer_reference != spy_sales_order.customer_reference')
            ->where('spy_sales_order.email is not null')

U017Y69D9U4

Thanks for the reply 🙂 Unfortunately this doesn't seem to be working.

The addJoin method calls a method to add the join to an in memory array of "joins", indexed by a string name. But it doesn't pass the name as a parameter (which defaults to null).

Then setJoinCondition attempts to do a lookup in that array by name, which then fails. I might look to see though if that can be patched 👍

U017Y69D9U4

Unfortunately, I can't call join() either to add it initially (it seems), as there is no foreign key relation between the customer table in the orders table