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

Fi1osof
9 окт. 2015 г., 0:26

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

В блоге Tips & tricks

Мне довольно часто приходится формировать совсем не простые запросы к БД, и не редко 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%' ) ) ) )

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