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 π€
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')
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 π
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