/** * * @param array $updates * @return type */ public static function setUpdatePostedFrom(&$updates) { if (!is_array($updates) || 0 === count($updates)) { return; } $users = []; foreach ($updates as &$update) { if (!isset($users[$update['user_id']])) { $users[$update['user_id']] = []; } $users[$update['user_id']][] =& $update; } $userIds = ArrayHelper::keyArray($users); if (0 === count($userIds)) { return; } $stmt = \CW::$app->db->executeQuery('SELECT id, username, profile_img_id FROM users WHERE id IN (' . ArrayHelper::getArrayToString($userIds, ',') . ')'); $result = $stmt->fetchAll(\PDO::FETCH_ASSOC); foreach ($result as $user) { $user['imgUrl'] = \models\User::getProfilePictureUrl($user['profile_img_id'], $user['id']); $user['username'] = htmlspecialchars($user['username']); $user['profileUrl'] = \models\User::getProfileUrl($user['id']); foreach ($users[$user['id']] as &$update) { $update['from'] = $user; } } }
public static function getComments($updateId, $page = null) { if (!is_numeric($updateId)) { return []; } $query = "SELECT *, " . (CW::$app->user->isLogged() ? "0 < (SELECT count(*) FROM `comment_upvoters` WHERE `comment_id` = `comments`.`id` AND `user_id` = " . CW::$app->user->identity->id . ") " : ' false ') . " `voted`, null ownerId, null ownerUsername from `comments` WHERE `update_id` = {$updateId} AND `reply_to` IS NULL ORDER BY `rate` DESC, `posted_on` ASC LIMIT " . self::COMMENT_LOAD_COUNT . " OFFSET " . ($page ? $page * self::COMMENT_LOAD_COUNT : 0); $stmt = \CW::$app->db->executeQuery($query); $result = $stmt->fetchAll(\PDO::FETCH_OBJ); $commentsCount = count($result); $_comments = []; $_topLevelComments = []; $_replies = []; for ($i = 0; $i < $commentsCount; $i++) { if (!isset($_comments[$result[$i]->user_id])) { $_comments[$result[$i]->user_id] = []; } $_comments[$result[$i]->user_id][] = $result[$i]; $query = "SELECT *, " . (CW::$app->user->isLogged() ? "0 < (SELECT count(*) FROM `comment_upvoters` WHERE `comment_id` = `comments`.`id` AND `user_id` = " . CW::$app->user->identity->id . ") " : ' false ') . " `voted` from `comments` WHERE `reply_to` = {$result[$i]->id} ORDER BY `posted_on` ASC LIMIT 1"; $stmt1 = \CW::$app->db->executeQuery($query); $_topLevelComments[$result[$i]->id] = $result[$i]; $replies = $stmt1->fetchAll(\PDO::FETCH_OBJ); $result[$i]->repliesCount = count($replies); $result[$i]->owner = ['id' => $result[$i]->ownerId, 'username' => $result[$i]->ownerUsername, 'profileUrl' => \models\User::getProfileUrl($result[$i]->ownerId)]; $result[$i]->content = htmlspecialchars($result[$i]->content); $result[$i]->postedAgo = BaseModel::getPostedAgoTime($result[$i]->posted_on); $result[$i]->voted = (bool) $result[$i]->voted; $repliesCount = count($replies); for ($j = 0; $j < $repliesCount; $j++) { if (!isset($_comments[$replies[$j]->user_id])) { $_comments[$replies[$j]->user_id] = []; } $_replies[$replies[$j]->reply_to] = $replies[$j]->id; $_comments[$replies[$j]->user_id][] = $replies[$j]; $replies[$j]->content = htmlspecialchars($replies[$j]->content); $replies[$j]->postedAgo = BaseModel::getPostedAgoTime($replies[$j]->posted_on); $replies[$j]->voted = (bool) $replies[$j]->voted; } $result[$i]->replies = $replies; } if (0 < count($_comments)) { $userIds = ArrayHelper::keyArray($_comments); $query = 'SELECT `id`, `username`, `profile_img_id` FROM `users` WHERE `id` IN (' . ArrayHelper::getArrayToString($userIds, ',') . ')'; $stmt = \CW::$app->db->executeQuery($query); $users = $stmt->fetchAll(\PDO::FETCH_ASSOC); $usersCount = count($users); for ($i = 0; $i < $usersCount; $i++) { $userComments = $_comments[$users[$i]['id']]; $c = count($userComments); for ($j = 0; $j < $c; $j++) { $commentJ = $userComments[$j]; $commentJ->owner = ['id' => $users[$i]['id'], 'username' => htmlspecialchars($users[$i]['username']), 'profileUrl' => \models\User::getProfileUrl($users[$i]['id']), 'pictureUrl' => User::getProfilePictureUrl($users[$i]['profile_img_id'], $users[$i]['id'])]; } } } if (0 < count($_replies)) { $a = []; foreach ($_replies as $replyTo => $replyId) { $__replies = $_topLevelComments[$replyTo]->replies; $last = $__replies[count($__replies) - 1]->posted_on; $a[] = "SELECT `reply_to` FROM `comments` WHERE `reply_to` = {$replyTo} AND `posted_on` > '{$last}' LIMIT 1"; } $q = ArrayHelper::getArrayToString($a, ' UNION ', function ($v) { return "({$v})"; }); $stmt = CW::$app->db->executeQuery($q); $_result = $stmt->fetchAll(\PDO::FETCH_ASSOC); foreach ($_result as $replyTo) { $_topLevelComments[$replyTo['reply_to']]->hasMore = true; } } if ($commentsCount === self::COMMENT_LOAD_COUNT) { $qq = "SELECT `id`, update_id, content FROM `comments` WHERE `update_id` = {$updateId} AND `reply_to` IS NULL ORDER BY `rate` DESC, `posted_on` DESC LIMIT 10 OFFSET " . ($page ? $page * self::COMMENT_LOAD_COUNT + self::COMMENT_LOAD_COUNT : self::COMMENT_LOAD_COUNT); $stmt = CW::$app->db->executeQuery($qq); $_result = $stmt->fetchAll(\PDO::FETCH_ASSOC); $hasMore = 0 < count($_result); } else { $hasMore = false; } return ['items' => $result, 'hasMore' => $hasMore]; }