Subquery in MODX

Using SQL's subquery is a common practice by developers to join several tables and sort the result or search a text inside cross tables in a database.

Now for MODX, that is using xPDO for its database abstraction, it can also be done in its format.

Let's say we want to list customers based on user's account, to get their total ordered item and paid invoices.

In basic SQL, it might be formatted as below:


SELECT DISTINCT
	modUser.id,
	modUser.username,
	Profile.fullname,
	(
		SELECT COUNT(*)
		FROM `modx_marketeer_order` AS `mkteerOrder`
		WHERE mkteerOrder.user_id = modUser.id
	) AS calc_total_order,
	(
		SELECT COUNT(*)
		FROM `modx_marketeer_invoice` AS `mkteerInvoice`
		WHERE ( mkteerInvoice.user_id = modUser.id AND `mkteerInvoice`.`is_paid` = 1 )
	) AS calc_total_paid_invoice
FROM `modx_users` AS `modUser`
LEFT JOIN `modx_user_attributes` `Profile` ON Profile.internalKey = modUser.id
RIGHT JOIN `modx_marketeer_order` `Order` ON Order.user_id = modUser.id
RIGHT JOIN `modx_marketeer_invoice` `Invoice` ON Invoice.user_id = modUser.id
HAVING `modUser`.`id` IS NOT NULL
ORDER BY calc_total_order desc
LIMIT 10

To achieve the same query using xPDO is below:

<?php

$classKey = 'modUser';

/**
 * Total order
 */
$subQuery      = $modx->newQuery('mkteerOrder');
$subQuery->select("COUNT(*)");
// this is a STRING format!
$subQuery->where("mkteerOrder.user_id = {$classKey}.id");
$subQuery->prepare();
$totalOrderSql = $subQuery->toSQL();

/**
 * Total invoice
 */
$subQuery        = $modx->newQuery('mkteerInvoice');
$subQuery->select("COUNT(*)");
//$subQuery->where("mkteerInvoice.user_id = {$classKey}.id AND is_paid=1");
// or...
$subQuery->where(array(
    "mkteerInvoice.user_id = {$classKey}.id", // this is a STRING format!
    'is_paid' => 1,
));
$subQuery->prepare();
$totalInvoiceSql = $subQuery->toSQL();

$c = $modx->newQuery('modUser');
$c->distinct();
$c->leftJoin('modUserProfile', 'Profile', "Profile.internalKey = {$classKey}.id");

$c->rightJoin('mkteerOrder', 'Order', 'Order.user_id = '.$classKey.'.id');
$c->rightJoin('mkteerInvoice', 'Invoice', 'Invoice.user_id = '.$classKey.'.id');

$c->select(array(
    "$classKey.id",
    "$classKey.username",
    'Profile.fullname',
    'calc_total_order'        => "($totalOrderSql)",
    'calc_total_paid_invoice' => "($totalInvoiceSql)",
));

$c->having(array(
    "$classKey.id:!=" => null,
));

//$c->sortby('id', 'asc');
$c->sortby('calc_total_order', 'desc');

$c->limit(10);

/**
 * Uncomment this to see the formed SQL
 */
//$c->prepare();
//$sql = $c->toSQL();
//echo $sql.'<hr>';

$customers = $modx->getCollection($classKey, $c);
if (!$customers) {
    die('No customers were found!');
}

/**
 * OUTPUT
 */
$customerArray = array();
foreach ($customers as $customer) {
    $customerArray[] = $customer->toArray('', false, true);
}
echo '<pre>';
print_r($customerArray);
echo '</pre>';
echo '<hr>Done!';

You can use the part of the above code to be used inside the prepareQueryBeforeCount() method if you use class based processor for Custom Manager Page in MODX's manager.

Example:


<?php

class MarketeerCustomerGetListProcessor extends modObjectGetListProcessor
{

    public $classKey             = 'modUser';
    public $languageTopics       = array('marketeer:mgr');
    public $defaultSortField     = 'id';
    public $defaultSortDirection = 'desc';
    public $objectType           = 'marketeer.MarketeerCustomerGetList';

    public function prepareQueryBeforeCount(xPDOQuery $c)
    {
        $c->distinct();

        /**
         * Total order
         */
        $subQuery      = $this->modx->newQuery('mkteerOrder');
        $subQuery->select("COUNT(*)");
        $subQuery->where("mkteerOrder.user_id = {$this->classKey}.id");
        $subQuery->prepare();
        $totalOrderSql = $subQuery->toSQL();

        /**
         * Total invoice
         */
        $subQuery        = $this->modx->newQuery('mkteerInvoice');
        $subQuery->select("COUNT(*)");
        $subQuery->where("mkteerInvoice.user_id = {$this->classKey}.id AND is_paid=1");
        $subQuery->prepare();
        $totalInvoiceSql = $subQuery->toSQL();

        $c->select(array(
            $this->classKey.'.*',
            'calc_total_order'        => "($totalOrderSql)",
            'calc_total_paid_invoice' => "($totalInvoiceSql)",
        ));
        $c->rightJoin('mkteerOrder', 'Order', 'Order.user_id = '.$this->classKey.'.id');
        $c->rightJoin('mkteerInvoice', 'Invoice', 'Invoice.user_id = '.$this->classKey.'.id');

        $query = $this->getProperty('query');
        if (!empty($query)) {
            $c->leftJoin('modUserProfile', 'Profile', 'Profile.internalKey = '.$this->classKey.'.id');
            $c->where(array(
                $this->classKey.'.username:LIKE' => '%'.$query.'%',
                'OR:Profile.fullname:LIKE'       => '%'.$query.'%'
            ));
        }

        $c->having(array(
            $this->classKey.'.id:!=' => null
        ));

        return $c;
    }

}

return 'MarketeerCustomerGetListProcessor';

I hope this is useful on explaining the subquery format in MODX's database call.

On side note: marketeer is our custom e-commerce package, so you can change the prefix to suit your needs.

Cheers.



Comments

blog comments powered by Disqus