Примеры сложных условий запросов к БД в xPDO

FreeCode.Academy

Стратегии обучения
Уроки
Технологии
Участники
Проекты
Топики
Блоги
О проекте
Мне довольно часто приходится формировать совсем не простые запросы к БД, и не редко xPDO не позволяет просто так составить нужный запрос. В процессе я нарыл весьма глубинный способ формировать нужные запросы с вложенными подзапросами.  
 
Вот один из таких примеров (который в общих чертах попадет в новую сборку ShopModxBox, которую я так давно обещаю и кто-то уже устал ждать).
 
<?php

require_once MODX_CORE_PATH . 'components/billing/processors/mgr/orders/grid/getlist.class.php';

class ShopOrdersGetlistProcessor extends modMgrOrdersGridGetlistProcessor{
    
    
    public function prepareQueryBeforeCount(xPDOQuery $c){
        $c = parent::prepareQueryBeforeCount($c);
        
        $alias = $c->getAlias();
        
        $where = array();
        
        if($status = (int)$this->getProperty('status')){
            $where['status_id'] = $status;
        }
        
        if($contractor = (int)$this->getProperty('contractor')){
            $where['contractor'] = $contractor;
        }
        
        if($date_from = $this->getProperty('date_from')){
            $where['createdon:>='] = date('Y-m-d H:i:s', strtotime($this->getProperty('date_from')));
        }
        
        if($date_from = $this->getProperty('date_till')){
            $where['createdon:<='] = date('Y-m-d H:i:s', strtotime($this->getProperty('date_till')));
        }
                
        if($where){
            $c->where($where);
        }
        
        
        
        if($search = $this->getProperty('search')){
            $word = $this->modx->quote("%{$search}%");
            
            $q = $this->modx->newQuery('OrderProduct');
            $q->innerJoin('ShopmodxProduct', 'Product');
            $q->innerJoin('modResource', 'ResourceProduct', "ResourceProduct.id = Product.resource_id");
            
            $q_alias = $q->getAlias();
            
            $q->select(array(
                "{$q_alias}.order_id",
            ));
            
            $order_id = (int)$search;
            
            $q->where(array(
                "order_id = {$alias}.id 
                AND (order_id = {$order_id}
                    OR ResourceProduct.pagetitle LIKE {$word} 
                    OR ResourceProduct.longtitle LIKE {$word}
                    OR ResourceProduct.content LIKE {$word}
                )",
            ));
            
            $q->prepare();
            $sql = $q->toSQL();
            
            # print $sql;
            
            $c->where(array(
                "ContractorProfile.phone LIKE {$word}",
            ));
            $c->query['where'][] = new xPDOQueryCondition(array(
                'sql' => "EXISTS ({$sql})",
                'conjunction'   => "OR",
            ));
            
        }
        
        
        return $c;
    }
    
}

return 'ShopOrdersGetlistProcessor';

Советую особое внимание обратить на параметр 'conjunction' => «OR».
 
Этот запрос позволяет выполнить поиск заказов с учетом дат, id заказа, пользователя, заголовков/контента товаров и т.п.
 Вот итоговый SQL:
 
SELECT 
	`Order`.*, 
	`Order`.id as order_id, 
	Status.status as status_str, 
	(
		select 
		sum(op.price * op.quantity) 
		from `modx_billing_order_products` op 
			where op.order_id = Order.id
	) as sum, 
	Payment.id as pay_id, 
	Payment.paysys_invoice_id, 
	Payment.date as pay_date, 
	Payment.sum as pay_sum, 
	Paysystem.name as paysystem_name, 
	ContractorProfile.fullname  as contractor_fullname, 
	ContractorProfile.email as contractor_email, 
	if(ContractorProfile.mobilephone != '', ContractorProfile.mobilephone, ContractorProfile.phone) as contractor_phone, 
	ManagerProfile.fullname as manager_fullname, 
	`Status`.`id` AS `status_id`, 
	`Status`.`status` AS `status_status`, 
	`Status`.`color` AS `status_color`, 
	`Status`.`rank` AS `status_rank`, 
	`Status`.`comment` AS `status_comment` 
	FROM `modx_billing_orders` AS `Order` 
		JOIN `modx_billing_order_statuses` `Status` ON `Order`.`status_id` =  `Status`.`id` 
		LEFT JOIN `modx_billing_payments` `Payment` ON Payment.order_id = Order.id 
		LEFT JOIN `modx_billing_paysystems` `Paysystem` ON Payment.paysystem_id = Paysystem.id 
		LEFT JOIN `modx_users` `Contractor` ON `Order`.`contractor` =  `Contractor`.`id` 
		LEFT JOIN `modx_user_attributes` `ContractorProfile` ON Contractor.id=ContractorProfile.internalKey 
		LEFT JOIN `modx_users` `Manager` ON `Order`.`manager` =  `Manager`.`id` 
		LEFT JOIN `modx_user_attributes` `ManagerProfile` ON Manager.id=ManagerProfile.internalKey 
		WHERE  (  
			(  
				( 
					`Order`.`createdon` >= '2015-09-27 00:00:00' AND `Order`.`createdon` <= '2015-10-07 00:00:00' 
				)  
				AND ContractorProfile.phone LIKE '%980%' 
			)  
			OR EXISTS (
				SELECT OrderProduct.order_id 
				FROM `modx_billing_order_products` AS `OrderProduct` 
				JOIN `modx_shopmodx_products` `Product` ON `OrderProduct`.`product_id` =  `Product`.`id` 
				JOIN `modx_site_content` `ResourceProduct` ON ResourceProduct.id = Product.resource_id 
				WHERE order_id = Order.id 
					AND (order_id = 980
						OR ResourceProduct.pagetitle LIKE '%980%' 
						OR ResourceProduct.longtitle LIKE '%980%'
						OR ResourceProduct.content LIKE '%980%'
					) 
			) 
		)
Другой вариант этого же запроса:
 
<?php

require_once MODX_CORE_PATH . 'components/billing/processors/mgr/orders/grid/getlist.class.php';

class ShopOrdersGetlistProcessor extends modMgrOrdersGridGetlistProcessor{
    
    
    public function prepareQueryBeforeCount(xPDOQuery $c){
        $c = parent::prepareQueryBeforeCount($c);
        
        $alias = $c->getAlias();
        
        $where = array();
        
        if($status = (int)$this->getProperty('status')){
            $where['status_id'] = $status;
        }
        
        if($contractor = (int)$this->getProperty('contractor')){
            $where['contractor'] = $contractor;
        }
        
        if($date_from = $this->getProperty('date_from')){
            $where['createdon:>='] = date('Y-m-d H:i:s', strtotime($this->getProperty('date_from')));
        }
        
        if($date_from = $this->getProperty('date_till')){
            $where['createdon:<='] = date('Y-m-d H:i:s', strtotime($this->getProperty('date_till')));
        }
                
        if($where){
            $c->where($where);
        }
        
        
        
        if($search = $this->getProperty('search')){
            $word = $this->modx->quote("%{$search}%");
            
            $q = $this->modx->newQuery('OrderProduct');
            $q->innerJoin('ShopmodxProduct', 'Product');
            $q->innerJoin('modResource', 'ResourceProduct', "ResourceProduct.id = Product.resource_id");
            
            $q_alias = $q->getAlias();
            
            $q->select(array(
                "{$q_alias}.order_id",
            ));
            
            $order_id = (int)$search;
            
            $q->where(array(
                "order_id = {$alias}.id 
                AND (order_id = {$order_id}
                    OR ResourceProduct.pagetitle LIKE {$word} 
                    OR ResourceProduct.longtitle LIKE {$word}
                    OR ResourceProduct.content LIKE {$word}
                )",
            ));
            
            $q->prepare();
            $sql = $q->toSQL();
            
            $c->query['where'][] = new xPDOQueryCondition(array(
                'sql' => "ContractorProfile.phone LIKE {$word}",
            ));
            $c->query['where'][] = new xPDOQueryCondition(array(
                'sql' => "EXISTS ({$sql})",
                'conjunction'   => "OR",
            ));
        }
        
        
        return $c;
    }
    
}

return 'ShopOrdersGetlistProcessor';

Формирует тот же самый запрос. Отличие только в том, что заменили конструкцию 
$c->where(array(
                "ContractorProfile.phone LIKE {$word}",
            ));
на 
$c->query['where'][] = new xPDOQueryCondition(array(
                'sql' => "ContractorProfile.phone LIKE {$word}",
            ));
Здесь конструкций EXISTS более предпочтительна, чем обычный JOIN, так как у на записи не уникальные (Заказ и Товары заказа — связь один-ко-многим). EXISTS выполняет промежуточный поиск и возвращает по прежнему уникальные записи из основной таблицы. А если джоинить, то получим не уникальные записи.
 
UPD: В предыдущей версии запроса были логические ошибки. Кто скажет какие — молодца! :)
 Исправленная версия запроса с улучшениями:
 
<?php

require_once MODX_CORE_PATH . 'components/billing/processors/mgr/orders/grid/getlist.class.php';

class ShopOrdersGetlistProcessor extends modMgrOrdersGridGetlistProcessor{
    
    
    public function prepareQueryBeforeCount(xPDOQuery $c){
        $c = parent::prepareQueryBeforeCount($c);
        
        $alias = $c->getAlias();
        
        $where = array();
        
        if($status = (int)$this->getProperty('status')){
            $where['status_id'] = $status;
        }
        
        if($contractor = (int)$this->getProperty('contractor')){
            $where['contractor'] = $contractor;
        }
        
        if($date_from = $this->getProperty('date_from')){
            $where['createdon:>='] = date('Y-m-d H:i:s', strtotime($this->getProperty('date_from')));
        }
        
        if($date_from = $this->getProperty('date_till')){
            $where['createdon:<='] = date('Y-m-d H:i:s', strtotime($this->getProperty('date_till')));
        }
                
        if($where){
            $c->where($where);
        }
        
        
        
        if($search = $this->getProperty('search')){
            $word = $this->modx->quote("%{$search}%");
            
            $q = $this->modx->newQuery('OrderProduct');
            $q->innerJoin('ShopmodxProduct', 'Product');
            $q->innerJoin('modResource', 'ResourceProduct', "ResourceProduct.id = Product.resource_id");
            
            $q_alias = $q->getAlias();
            
            $q->select(array(
                "{$q_alias}.order_id",
            ));
            
            $order_id = (int)$search;
            
            $q->where(array(
                "order_id = {$alias}.id 
                AND (order_id = {$order_id}
                    OR ResourceProduct.pagetitle LIKE {$word} 
                    OR ResourceProduct.longtitle LIKE {$word}
                    OR ResourceProduct.content LIKE {$word}
                )",
            ));
            
            $q->prepare();
            $sql = $q->toSQL();
            
            # print $sql;
            
            $conditions = [];
            
            if($phone = preg_replace('/[^\+0-9\-\(\)]/', '', $search)){
                $phone = $this->modx->quote("%{$phone}%");
                
                $conditions[] = new xPDOQueryCondition(array(
                    'sql' => "REPLACE(ContractorProfile.phone, ' ', '') LIKE {$phone}",
                ));
            }
            
            $conditions[] = new xPDOQueryCondition(array(
                'sql' => "EXISTS ({$sql})",
                'conjunction'   => $conditions ? "OR" : "AND",
            ));
            
            $c->query['where'][] = $conditions;
            
            
            # $c->prepare();
            # print $c->toSQL();
        }
        
        
        return $c;
    }
    
}

return 'ShopOrdersGetlistProcessor';

Результат:
 
SELECT 
	`Order`.*, 
	`Order`.id as order_id, 
	Status.status as status_str, 
	(
		select sum(op.price * op.quantity) 
		from `modx_billing_order_products` op 
		where op.order_id = Order.id
	) as sum, 
	Payment.id as pay_id, 
	Payment.paysys_invoice_id, 
	Payment.date as pay_date, 
	Payment.sum as pay_sum, 
	Paysystem.name as paysystem_name, 
	ContractorProfile.fullname  as contractor_fullname, 
	ContractorProfile.email as contractor_email, 
	if(ContractorProfile.mobilephone != '', ContractorProfile.mobilephone, ContractorProfile.phone) as contractor_phone, 
	ManagerProfile.fullname as manager_fullname, 
	`Status`.`id` AS `status_id`, 
	`Status`.`status` AS `status_status`, 
	`Status`.`color` AS `status_color`, 
	`Status`.`rank` AS `status_rank`, 
	`Status`.`comment` AS `status_comment` 
	FROM `modx_billing_orders` AS `Order` 
	JOIN `modx_billing_order_statuses` `Status` ON `Order`.`status_id` =  `Status`.`id` 
	LEFT JOIN `modx_billing_payments` `Payment` ON Payment.order_id = Order.id 
	LEFT JOIN `modx_billing_paysystems` `Paysystem` ON Payment.paysystem_id = Paysystem.id 
	LEFT JOIN `modx_users` `Contractor` ON `Order`.`contractor` =  `Contractor`.`id` 
	LEFT JOIN `modx_user_attributes` `ContractorProfile` ON Contractor.id=ContractorProfile.internalKey 
	LEFT JOIN `modx_users` `Manager` ON `Order`.`manager` =  `Manager`.`id` 
	LEFT JOIN `modx_user_attributes` `ManagerProfile` ON Manager.id=ManagerProfile.internalKey 
	WHERE  (  
		( `Order`.`createdon` >= '2015-09-27 00:00:00' AND `Order`.`createdon` <= '2015-10-07 00:00:00' )  
		AND  
		( 
			REPLACE(ContractorProfile.phone, ' ', '') LIKE '%253140%' 
			OR EXISTS (
				SELECT OrderProduct.order_id 
					FROM `modx_billing_order_products` AS `OrderProduct` 
					JOIN `modx_shopmodx_products` `Product` ON `OrderProduct`.`product_id` =  `Product`.`id` 
					JOIN `modx_site_content` `ResourceProduct` ON ResourceProduct.id = Product.resource_id 
					WHERE order_id = Order.id 
						AND (order_id = 253140
							OR ResourceProduct.pagetitle LIKE '%253140%' 
							OR ResourceProduct.longtitle LIKE '%253140%'
							OR ResourceProduct.content LIKE '%253140%'
						) 
					) 
		)  
	)
Примеры сложных условий запросов к БД в xPDO

Добавить комментарий
