Как выбрать ЛУЧШИЕ где комментов >= 10

Вывожу лучшие тикеты через mFilter2 с сортировкой по количеству комментариев примерно так (убрал всё лишнее чтобы не мешало):

{$_modx->runSnippet('!mFilter2', [
    'element' => 'getTickets',
    'sortdir' => 'DESC',
    'sortby' => 'comments',
    'leftJoin' => '{
		"Thread":{ "class":"TicketThread","on":"Thread.resource=Ticket.id" },
		"Comment":{ "class":"TicketComment","on":"Comment.thread=Thread.id AND Comment.parent = \'0\'" }
	}',
    'select' => '{
		"Comment":"COUNT(DISTINCT Comment.id) as comments"
	}',
])}

Есть 2 вопроса:

1. Почему не срабатывает выражение

AND Comment.parent = \'0\'

То есть выводится количество всех комментариев, а не только первого уровня как мне нужно.

2. Как выбрать только те, у которых количество комментариев больше 10 не меняя направленность сортировки вывода?

===========================================>
UPD.: написал решение в отдельной статье.
===========================================>

Василий Столейков

27 июня 2016, 06:00

2 630

Комментарии: 11

Василий Столейков

27 июня 2016, 09:05

2.1 Почему-то не работает эта выборка по where:

'where' => '{ "comments:>=": "10" }',

Хотя сортировка по тому же полю срабатывает:

'sortby' => 'comments',

Василий Столейков

27 июня 2016, 14:57

А если в каждом чанке проверять плейсхолдер comments по условию, то это будет ужасный костыль.
С таким подходом всё равно из базы выбираются все записи и невозможно указать количество тикетов для вывода (разве если не играться при каждой итерации с set|getPlaceholder устанавливая в них колчиество пропущенных тикетов и проверять их… Будет очень печально..)

Василий Столейков

27 июня 2016, 21:35

Похоже что не такой уж это и костыльный вариант…
Василий в &checkPermissions тоже проверяет всё это дело тоже после выборки bezumkin.ru/sections/components/1841/:

Права проверяются после выборки, а это значит, что при указанном лимите = 10, на странице может быть меньше документов, потому что несколько из них не прошли проверку.
Проверять до выборки или во время нее пока не получается. Немного успокаивает тот факт, что getResources работает ровно так же, но в отличии от pdoTools — даже не меняет плейсхолдер [[+total]] при исключении.

Владимир Кисилица

27 июня 2016, 16:38

Сложно сказать, почему не работает. Скиньте, лог работы сниппета

Василий Столейков

27 июня 2016, 21:14

Упростил вызов по-максимому и выкинул всё лишнее, чтобы не засорять лог.

Получилось следующее:

{$_modx->runSnippet('!getTickets', [
    'tpl' => 'tpl.consultant.card',
    'sortdir' => 'DESC',
    'sortby' => 'comments',
    'showHidden' => 1,
    'limit' => 1,
    'parents' => 364375,
    'leftJoin' => '{
		"Thread":{ "class":"TicketThread","on":"Thread.resource=Ticket.id" },
		"Comment":{ "class":"TicketComment","on":"Comment.thread=Thread.id AND Comment.parent = \'0\'" }
	}',
    'select' => '{
		"Comment":"COUNT(DISTINCT Comment.id) as comments"
	}',
	'where' => '{ "comments:>=": "10" }',
    'showLog' => 1,
])}

А вот и ругань логов:

0.0000451: Query parameters are prepared.
0.0000100: xPDO query object created
0.0000439: leftJoined TicketsSection as Section
0.0000319: leftJoined modUser as User
0.0000300: leftJoined modUserProfile as Profile
0.0002699: leftJoined TicketVote as Vote
0.0002429: leftJoined TicketStar as Star
0.0003140: leftJoined TicketThread as Thread
0.0003591: leftJoined TicketComment as Comment
0.0000010: Grouped by Ticket.id
0.0000300: Added selection of TicketsSection: SQL_CALC_FOUND_ROWS `id` AS `section.id`, `type` AS `section.type`, `contentType` AS `section.contentType`, `pagetitle` AS `section.pagetitle`, `longtitle` AS `section.longtitle`, `description` AS `section.description`, `alias` AS `section.alias`, `link_attributes` AS `section.link_attributes`, `published` AS `section.published`, `pub_date` AS `section.pub_date`, `unpub_date` AS `section.unpub_date`, `parent` AS `section.parent`, `isfolder` AS `section.isfolder`, `introtext` AS `section.introtext`, `richtext` AS `section.richtext`, `template` AS `section.template`, `menuindex` AS `section.menuindex`, `searchable` AS `section.searchable`, `cacheable` AS `section.cacheable`, `createdby` AS `section.createdby`, `createdon` AS `section.createdon`, `editedby` AS `section.editedby`, `editedon` AS `section.editedon`, `deleted` AS `section.deleted`, `deletedon` AS `section.deletedon`, `deletedby` AS `section.deletedby`, `publishedon` AS `section.publishedon`, `publishedby` AS `section.publishedby`, `menutitle` AS `section.menutitle`, `donthit` AS `section.donthit`, `privateweb` AS `section.privateweb`, `privatemgr` AS `section.privatemgr`, `content_dispo` AS `section.content_dispo`, `hidemenu` AS `section.hidemenu`, `class_key` AS `section.class_key`, `context_key` AS `section.context_key`, `content_type` AS `section.content_type`, `uri` AS `section.uri`, `uri_override` AS `section.uri_override`, `hide_children_in_tree` AS `section.hide_children_in_tree`, `show_in_tree` AS `section.show_in_tree`, `properties` AS `section.properties`, `ext_hot` AS `section.ext_hot`, `ext_vip` AS `section.ext_vip`, `ext_moderation` AS `section.ext_moderation`, `ext_author` AS `section.ext_author`, `ext_hot_time` AS `section.ext_hot_time`, `ext_vip_time` AS `section.ext_vip_time`
0.0000041: Added selection of modUser: `username`
0.0000072: Added selection of modUserProfile: `internalKey`, `fullname`, `email`, `phone`, `mobilephone`, `blocked`, `blockeduntil`, `blockedafter`, `logincount`, `lastlogin`, `thislogin`, `failedlogincount`, `sessionid`, `dob`, `gender`, `address`, `country`, `city`, `state`, `zip`, `fax`, `photo`, `comment`, `website`, `extended`
0.0000100: Added selection of Ticket: `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `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`, `ext_hot`, `ext_vip`, `ext_moderation`, `ext_author`, `ext_hot_time`, `ext_vip_time`
0.0000029: Added selection of TicketVote: `value` as `vote`
0.0000188: Added selection of TicketStar: COUNT(`id`) as `star`
0.0000110: Added selection of TicketComment: COUNT(DISTINCT Comment.id) as comments
0.0002079: Processed additional conditions
0.0002711: Added where condition: class_key=Ticket, comments:>==10, Ticket.parent:IN(364375,364451,364452,364453,364454,364455,364456,364457,364458,364459), Ticket.published=1, Ticket.deleted=0
0.0000122: Sorted by comments, DESC
0.0000010: Limited to 1, offset 0
0.0001349: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `Section`.`id` AS `section.id`, `Section`.`type` AS `section.type`, `Section`.`contentType` AS `section.contentType`, `Section`.`pagetitle` AS `section.pagetitle`, `Section`.`longtitle` AS `section.longtitle`, `Section`.`description` AS `section.description`, `Section`.`alias` AS `section.alias`, `Section`.`link_attributes` AS `section.link_attributes`, `Section`.`published` AS `section.published`, `Section`.`pub_date` AS `section.pub_date`, `Section`.`unpub_date` AS `section.unpub_date`, `Section`.`parent` AS `section.parent`, `Section`.`isfolder` AS `section.isfolder`, `Section`.`introtext` AS `section.introtext`, `Section`.`richtext` AS `section.richtext`, `Section`.`template` AS `section.template`, `Section`.`menuindex` AS `section.menuindex`, `Section`.`searchable` AS `section.searchable`, `Section`.`cacheable` AS `section.cacheable`, `Section`.`createdby` AS `section.createdby`, `Section`.`createdon` AS `section.createdon`, `Section`.`editedby` AS `section.editedby`, `Section`.`editedon` AS `section.editedon`, `Section`.`deleted` AS `section.deleted`, `Section`.`deletedon` AS `section.deletedon`, `Section`.`deletedby` AS `section.deletedby`, `Section`.`publishedon` AS `section.publishedon`, `Section`.`publishedby` AS `section.publishedby`, `Section`.`menutitle` AS `section.menutitle`, `Section`.`donthit` AS `section.donthit`, `Section`.`privateweb` AS `section.privateweb`, `Section`.`privatemgr` AS `section.privatemgr`, `Section`.`content_dispo` AS `section.content_dispo`, `Section`.`hidemenu` AS `section.hidemenu`, `Section`.`class_key` AS `section.class_key`, `Section`.`context_key` AS `section.context_key`, `Section`.`content_type` AS `section.content_type`, `Section`.`uri` AS `section.uri`, `Section`.`uri_override` AS `section.uri_override`, `Section`.`hide_children_in_tree` AS `section.hide_children_in_tree`, `Section`.`show_in_tree` AS `section.show_in_tree`, `Section`.`properties` AS `section.properties`, `Section`.`ext_hot` AS `section.ext_hot`, `Section`.`ext_vip` AS `section.ext_vip`, `Section`.`ext_moderation` AS `section.ext_moderation`, `Section`.`ext_author` AS `section.ext_author`, `Section`.`ext_hot_time` AS `section.ext_hot_time`, `Section`.`ext_vip_time` AS `section.ext_vip_time`, `User`.`username`, `Profile`.`internalKey`, `Profile`.`fullname`, `Profile`.`email`, `Profile`.`phone`, `Profile`.`mobilephone`, `Profile`.`blocked`, `Profile`.`blockeduntil`, `Profile`.`blockedafter`, `Profile`.`logincount`, `Profile`.`lastlogin`, `Profile`.`thislogin`, `Profile`.`failedlogincount`, `Profile`.`sessionid`, `Profile`.`dob`, `Profile`.`gender`, `Profile`.`address`, `Profile`.`country`, `Profile`.`city`, `Profile`.`state`, `Profile`.`zip`, `Profile`.`fax`, `Profile`.`photo`, `Profile`.`comment`, `Profile`.`website`, `Profile`.`extended`, `Ticket`.`id`, `Ticket`.`type`, `Ticket`.`contentType`, `Ticket`.`pagetitle`, `Ticket`.`longtitle`, `Ticket`.`description`, `Ticket`.`alias`, `Ticket`.`link_attributes`, `Ticket`.`published`, `Ticket`.`pub_date`, `Ticket`.`unpub_date`, `Ticket`.`parent`, `Ticket`.`isfolder`, `Ticket`.`introtext`, `Ticket`.`richtext`, `Ticket`.`template`, `Ticket`.`menuindex`, `Ticket`.`searchable`, `Ticket`.`cacheable`, `Ticket`.`createdby`, `Ticket`.`createdon`, `Ticket`.`editedby`, `Ticket`.`editedon`, `Ticket`.`deleted`, `Ticket`.`deletedon`, `Ticket`.`deletedby`, `Ticket`.`publishedon`, `Ticket`.`publishedby`, `Ticket`.`menutitle`, `Ticket`.`donthit`, `Ticket`.`privateweb`, `Ticket`.`privatemgr`, `Ticket`.`content_dispo`, `Ticket`.`hidemenu`, `Ticket`.`class_key`, `Ticket`.`context_key`, `Ticket`.`content_type`, `Ticket`.`uri`, `Ticket`.`uri_override`, `Ticket`.`hide_children_in_tree`, `Ticket`.`show_in_tree`, `Ticket`.`properties`, `Ticket`.`ext_hot`, `Ticket`.`ext_vip`, `Ticket`.`ext_moderation`, `Ticket`.`ext_author`, `Ticket`.`ext_hot_time`, `Ticket`.`ext_vip_time`, `Vote`.`value` as `vote`, COUNT(`Star`.`id`) as `star`, COUNT(DISTINCT Comment.id) as comments FROM `modx_site_content` AS `Ticket` LEFT JOIN `modx_site_content` `Section` ON `Section`.`id` = `Ticket`.`parent` LEFT JOIN `modx_users` `User` ON `User`.`id` = `Ticket`.`createdby` LEFT JOIN `modx_user_attributes` `Profile` ON `Profile`.`internalKey` = `User`.`id` LEFT JOIN `modx_tickets_votes` `Vote` ON `Vote`.`id` = `Ticket`.`id` AND `Vote`.`class` = "Ticket" AND `Vote`.`createdby` = 2 LEFT JOIN `modx_tickets_stars` `Star` ON `Star`.`id` = `Ticket`.`id` AND `Star`.`class` = "Ticket" AND `Star`.`createdby` = 2 LEFT JOIN `modx_tickets_threads` `Thread` ON Thread.resource=Ticket.id LEFT JOIN `modx_tickets_comments` `Comment` ON Comment.thread=Thread.id AND Comment.parent = '0' WHERE  ( `Ticket`.`class_key` = 'Ticket' AND `Ticket`.`comments` >= '10' AND `Ticket`.`parent` IN (364375,364451,364452,364453,364454,364455,364456,364457,364458,364459) AND `Ticket`.`published` = 1 AND `Ticket`.`deleted` = 0 )  GROUP BY Ticket.id ORDER BY comments DESC LIMIT 1 "
0.0004210: Could not process query, error #1054: Unknown column 'Ticket.comments' in 'where clause'
0.0000031: Returning processed chunks
0.0025301: Total time
2 097 152: Memory usage

Как я понимаю от пытается выбрать колонку Ticket.comments, хотя comments является резлультатом подсчёта в select.

Но как тогда правильно подключить в вызове результат подсчёта и исходя из его значения делать выборку?

Владимир Кисилица

27 июня 2016, 21:44

'where' => '{ "Comment.comments:>=": "10" }',

Василий Столейков

27 июня 2016, 21:55

Неа, ошибка практическа та же:

0.0004501: Could not process query, error #1054: Unknown column 'Comment.comments' in 'where clause'

Алексей Ерохин

28 июня 2016, 15:41

'where' => '{ "Comment.comments:>=": "10" }',
'select' => '{
		"Comment":"COUNT(DISTINCT Comment.id) as Comment.comments"
	}',

Василий Столейков

28 июня 2016, 15:48

Интересно, та ошибка исчезла, сейчас говорит что SQL запрос составлен неверно…

0.0000460: Query parameters are prepared.
0.0000100: xPDO query object created
0.0000451: leftJoined TicketsSection as Section
0.0000310: leftJoined modUser as User
0.0000288: leftJoined modUserProfile as Profile
0.0001030: leftJoined TicketVote as Vote
0.0000889: leftJoined TicketStar as Star
0.0000858: leftJoined TicketThread as Thread
0.0000858: leftJoined TicketComment as Comment
0.0000010: Grouped by Ticket.id
0.0000310: Added selection of TicketsSection: SQL_CALC_FOUND_ROWS `id` AS `section.id`, `type` AS `section.type`, `contentType` AS `section.contentType`, `pagetitle` AS `section.pagetitle`, `longtitle` AS `section.longtitle`, `description` AS `section.description`, `alias` AS `section.alias`, `link_attributes` AS `section.link_attributes`, `published` AS `section.published`, `pub_date` AS `section.pub_date`, `unpub_date` AS `section.unpub_date`, `parent` AS `section.parent`, `isfolder` AS `section.isfolder`, `introtext` AS `section.introtext`, `richtext` AS `section.richtext`, `template` AS `section.template`, `menuindex` AS `section.menuindex`, `searchable` AS `section.searchable`, `cacheable` AS `section.cacheable`, `createdby` AS `section.createdby`, `createdon` AS `section.createdon`, `editedby` AS `section.editedby`, `editedon` AS `section.editedon`, `deleted` AS `section.deleted`, `deletedon` AS `section.deletedon`, `deletedby` AS `section.deletedby`, `publishedon` AS `section.publishedon`, `publishedby` AS `section.publishedby`, `menutitle` AS `section.menutitle`, `donthit` AS `section.donthit`, `privateweb` AS `section.privateweb`, `privatemgr` AS `section.privatemgr`, `content_dispo` AS `section.content_dispo`, `hidemenu` AS `section.hidemenu`, `class_key` AS `section.class_key`, `context_key` AS `section.context_key`, `content_type` AS `section.content_type`, `uri` AS `section.uri`, `uri_override` AS `section.uri_override`, `hide_children_in_tree` AS `section.hide_children_in_tree`, `show_in_tree` AS `section.show_in_tree`, `properties` AS `section.properties`, `ext_category` AS `section.ext_category`, `ext_author` AS `section.ext_author`, `ext_moderation` AS `section.ext_moderation`, `ext_vip_time` AS `section.ext_vip_time`, `ext_hot_time` AS `section.ext_hot_time`, `ext_vip` AS `section.ext_vip`, `ext_hot` AS `section.ext_hot`
0.0000029: Added selection of modUser: `username`
0.0000072: Added selection of modUserProfile: `internalKey`, `fullname`, `email`, `phone`, `mobilephone`, `blocked`, `blockeduntil`, `blockedafter`, `logincount`, `lastlogin`, `thislogin`, `failedlogincount`, `sessionid`, `dob`, `gender`, `address`, `country`, `city`, `state`, `zip`, `fax`, `photo`, `comment`, `website`, `extended`
0.0000100: Added selection of Ticket: `id`, `type`, `contentType`, `pagetitle`, `longtitle`, `description`, `alias`, `link_attributes`, `published`, `pub_date`, `unpub_date`, `parent`, `isfolder`, `introtext`, `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`, `ext_category`, `ext_author`, `ext_moderation`, `ext_vip_time`, `ext_hot_time`, `ext_vip`, `ext_hot`
0.0000100: Added selection of TicketVote: `value` as `vote`
0.0000229: Added selection of TicketStar: COUNT(`id`) as `star`
0.0000160: Added selection of TicketComment: COUNT(DISTINCT Comment.id) as Comment.comments
0.0002849: Processed additional conditions
0.0003471: Added where condition: class_key=Ticket, Comment.comments:>==10, Ticket.parent:IN(364375,364451,364452,364453,364454,364455,364456,364457,364458,364459), Ticket.published=1, Ticket.deleted=0
0.0000119: Sorted by comments, DESC
0.0000010: Limited to 10, offset 0
0.0001340: SQL prepared "SELECT SQL_CALC_FOUND_ROWS `Section`.`id` AS `section.id`, `Section`.`type` AS `section.type`, `Section`.`contentType` AS `section.contentType`, `Section`.`pagetitle` AS `section.pagetitle`, `Section`.`longtitle` AS `section.longtitle`, `Section`.`description` AS `section.description`, `Section`.`alias` AS `section.alias`, `Section`.`link_attributes` AS `section.link_attributes`, `Section`.`published` AS `section.published`, `Section`.`pub_date` AS `section.pub_date`, `Section`.`unpub_date` AS `section.unpub_date`, `Section`.`parent` AS `section.parent`, `Section`.`isfolder` AS `section.isfolder`, `Section`.`introtext` AS `section.introtext`, `Section`.`richtext` AS `section.richtext`, `Section`.`template` AS `section.template`, `Section`.`menuindex` AS `section.menuindex`, `Section`.`searchable` AS `section.searchable`, `Section`.`cacheable` AS `section.cacheable`, `Section`.`createdby` AS `section.createdby`, `Section`.`createdon` AS `section.createdon`, `Section`.`editedby` AS `section.editedby`, `Section`.`editedon` AS `section.editedon`, `Section`.`deleted` AS `section.deleted`, `Section`.`deletedon` AS `section.deletedon`, `Section`.`deletedby` AS `section.deletedby`, `Section`.`publishedon` AS `section.publishedon`, `Section`.`publishedby` AS `section.publishedby`, `Section`.`menutitle` AS `section.menutitle`, `Section`.`donthit` AS `section.donthit`, `Section`.`privateweb` AS `section.privateweb`, `Section`.`privatemgr` AS `section.privatemgr`, `Section`.`content_dispo` AS `section.content_dispo`, `Section`.`hidemenu` AS `section.hidemenu`, `Section`.`class_key` AS `section.class_key`, `Section`.`context_key` AS `section.context_key`, `Section`.`content_type` AS `section.content_type`, `Section`.`uri` AS `section.uri`, `Section`.`uri_override` AS `section.uri_override`, `Section`.`hide_children_in_tree` AS `section.hide_children_in_tree`, `Section`.`show_in_tree` AS `section.show_in_tree`, `Section`.`properties` AS `section.properties`, `Section`.`ext_category` AS `section.ext_category`, `Section`.`ext_author` AS `section.ext_author`, `Section`.`ext_moderation` AS `section.ext_moderation`, `Section`.`ext_vip_time` AS `section.ext_vip_time`, `Section`.`ext_hot_time` AS `section.ext_hot_time`, `Section`.`ext_vip` AS `section.ext_vip`, `Section`.`ext_hot` AS `section.ext_hot`, `User`.`username`, `Profile`.`internalKey`, `Profile`.`fullname`, `Profile`.`email`, `Profile`.`phone`, `Profile`.`mobilephone`, `Profile`.`blocked`, `Profile`.`blockeduntil`, `Profile`.`blockedafter`, `Profile`.`logincount`, `Profile`.`lastlogin`, `Profile`.`thislogin`, `Profile`.`failedlogincount`, `Profile`.`sessionid`, `Profile`.`dob`, `Profile`.`gender`, `Profile`.`address`, `Profile`.`country`, `Profile`.`city`, `Profile`.`state`, `Profile`.`zip`, `Profile`.`fax`, `Profile`.`photo`, `Profile`.`comment`, `Profile`.`website`, `Profile`.`extended`, `Ticket`.`id`, `Ticket`.`type`, `Ticket`.`contentType`, `Ticket`.`pagetitle`, `Ticket`.`longtitle`, `Ticket`.`description`, `Ticket`.`alias`, `Ticket`.`link_attributes`, `Ticket`.`published`, `Ticket`.`pub_date`, `Ticket`.`unpub_date`, `Ticket`.`parent`, `Ticket`.`isfolder`, `Ticket`.`introtext`, `Ticket`.`richtext`, `Ticket`.`template`, `Ticket`.`menuindex`, `Ticket`.`searchable`, `Ticket`.`cacheable`, `Ticket`.`createdby`, `Ticket`.`createdon`, `Ticket`.`editedby`, `Ticket`.`editedon`, `Ticket`.`deleted`, `Ticket`.`deletedon`, `Ticket`.`deletedby`, `Ticket`.`publishedon`, `Ticket`.`publishedby`, `Ticket`.`menutitle`, `Ticket`.`donthit`, `Ticket`.`privateweb`, `Ticket`.`privatemgr`, `Ticket`.`content_dispo`, `Ticket`.`hidemenu`, `Ticket`.`class_key`, `Ticket`.`context_key`, `Ticket`.`content_type`, `Ticket`.`uri`, `Ticket`.`uri_override`, `Ticket`.`hide_children_in_tree`, `Ticket`.`show_in_tree`, `Ticket`.`properties`, `Ticket`.`ext_category`, `Ticket`.`ext_author`, `Ticket`.`ext_moderation`, `Ticket`.`ext_vip_time`, `Ticket`.`ext_hot_time`, `Ticket`.`ext_vip`, `Ticket`.`ext_hot`, `Vote`.`value` as `vote`, COUNT(`Star`.`id`) as `star`, COUNT(DISTINCT Comment.id) as Comment.comments FROM `modx_site_content` AS `Ticket` LEFT JOIN `modx_site_content` `Section` ON `Section`.`id` = `Ticket`.`parent` LEFT JOIN `modx_users` `User` ON `User`.`id` = `Ticket`.`createdby` LEFT JOIN `modx_user_attributes` `Profile` ON `Profile`.`internalKey` = `User`.`id` LEFT JOIN `modx_tickets_votes` `Vote` ON `Vote`.`id` = `Ticket`.`id` AND `Vote`.`class` = "Ticket" AND `Vote`.`createdby` = 2 LEFT JOIN `modx_tickets_stars` `Star` ON `Star`.`id` = `Ticket`.`id` AND `Star`.`class` = "Ticket" AND `Star`.`createdby` = 2 LEFT JOIN `modx_tickets_threads` `Thread` ON Thread.resource=Ticket.id LEFT JOIN `modx_tickets_comments` `Comment` ON Comment.thread=Thread.id WHERE  ( `Ticket`.`class_key` = 'Ticket' AND `Comment`.`comments` >= '10' AND `Ticket`.`parent` IN (364375,364451,364452,364453,364454,364455,364456,364457,364458,364459) AND `Ticket`.`published` = 1 AND `Ticket`.`deleted` = 0 )  GROUP BY Ticket.id ORDER BY comments DESC LIMIT 10 "
0.0002470: Could not process query, error #1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.comments FROM `modx_site_content` AS `Ticket` LEFT JOIN `modx_site_content` `Se' at line 1
0.0000029: Returning processed chunks
0.0016201: Total time
6 291 456: Memory usage

Василий Столейков

29 июня 2016, 14:02

Всё-таки пришлось делать костылём, как я и боялся…
Получается сейчас у меня выбираются все (&limit=`0`) тикеты, а показываются только те что подпадают под условие в самом чанке, что не совсем производительно…
Принцип сейчас такой:

Перед вызовом сниппета:

{$_modx->setPlaceholder('total_hot',0)}

В чанке:

{set $total = $_modx->getPlaceholder('total_hot')}
{if $comments >= 10}
	... // содержимое чанка
	{$_modx->setPlaceholder('total_hot',$total+1)}
{/if}

После сниппета:

Всего лучших: {$_modx->getPlaceholder('total_hot')}

Но как решить проблему с пагинацией я ещё не представляю себе…

Василий Столейков

29 июня 2016, 14:06

Ещё я думаю как можно записывать количество комментариев в ТВ или прямо в расширенные поля таблицы. Тогда не нужно ничего на лету подсчитывать — все данные будут уже у ресурса и все проблемы решаться.

Вот только я пока не знаю в какую сторону копать?

Плагин на сохранение комментария?
Плагин на сохранение тикета?
Где-то там уже есть готовое количество комментариев в переменной?
Или нужно самому подсчитывать из таблицы комментариев по ветке и по ресурсу (2 запроса)?

Авторизуйтесь или зарегистрируйтесь, чтобы оставлять комментарии.

Как выбрать ЛУЧШИЕ где комментов >= 10

Комментарии: 11

Здесь упомянуты