MODX Community

Understanding how to use xPDOQuery.leftJoin correctly

Hello all,

Trust all are doing as best as they can during this covid-19 period. I’m trying to write a query with xPDO. The correct query syntax is: “SELECT Order.user_id, Order.createdon, OrderProduct.product_id FROM modx_ms2_orders AS Order LEFT JOIN modx_ms2_order_products OrderProduct ON OrderProduct.order_id = Order.id WHERE Order.id = 1”

This is the code that I wrote:
$query = $modx->newQuery(‘msOrder’);
$query->select($modx->getSelectColumns(‘msOrder’, ‘Order’,’’,array(‘user_id’,‘createdon’)));
$query->select($modx->getSelectColumns(‘msOrderProduct’, ‘OrderProduct’,’’,array(‘product_id’)));
$query->setClassAlias(‘Order’);
$query->leftJoin(‘msOrderProduct’,‘OrderProduct’,array(
‘OrderProduct.order_id:=’ => ‘Order.id’
));
$query->where(array(
‘Order.id’ => 1,
));
But when I try $orders = $modx->getCollection(‘msOrder’,$query), I’m getting a 500 error and the prepare -> toSQL return: “SELECT Order.user_id, Order.createdon, OrderProduct.product_id FROM modx_ms2_orders AS Order LEFT JOIN modx_ms2_order_products OrderProduct ON OrderProduct.order_id = ‘Order.id’ WHERE Order.id = 1”.

What should I do to make the SQL to return “OrderProduct.order_id = Order.id” instead of “OrderProduct.order_id = ‘Order`.id’”?

Hi!

Not the easiest to read since you didn’t put it in code tags but I’m guessing this might work:

$query->leftJoin('msOrderProduct','OrderProduct','OrderProduct.order_id = Order.id');

instead of the join you have there.

1 Like

Yes I tried that but when I tried $orders = $modx->getCollection(‘msOrder’,$query), I’m getting a 500 error

What does the PHP error log say?

There are no errors in the console. It is reporting nothing.

Mark means the PHP error log on your server (or local dev environment) rather than the browser console.

If you’re getting a 500 response that means something is going wrong server-side and the only way to know for sure is to look at what error is showing in the log.

Would also help to see the SQL produced after making the change to the left join syntax digitalpenguin suggested.

Depending on your server configuration, temporarily placing

ini_set('display_errors', 'on')

at the top of your script might save you having to dig out the PHP log to see what’s throwing the 500 error.

Hey guys,

This works. Thanks.

1 Like

Ah, good to hear! :slight_smile: