• InnerJoin in xpdo#

  • 37475
    25

    Bingo Reply #1, 4 months, 1 week ago

    Reply
    I have tried many different ideas but can not find a workable solution. I want to get this sql to xpdo.

    SQL:
    SELECT modx_garpproducts.title, modx_garpproducts.alias, modx_garpcategories.type, modx_garpcategories.parent
FROM modx_garpproducts
INNER JOIN modx_garpcategories ON modx_garpproducts.category = modx_garpcategories.catId
ORDER BY modx_garpproducts.title


    How can I solve this the best way?
    Feels like I tried 100 ways.
    Edited 4 months, 1 week ago by bingojoviken


  • 31946
    116

    Wanze Reply #2, 4 months, 1 week ago

    Reply
    Hav you tried with the innerJoin method?
    $query = $xpdo->newQuery('Garpproducts');
$query->select('Garpproducts.id, Garpproducts.title, Garpproducts.alias, Cat.id, Cat.type, Cat.parent ');
$query->innerJoin('Cat','Garpcategories','Garpproducts.category = Cat.catId');
$query->sortby('Garpproducts.title','ASC');

    I think you have to include the Primary Key's of both object into the select statement, because of lazy loading (not sure..).
    You'd also need to adjust your classnames.

    Cheers


  • 37475
    25

    Bingo Reply #3, 4 months, 1 week ago

    Reply
    Managed to solve it.

    Code:
    $query = $modx->newQuery('Product');
$query->select($modx->getSelectColumns('Product','Product'));
$query->select($modx->getSelectColumns('Category','Category', '', array('catId', 'catName', 'type', 'parent')));
$query->innerJoin('Category','Category','Product.category = Category.catId');
$query->where($where);
$query->sortby('title','ASC');
$products = $modx->getCollection('Product',$query);


  • 37475
    25

    Bingo Reply #4, 4 months, 1 week ago

    Reply
    I didn't see your reply until now, but I solved it in pretty much the same way as you wrote. Thank you anyway.