Большой рассказ про pdoTools, часть вторая

Инициализация класса

$pdo = $modx->getService('pdoFetch', array('class' => 'modUser'));

$pdo = $modx->getService('pdoFetch');
$pdo->setConfig(array('class' => 'modUser'));

$pdo->setConfig(array(
    'class' => 'msResourceFile',
    'loadModels' => 'ms2gallery'
));

Метод makeQuery

Методы addTVFilters и addTVs

Метод addJoins

• rightJoin - здесь наоборот, Выбираются все записи второй (правой) таблицы, даже если они не соответствуют записям в первой (левой) таблице.
• innerJoin - внутреннее объединение двух таблиц. Если для строки из одной таблицы нет соответствия в другой - такая строка исключается. То есть, innerJoin всегда возвращает данные сразу из двух таблиц.

$pdo->setConfig(array(
    'class' => 'modUser',
    'leftJoin' => array(
        'Profile' => array(
            'class' => 'modUserProfile',
            'on' => 'modUser.id = Profile.internalKey',
        ),
    'select' => array(
        'modUser' => '*',
        'Profile' => 'fullname,email',
    )
));

[[!pdoResources?
    &class=`modUser`
    &leftJoin=`{
        "Profile": {
            "class": "modUserProfile",
            "on": "modUser.id = Profile.internalKey"
        }
    }`
    &select=`{
        "modUser": "*",
        "Profile": "fullname,email"
    }`
    &showLog=`1`
    &sortby=`modUser.id`
    &sortdir=`ASC`
]]

0.0000799: pdoTools loaded
0.0000319: xPDO query object created
0.0005212: leftJoined modUserProfile as Profile
0.0001311: Added selection of modUser: SQL_CALC_FOUND_ROWS `id`, `username`, `password`, `cachepwd`, `class_key`, `active`, `remote_key`, `remote_data`, `hash_class`, `salt`, `primary_group`, `session_stale`, `sudo`
0.0000789: Added selection of modUserProfile: `fullname`, `email`
0.0000360: Sorted by modUser.id, ASC
0.0000050: Limited to 10, offset 0
0.0000942: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `modUser`.`id`, `modUser`.`username`, `modUser`.`password`, `modUser`.`cachepwd`, `modUser`.`class_key`, `modUser`.`active`, `modUser`.`remote_key`, `modUser`.`remote_data`, `modUser`.`hash_class`, `modUser`.`salt`, `modUser`.`primary_group`, `modUser`.`session_stale`, `modUser`.`sudo`, `Profile`.`fullname`, `Profile`.`email` FROM `modx_users` AS `modUser` LEFT JOIN `modx_user_attributes` `Profile` ON modUser.id = Profile.internalKey ORDER BY modUser.id ASC LIMIT 10 "
0.0019310: SQL executed
0.0001349: Total rows: 3
0.0000298: Rows fetched
0.0001581: Returning processed chunks
0.0034399: Total time
3 932 160: Memory usage

[[!pdoResources?
    &parents=`0`
    &class=`modResource`
    &loadModels=`ms2gallery`
    &leftJoin=`{
        "Image": {
            "class": "msResourceFile",
            "on": "modResource.id = Image.resource_id AND Image.parent = 0"
        },
        "Thumb": {
            "class": "msResourceFile",
            "on": "Image.id = Thumb.parent AND Thumb.path LIKE '%120x90%'"
        }
    }`
    &select=`{
        "modResource": "*",
        "Image": "Image.url as image",
        "Thumb": "Thumb.url as thumb"
    }`
    &showLog=`1`
    &sortby=`id`
    &sortdir=`ASC`
]]

Image.id = Thumb.parent

0.0001011: Loaded model "ms2gallery" from "/core/components/ms2gallery/model/"
0.0001070: pdoTools loaded
0.0000360: xPDO query object created
0.0003860: leftJoined msResourceFile as Image
0.0003152: leftJoined msResourceFile as Thumb
0.0002568: Added selection of modResource: SQL_CALC_FOUND_ROWS `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `content`, `richtext`, `template`, `menuindex`, `searchable`, `cacheable`, `createdby`, `createdon`, `editedby`, `editedon`, `deleted`, `deletedon`, `deletedby`, `publishedon`, `publishedby`, `menutitle`, `donthit`, `privateweb`, `privatemgr`, `content_dispo`, `hidemenu`, `class_key`, `context_key`, `content_type`, `uri`, `uri_override`, `hide_children_in_tree`, `show_in_tree`, `properties`
0.0000169: Added selection of msResourceFile: Image.url as image
0.0000129: Added selection of msResourceFile: Thumb.url as thumb
0.0000200: Processed additional conditions
0.0003581: Added where condition: modResource.published=1, modResource.deleted=0
0.0001092: Sorted by modResource.id, ASC
0.0000041: Limited to 10, offset 0
0.0002589: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `modResource`.`id`, `modResource`.`type`, `modResource`.`contentType`, `modResource`.`pagetitle`, `modResource`.`longtitle`, `modResource`.`description`, `modResource`.`alias`, `modResource`.`link_attributes`, `modResource`.`published`, `modResource`.`pub_date`, `modResource`.`unpub_date`, `modResource`.`parent`, `modResource`.`isfolder`, `modResource`.`introtext`, `modResource`.`content`, `modResource`.`richtext`, `modResource`.`template`, `modResource`.`menuindex`, `modResource`.`searchable`, `modResource`.`cacheable`, `modResource`.`createdby`, `modResource`.`createdon`, `modResource`.`editedby`, `modResource`.`editedon`, `modResource`.`deleted`, `modResource`.`deletedon`, `modResource`.`deletedby`, `modResource`.`publishedon`, `modResource`.`publishedby`, `modResource`.`menutitle`, `modResource`.`donthit`, `modResource`.`privateweb`, `modResource`.`privatemgr`, `modResource`.`content_dispo`, `modResource`.`hidemenu`, `modResource`.`class_key`, `modResource`.`context_key`, `modResource`.`content_type`, `modResource`.`uri`, `modResource`.`uri_override`, `modResource`.`hide_children_in_tree`, `modResource`.`show_in_tree`, `modResource`.`properties`, Image.url as image, Thumb.url as thumb FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_ms2_resource_files` `Image` ON modResource.id = Image.resource_id AND Image.parent = 0 LEFT JOIN `modx_ms2_resource_files` `Thumb` ON Image.id = Thumb.parent AND Thumb.path LIKE '%120x90%' WHERE  ( `modResource`.`published` = 1 AND `modResource`.`deleted` = 0 )  ORDER BY modResource.id ASC LIMIT 10 "
0.0015361: SQL executed
0.0000839: Total rows: 89
0.0001299: Rows fetched
0.0007241: Returning processed chunks
0.0049288: Total time
3 932 160: Memory usage

[[!pdoResources?
    &class=`msResourceFile`
    &loadModels=`ms2gallery`
    &where=`{
        "msResourceFile.parent": 0
    }`
    &leftJoin=`{
        "Resource": {
            "class": "modResource",
            "on": "msResourceFile.resource_id = Resource.id"
        },
        "Thumb": {
            "class": "msResourceFile",
            "on": "msResourceFile.id = Thumb.parent AND Thumb.path LIKE '%120x90%'"
        }
    }`
    &select=`{
        "msResourceFile": "*",
        "Resource": "pagetitle",
        "Thumb": "Thumb.url as thumb"
    }`
    &showLog=`1`
    &sortby=`id`
    &sortdir=`ASC`
]]

0.0001011: Loaded model "ms2gallery" from "/core/components/ms2gallery/model/"
0.0000682: pdoTools loaded
0.0000339: xPDO query object created
0.0001562: leftJoined modResource as Resource
0.0001469: leftJoined msResourceFile as Thumb
0.0001209: Added selection of msResourceFile: SQL_CALC_FOUND_ROWS `id`, `resource_id`, `source`, `parent`, `name`, `description`, `path`, `file`, `type`, `createdon`, `createdby`, `rank`, `url`, `properties`, `hash`, `active`
0.0001709: Added selection of modResource: `pagetitle`
0.0000141: Added selection of msResourceFile: Thumb.url as thumb
0.0001609: Added where condition: msResourceFile.parent=0
0.0000348: Sorted by msResourceFile.id, ASC
0.0000038: Limited to 10, offset 0
0.0001800: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `msResourceFile`.`id`, `msResourceFile`.`resource_id`, `msResourceFile`.`source`, `msResourceFile`.`parent`, `msResourceFile`.`name`, `msResourceFile`.`description`, `msResourceFile`.`path`, `msResourceFile`.`file`, `msResourceFile`.`type`, `msResourceFile`.`createdon`, `msResourceFile`.`createdby`, `msResourceFile`.`rank`, `msResourceFile`.`url`, `msResourceFile`.`properties`, `msResourceFile`.`hash`, `msResourceFile`.`active`, `Resource`.`pagetitle`, Thumb.url as thumb FROM `modx_ms2_resource_files` AS `msResourceFile` LEFT JOIN `modx_site_content` `Resource` ON msResourceFile.resource_id = Resource.id LEFT JOIN `modx_ms2_resource_files` `Thumb` ON msResourceFile.id = Thumb.parent AND Thumb.path LIKE '%120x90%' WHERE `msResourceFile`.`parent` = 0 ORDER BY msResourceFile.id ASC LIMIT 10 "
0.0007789: SQL executed
0.0001690: Total rows: 24
0.0000670: Rows fetched
0.0005300: Returning processed chunks
0.0032029: Total time
3 932 160: Memory usage

Метод addGrouping

[[!pdoResources?
    &parents=`0`
    &class=`modResource`
    &loadModels=`ms2gallery`
    &leftJoin=`{
        "Image": {
            "class": "msResourceFile",
            "on": "modResource.id = Image.resource_id AND Image.parent = 0"
        }
    }`
    &select=`{
        "modResource": "*",
        "Image": "COUNT(Image.id) as images"
    }`
    &groupby=`modResource.id`
    &showLog=`1`
    &sortby=`id`
    &sortdir=`ASC`
]]

Метод addSelects

&select=`id,pagetitle,longtitle`

1. При использовании Join нужен массив, который укажет из какой таблицы что выбирать

&select=`{
    "modResource": "id,pagetitle,longtitle"
    "Image": "url"
}`

1. Если используются псевдонимы в select, то обязательно нужно указать и псевдоним таблицы

&select=`{
    "modResource": "id,pagetitle,longtitle"
    "Image": "Image.url as image"
}`

1. Перечисление всех полей можно заменять звездочкой

&select=`{
    "modResource": "*"
    "Image": "Image.url as image"
}`

Метод addWhere

&where=`{"id:>": "15", "published:!=": "0"}`

&leftJoin=`{
    "Image": {
        "class": "msResourceFile",
        "on": "modResource.id = Image.resource_id AND Image.parent = 0"
    }
}`
&where=`{
    "published:!=": "0",
    "Image.active": "1"
}`

&where=`{
    "modResource.id:>": "15",
    "published:!=": "0",
    "Image.active": "1"
}`

&includeTVs=`image,file,mytv`
&where=`{
    "image:LIKE":"%yandex.ru%",
    "OR:file:=": "1",
    "OR:text:!=": "",
}`

&includeTVs=`image,file,mytv`
&where=`{
    "TVimage.value:LIKE":"%yandex.ru%",
    "OR:TVfile.value:=": "1",
    "OR:TVtext.value:!=": "",
}`

&where=`["
    TVimage LIKE \"%yandex%\" OR (TVfile.value = 1 AND TVtext.value != \"\")
"]`

Метод addSort

&sortby=`publishedon`
&sortdir=`ASC`

&sortby=`modResource.publishedon`
&sortdir=`ASC`

&sortby=`{
    "modResource.publishedon": "ASC",
    "modUser.id": "DESC"
}`

Выполнение запроса

• ids - список с id подходящих объектов, через запятую. Обычно используется, чтобы выбрать одним сниппетом нужные id ресурсов и передать их на вход другому сниппету.
• data - массив с результатами. При вызове через сниппет вы получите слово Array, так как все сниппеты MODX возвращают только строки. Зато при вызове из другог осниппета вы получите массив.
• tpl - результат запроса, оформленный в указанный чанк &tpl=``. А если чанка нет, то просто распечатанные массивы результатов.

Обработка результатов

&checkPermissions=`list,view`

Полезные методы getArray и getCollection

$pdo = $modx->getService('pdoFetch');
$res = $pdo->getArray('modResource', 1);
print_r($res);

$pdo = $modx->getService('pdoFetch');
$resources = $pdo->getCollection('modResource');
foreach ($res as $res) {
    print_r($res);
}

$pdo = $modx->getService('pdoFetch');
$res = $pdo->getArray('modResource', 1);

print_r($modx->getPlaceholder('pdoTools.log'));

$files = $pdo->getCollection('TicketFile', array('parent' => 1));
print_r($files);

$files = $pdo->getCollection('TicketFile', array(), array(
    'innerJoin' => array(
        'Ticket' => array(
            'class' => 'Ticket',
            'on' => 'Ticket.id = TicketFile.parent'
        )
    ),
    'select' => array(
        'TicketFile' => '*',
        'Ticket' => 'pagetitle'
    ),
    'sortby' => array(
        'TicketFile.id' => 'ASC'
    )
));
echo '<pre>';
print_r($modx->getPlaceholder('pdoTools.log'));
print_r($files);

0.0000322: xPDO query object created
0.0003891: innerJoined Ticket as Ticket
0.0001400: Added selection of TicketFile: SQL_CALC_FOUND_ROWS `id`, `parent`, `class`, `source`, `name`, `description`, `path`, `file`, `type`, `size`, `createdon`, `createdby`, `url`, `thumb`, `deleted`, `properties`, `hash`
0.0001042: Added selection of Ticket: `pagetitle`
0.0005560: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `TicketFile`.`id`, `TicketFile`.`parent`, `TicketFile`.`class`, `TicketFile`.`source`, `TicketFile`.`name`, `TicketFile`.`description`, `TicketFile`.`path`, `TicketFile`.`file`, `TicketFile`.`type`, `TicketFile`.`size`, `TicketFile`.`createdon`, `TicketFile`.`createdby`, `TicketFile`.`url`, `TicketFile`.`thumb`, `TicketFile`.`deleted`, `TicketFile`.`properties`, `TicketFile`.`hash`, `Ticket`.`pagetitle` FROM `modx_tickets_files` AS `TicketFile` JOIN `modx_site_content` `Ticket` ON Ticket.id = TicketFile.parent "
0.0023780: Total time
1 835 008: Memory usage

Заключение