public static function getArchivePhoto($type, $filter, $sort, $limit, $offset, $count = false) { ///////////////////////////////////SELECT////////////////////////////////////// if ($count) { $query = (new Query())->select('COUNT(1)'); } else { $query = (new Query())->select(['p.id AS photo_id', 'p.src AS photo_src', 'p.name AS photo_name', 'u.id AS user_id', 'u.name AS user_name', 'l1.name AS photo_category', 'l2.name AS photo_subcategory', 'p.date AS photo_date']); if ($type == "photo_day") { $query->addSelect('bp.date AS glory'); } elseif ($type == "photo_week") { $query->addSelect(['bp.week_num AS week_num', 'bp.year AS year']); } elseif ($type == "photo_month") { $query->addSelect(['bp.month_num AS month_num', 'bp.year AS year']); } elseif ($type == "photo_year") { $query->addSelect('bp.year_num AS year'); } } ///////////////////////////////////FROM////////////////////////////////////// $query = self::getFromToQuery($type, $query); ///////////////////////////////////JOIN////////////////////////////////////// $query->innerJoin('photos p', 'bp.id_photo=p.id')->innerJoin('user u', 'p.id_author=u.id')->innerJoin('level_1 l1', 'p.id_level_1=l1.id')->leftJoin('level_2 l2', 'p.id_level_2=l2.id'); ///////////////////////////////////WHERE////////////////////////////////////// if ($filter) { $query->filterWhere(['=', 'p.id', $filter['photo_id']])->AndFilterWhere(['like', 'p.name', $filter['photo_name']])->AndFilterWhere(['like', 'u.name', $filter['user_name']])->AndFilterWhere(['like', 'l1.name', $filter['photo_category']])->AndFilterWhere(['like', 'l2.name', $filter['photo_subcategory']])->AndFilterWhere(['>=', 'p.date', $filter['date_min']])->AndFilterWhere(['<=', 'p.date', $filter['date_max']]); $query = self::getFilterGlory($filter['glory_min'], $filter['glory_max'], $type, $query); } ///////////////////////////////////LIMIT, OFFSET////////////////////////////////////// if (!$count) { if ($limit) { $query->limit($limit)->offset($offset); } ///////////////////////////////////ORDER BY////////////////////////////////////// if ($sort) { if ($sort == 'glory' || $sort == '-glory') { $sort = self::getSortGlory($sort, $type); $query->orderBy($sort); } else { $query = AdminCommon::getSortQuery($query, $sort); } } $photos = $query->all(); foreach ($photos as &$photo) { $photo['photo_src'] = '/img/photos/' . str_replace('-', '/', explode(' ', $photo['photo_date'])[0]) . '/s_' . $photo['photo_src']; $photo['photo_date'] = explode(" ", $photo['photo_date'])[0]; if ($photo['photo_subcategory'] == "") { $photo['photo_subcategory'] = "Немає підкатегорії"; } $photo['glory'] = self::generatePhotoGlory($photo, $type); } return $photos; } else { return $query->scalar(); } }
public static function getArticles($filter, $sort, $offset, $limit) { $query = (new Query())->select(' a.id AS article_id, a.name AS article_name, a.date AS article_date, a.thumbnail AS article_cover, a.rating_all AS article_rating, a.id AS article_id, u.id AS user_id, u.name AS user_name, l1.name AS category_name, ')->from('articles a')->innerJoin('user u', 'a.id_author=u.id')->innerJoin('level_1 l1', 'a.id_level_1=l1.id'); if ($filter) { self::getFilterArticles($filter, $query); } if ($sort) { AdminCommon::getSortQuery($query, $sort); } $articles = $query->limit($limit)->offset($offset)->all(); return $articles; }