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

U017Y69D9U4
U017Y69D9U4 Posts: 34 🧑🏻‍🚀 - Cadet
edited June 2023 in Propel ORM

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 🤔

Tagged:

Comments

  • U01K43ADW5N
    U01K43ADW5N Posts: 69 🧑🏻‍🚀 - Cadet

    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
    U017Y69D9U4 Posts: 34 🧑🏻‍🚀 - Cadet

    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
    U017Y69D9U4 Posts: 34 🧑🏻‍🚀 - Cadet

    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