/**
* Get standardized post data given an SQL query (which can specify WHERE conditions, for example.)
*
* @param ETSQLQuery $sql The SQL query to use as a basis.
* @return array An array of posts and their details.
*/
public function getWithSQL($sql)
{
// likeActivityId: いいね済みの場合、ActivityId が存在する
// likeCnt: いいねボタンのカウント数（※ポイント数ではないので注意）
$sql->select("p.*")->select("m.memberId", "memberId")->select("m.username", "username")->select("m.account", "account")->select("m.email", "email")->select("m.avatarFormat", "avatarFormat")->select("m.preferences", "preferences")->select("em.memberId", "editMemberId")->select("em.username", "editMemberName")->select("dm.memberId", "deleteMemberId")->select("dm.username", "deleteMemberName")->select("m.lastActionTime", "lastActionTime")->select("m.lastActionDetail", "lastActionDetail")->select("GROUP_CONCAT(g.groupId)", "groups")->select("GROUP_CONCAT(g.name)", "groupNames")->select("count(b.activityId)", "likeCnt")->from("post p")->from("member m", "m.memberId=p.memberId", "left")->from("member em", "em.memberId=p.editMemberId", "left")->from("member dm", "dm.memberId=p.deleteMemberId", "left")->from("member_group mg", "m.memberId=mg.memberId", "left")->from("group g", "g.groupId=mg.groupId", "left")->from("activity b", "b.memberId=p.memberId AND b.type='like' AND b.fromMemberId is not null AND b.postId=p.postId AND b.conversationId=p.conversationId", "left")->groupBy("p.postId")->orderBy("p.time ASC");
$fromMemberId = ET::$session->userId;
if ($fromMemberId) {
// ログイン済みの場合 いいね済みを取得
$sql->select("a.activityId", "likeActivityId")->from("activity a", "a.memberId=p.memberId AND a.type='like' AND a.fromMemberId=:fromMemberId AND a.postId=p.postId AND a.conversationId=p.conversationId", "left")->bind(":fromMemberId", $fromMemberId);
}
$this->trigger("getPostsBefore", array($sql));
$result = $sql->exec();
// Loop through the results and compile them into an array of posts.
$posts = array();
while ($post = $result->nextRow()) {
// 2016/02 SWCユーザ名を設定するように変更対応
$post["username"] = SwcUtils::getUserName($post["memberId"]);
$post["editMemberName"] = SwcUtils::getUserName($post["editMemberId"]);
$post["deleteMemberName"] = SwcUtils::getUserName($post["deleteMemberId"]);
ET::memberModel()->expand($post);
$posts[] = $post;
}
$this->trigger("getPostsAfter", array(&$posts));
return $posts;
}
/**
* Get standardized post data given an SQL query (which can specify WHERE conditions, for example.)
*
* @param ETSQLQuery $sql The SQL query to use as a basis.
* @return array An array of posts and their details.
*/
public function getWithSQL($sql)
{
$sql->select("p.*")->select("m.memberId", "memberId")->select("m.username", "username")->select("m.account", "account")->select("m.avatarFormat", "avatarFormat")->select("em.memberId", "editMemberId")->select("em.username", "editMemberName")->select("dm.memberId", "deleteMemberId")->select("dm.username", "deleteMemberName")->select("m.lastActionTime", "lastActionTime")->select("m.lastActionDetail", "lastActionDetail")->select("GROUP_CONCAT(g.groupId)", "groups")->select("GROUP_CONCAT(g.name)", "groupNames")->from("post p")->from("member m", "m.memberId=p.memberId", "left")->from("member em", "em.memberId=p.editMemberId", "left")->from("member dm", "dm.memberId=p.deleteMemberId", "left")->from("member_group mg", "m.memberId=mg.memberId", "left")->from("group g", "g.groupId=mg.groupId", "left")->groupBy("p.postId")->orderBy("p.time ASC");
$result = $sql->exec();
// Loop through the results and compile them into an array of posts.
$posts = array();
while ($post = $result->nextRow()) {
$post["groups"] = array_combine(explode(",", $post["groups"]), explode(",", $post["groupNames"]));
$posts[] = $post;
}
return $posts;
}
/**
* Get standardized post data given an SQL query (which can specify WHERE conditions, for example.)
*
* @param ETSQLQuery $sql The SQL query to use as a basis.
* @return array An array of posts and their details.
*/
public function getWithSQL($sql)
{
$sql->select("p.*")->select("m.memberId", "memberId")->select("m.username", "username")->select("m.account", "account")->select("m.email", "email")->select("m.avatarFormat", "avatarFormat")->select("m.preferences", "preferences")->select("em.memberId", "editMemberId")->select("em.username", "editMemberName")->select("dm.memberId", "deleteMemberId")->select("dm.username", "deleteMemberName")->select("m.lastActionTime", "lastActionTime")->select("m.lastActionDetail", "lastActionDetail")->select("GROUP_CONCAT(g.groupId)", "groups")->select("GROUP_CONCAT(g.name)", "groupNames")->from("post p")->from("member m", "m.memberId=p.memberId", "left")->from("member em", "em.memberId=p.editMemberId", "left")->from("member dm", "dm.memberId=p.deleteMemberId", "left")->from("member_group mg", "m.memberId=mg.memberId", "left")->from("group g", "g.groupId=mg.groupId", "left")->groupBy("p.postId")->orderBy("p.time ASC");
$this->trigger("getPostsBefore", array($sql));
$result = $sql->exec();
// Loop through the results and compile them into an array of posts.
$posts = array();
while ($post = $result->nextRow()) {
ET::memberModel()->expand($post);
$posts[] = $post;
}
$this->trigger("getPostsAfter", array(&$posts));
return $posts;
}
/**
* Get standardized member data given an SQL query (which can specify WHERE conditions, for example.)
*
* @param ETSQLQuery $sql The SQL query to use as a basis.
* @return array An array of members and their details.
*/
public function getWithSQL($sql)
{
$sql->select("m.*")->select("GROUP_CONCAT(g.groupId) AS groups")->select("GROUP_CONCAT(g.name) AS groupNames")->select("BIT_OR(g.canSuspend) AS canSuspend")->from("member m")->from("member_group mg", "mg.memberId=m.memberId", "left")->from("group g", "g.groupId=mg.groupId", "left")->groupBy("m.memberId");
$members = $sql->exec()->allRows();
// Expand the member data.
foreach ($members as &$member) {
$this->expand($member);
}
return $members;
}
/**
* Get records given an SQL query (which can specify WHERE conditions, for example.)
*
* @param ETSQLQuery $sql The SQL query to use as a basis.
* @return array An array of records.
*/
public function getWithSQL($sql)
{
return $sql->select("*")->from($this->table)->exec()->allRows();
}