/** * * @param int $perSize 每次迭代的数据条数 * @param bool $withRelations 是否带上关联查询 * @param int $limit 总条数限制 */ public function __construct($perSize = 100, $withRelations = true, $limit = 0) { $this->post = new Post(); $this->perSize = $perSize; $this->total_items = $this->post->count(); if ($limit > 0) { $this->total_items = max($this->total_items, $limit); } $this->postsTableName = $this->post->getSource(); $this->dbConnection = $this->post->getReadConnection(); $this->textsTableName = with(new Texts())->getSource(); $this->votesTableName = with(new Votes())->getSource(); $this->tagsPostsTableName = with(new TagsPosts())->getSource(); $this->tagsTableName = with(new Tags())->getSource(); $this->categoriesTableName = with(new CategoriesPosts())->getSource(); $this->endPosition = floor($this->total_items / $this->perSize); if ($withRelations) { $this->sql = <<<SQL SELECT \tpost.*, \tvote.upVote, \tvote.downVote, \t(SELECT GROUP_CONCAT(`categoryId`) FROM `{$this->categoriesTableName}` WHERE postId=post.id) as categoryIds, \t(SELECT GROUP_CONCAT(`tagName`) FROM `{$this->tagsTableName}` WHERE id IN (SELECT `tagId` FROM `{$this->tagsPostsTableName}` WHERE `postId`=post.id)) as tagNames, text.content FROM `{$this->postsTableName}` as post LEFT JOIN `{$this->textsTableName}` as text \tON text.postId=post.id LEFT JOIN `{$this->votesTableName}` as vote ON vote.postId=post.id SQL; } else { $this->sql = "SELECT * FROM {$this->postsTableName}"; } }
public function persistAction($params) { $counterRank = new CounterRankUtil(); $counterRank = $counterRank->getCounterRank('posts'); $post = new Post(); $count = 0; $tableName = $post->getSource(); foreach ($counterRank->getIterator(100, CounterIterator::PERSIST_WITH_DELETING) as $items) { $values = ''; $count += count($items); $ids = ''; foreach ($items as $post_id => $heat) { if ($ids != '') { $ids .= ','; } $ids .= $post_id; $values .= " WHEN id={$post_id} THEN `count`+{$heat} "; // $values .= "({$post_id}, {$heat}, '', 'private', '', 0)"; } $sql = <<<SQL UPDATE {$tableName} SET `count` = CASE {$values} ELSE `count` END WHERE `id` IN({$ids}) SQL; $post->getWriteConnection()->execute($sql); } $this->output->writelnComment('Done! Persist ' . $count . ' items;'); }