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