public function getMembersWithinSingleCategory($catId)
{
$r = \Cache::get($catId);
if ($r === null) {
$q = "SELECT * FROM (\n SELECT members.id, members.name, avatar, social_media.written_at \n FROM members ";
$q .= "INNER JOIN member_categories ON members.id = member_categories.member_id AND category_id = " . intval($catId) . " \n LEFT JOIN member_social_ids AS msi ON msi.member_id = members.id AND msi.primary_avatar = 1 AND msi.disabled = 0 \n LEFT JOIN social_media ON (social_media.member_id = members.id) \n ORDER BY social_media.written_at DESC \n ) AS tmp_table \n GROUP BY tmp_table.id \n ORDER BY tmp_table.written_at DESC";
$r = DB::select($q);
\Cache::put($catId, $r, 60);
}
return $r;
}
public function getSocialMediaWithMemberIds(array $memberArr, $socialMediaId = false, $offset = 0, $limit = 2)
{
if (count($memberArr) == 0) {
return array();
}
//use member ids as cache keys
$idArr = array_map(function ($obj) {
return $obj->id;
}, $memberArr);
sort($idArr);
$idStr = implode("_", $idArr);
// add additional params to make cache key
$key = $idStr . "_" . intval($socialMediaId) . "_" . $offset . "_" . $limit;
$memberKey = 'member_' . $key;
$contentKey = 'content_' . $key;
$newMemberArr = \Cache::get($memberKey);
$contentArr = \Cache::get($contentKey);
if ($newMemberArr !== null && $contentArr != null) {
return array($newMemberArr, $contentArr);
}
$contentArr = array();
foreach ($memberArr as $obj) {
$q = "SELECT * FROM (";
$q .= "SELECT social_media.id, social_media.member_id, written_at, social_media.member_social_id, ";
$q .= "social_id, text, media_url, media_height ";
$q .= "media_width, link, source ";
$q .= "FROM social_media ";
$q .= "INNER JOIN member_social_ids ON ";
$q .= "(member_social_ids.member_id = social_media.member_id ";
$q .= "AND ";
$q .= "member_social_ids.social_site = social_media.source) ";
$q .= "WHERE ";
$q .= "social_media.member_id = '" . (int) $obj->id . "' ";
$q .= "AND ";
$q .= "social_media.unpublish = '0' ";
$q .= "AND ";
$q .= "member_social_ids.disabled = 0 ";
if ($socialMediaId) {
$q .= "AND ";
$q .= "social_media.id < {$socialMediaId} ";
}
$q .= "ORDER BY social_media.id DESC ";
$q .= ") AS tmp_table ";
$q .= "GROUP BY tmp_table.id ";
$q .= "ORDER BY tmp_table.id DESC ";
$q .= "LIMIT {$limit} ";
$q .= "OFFSET {$offset}";
$r = DB::select($q);
// initialize array for ordering members by social media date
$mostRecentMediaDateArr[$obj->id] = 0;
foreach ($r as $i => $rowObj) {
// format links
$inReplyToText = '';
$text = is_object($rowObj) ? $rowObj->text : '';
preg_match("~<reply><(.*?)</reply>~is", $text, $arr);
if (isset($arr[0])) {
$inReplyToText = $arr[0];
$text = str_replace($inReplyToText, "", $text);
}
// TODO move to social media entity?
$text = Twitter::setSource($rowObj->source)->linkify($text);
$text = html_entity_decode($inReplyToText) . " " . $text;
$r[$i]->text = $text;
// set age of post
$age = $this->getAge($rowObj->written_at);
// . "| " . $rowObj->written_at . " | ";
$r[$i]->age = $age;
// members with most recent social media are to appear first
// set most recent social_media unixtime for member
$ut = strtotime($rowObj->written_at);
if ($mostRecentMediaDateArr[$rowObj->member_id] < $ut) {
$mostRecentMediaDateArr[$rowObj->member_id] = $ut;
}
}
// TODO use SocialMediaEntity
$contentArr[$obj->id] = $r;
}
// sort members based on most recent social media
arsort($mostRecentMediaDateArr);
$newMemberArr = [];
foreach ($mostRecentMediaDateArr as $memberId => $ut) {
foreach ($memberArr as $obj) {
if ($obj->id == $memberId) {
$newMemberArr[] = $obj;
}
}
}
\Cache::put($memberKey, $newMemberArr, 10);
\Cache::put($contentKey, $contentArr, 10);
return array($newMemberArr, $contentArr);
}
