MySQL
0
Вклад в тег
SELECT `articles`.`id` AS `id`, `articles`.`title` AS `title`,
GROUP_CONCAT(tags.title) AS `tagsDb`, GROUP_CONCAT(tag_weights.tag_weight) AS `tagsWeight`
FROM `articles` LEFT JOIN `articles_tags` ON `articles`.`id` = `articles_tags`.`article_id`
LEFT JOIN `tags` ON `articles_tags`.`tag_id` = `tags`.`id`
LEFT JOIN (SELECT `articles_tags`.`tag_id` AS `tag_id`,
COUNT(`article_id`) AS `tag_weight` FROM `articles_tags` GROUP BY `tag_id`)
AS `tag_weights` ON `articles_tags`.`tag_id` = `tag_weights`.`tag_id` WHERE `articles`.`id` = 336$select = $sql->select('articles');
$select->where(array('articles.id = ?' => $id));
$select->join(
'articles_tags', 'articles.id = articles_tags.article_id',
array(),
$select::JOIN_LEFT
);
$select->join(
'tags', 'articles_tags.tag_id = tags.id',
array(),
$select::JOIN_LEFT
);
$subSelect = $sql->select('articles_tags');
$subSelect->columns(array(
'tag_id',
'tag_weight' => new Expression('COUNT(`article_id`)')
));
$subSelect->group('tag_id');
$select->join(
array('tag_weights' => $subSelect),
'articles_tags.tag_id = tag_weights.tag_id',
array(),
$select::JOIN_LEFT
);
$select->columns(array(
'id' => 'id',
'title' => 'title',
'text' => 'text',
'summary' => 'summary',
'categoryId' => 'categoryId',
'author' => 'author',
'created' => 'created',
'updated' => 'updated',
'published' => 'published',
'isPublished' => 'isPublished',
'version' => 'version',
'tagsDb' => new Expression('GROUP_CONCAT(tags.title)'),
'tagsWeight' => new Expression('GROUP_CONCAT(tag_weights.tag_weight)')
));id title tagsDb tagsWeight
336 sdaf tag1,tag2,tag3,qw 3,3,3,6