public function getWaterfalls($limit = null, $offset = 0) { $query = "\n SELECT `waterfall`.`id`, `waterfall`.`name`, `waterfall`.`alias`,\n `watercourse`.`name` AS `watercourse`, `watercourse`.`alias` AS `watercourse_alias`\n FROM `jpemeric_waterfall`.`waterfall`\n INNER JOIN `jpemeric_waterfall`.`watercourse` ON `waterfall`.`watercourse` = `watercourse`.`id`\n WHERE `is_public` = :public\n ORDER BY `name`, `watercourse`"; if ($limit != null) { $query .= "\n LIMIT {$offset}, {$limit}"; } $bindings = ['public' => 1]; return $this->connections->getRead()->fetchAll($query, $bindings); }
public function getActiveLogs($limit = null, $offset = 0) { $query = "\n SELECT `id`, `title`, `alias`, `date`, `publish_date`, `introduction`\n FROM `jpemeric_waterfall`.`log`\n WHERE `is_public` = :public\n ORDER BY `date` DESC"; if ($limit != null) { $query .= "\n LIMIT {$offset}, {$limit}"; } $bindings = ['public' => 1]; return $this->connections->getRead()->fetchAll($query, $bindings); }
public function getTagsForPost($post) { $query = "\n SELECT `tag`.*\n FROM `jpemeric_blog`.`tag`\n INNER JOIN `jpemeric_blog`.`ptlink` ON `ptlink`.`tag_id` = `tag`.`id` AND `ptlink`.`post_id` = :post\n ORDER BY `tag`"; $bindings = ['post' => $post]; return $this->connections->getRead()->fetchAll($query, $bindings); }
/** * @param string $type * @param string $value * * @return array|false */ public function findByType($type, $value = '') { $query = "\n SELECT `title`, `content`, `image`\n FROM `jpemeric_blog`.`introduction`\n WHERE `type` = :type AND `value` = :value\n LIMIT 1"; $bindings = ['type' => $type, 'value' => $value]; return $this->connections->getRead()->fetchOne($query, $bindings); }
/** * @param integer $post * * @return array|false */ public function getSeriesForPost($post) { $query = "\n SELECT `series`.`title` AS `series_title`, `series`.`description` AS `series_description`,\n `post`.`id` AS `post`, `post`.`title`, `post`.`category`, `post`.`path`\n FROM `jpemeric_blog`.`series`\n INNER JOIN `jpemeric_blog`.`series_post` ON `series_post`.`series` = `series`.`id`\n INNER JOIN `jpemeric_blog`.`post` ON `post`.`id` = `series_post`.`post` AND\n `post`.`display` = :is_active\n WHERE `series`.`id` = (\n SELECT `series`\n FROM `jpemeric_blog`.`series_post`\n WHERE `post` = :lookup_post\n LIMIT 1)\n ORDER BY `series_post`.`order`"; $bindings = ['is_active' => 1, 'lookup_post' => $post]; return $this->connections->getRead()->fetchAll($query, $bindings); }
public function getActivePostsByRelatedTags($post, $limit = 4) { $query = "\n SELECT `id`, `title`, `path`, `date`, `body`, `category`, COUNT(1) AS `count`\n FROM `jpemeric_blog`.`post`\n INNER JOIN `jpemeric_blog`.`ptlink` ON `ptlink`.`post_id` = `post`.`id` AND\n `ptlink`.`tag_id` IN (\n SELECT `id`\n FROM `jpemeric_blog`.`tag`\n INNER JOIN `jpemeric_blog`.`ptlink` ON `ptlink`.`tag_id` = `tag`.`id` AND\n `ptlink`.`post_id` = :post)\n WHERE `id` <> :post AND `id` NOT IN (\n SELECT `post`\n FROM `jpemeric_blog`.`series_post`\n WHERE `series` = (\n SELECT `series`\n FROM `jpemeric_blog`.`series_post`\n WHERE `post` = :post\n )) AND `display` = :is_active\n GROUP BY `id`\n ORDER BY `count` DESC\n LIMIT {$limit}"; $bindings = ['post' => $post, 'is_active' => 1]; return $this->connections->getRead()->fetchAll($query, $bindings); }
public function getCompanionList() { $query = "\n SELECT `companion`.`name`, `companion`.`alias`, COUNT(1) AS `count`\n FROM `jpemeric_waterfall`.`companion`\n INNER JOIN `jpemeric_waterfall`.`log_companion_map` ON `log_companion_map`.`companion` = `companion`.`id`\n INNER JOIN `jpemeric_waterfall`.`log` ON `log`.`id` = `log_companion_map`.`log` AND\n `log`.`is_public` = :public\n GROUP BY `alias`\n ORDER BY `name`"; $bindings = ['public' => 1]; return $this->connections->getRead()->fetchAll($query, $bindings); }
public function getWatercourseList() { $query = "\n SELECT `sum_table`.`name`, `sum_table`.`alias`, SUM(`count`) AS `count`\n FROM ((\n SELECT `watercourse`.`name`, `watercourse`.`alias`, `parent_count`.`count`\n FROM (\n SELECT COUNT(1) AS `count`, `parent` AS `id`\n FROM `jpemeric_waterfall`.`watercourse`\n INNER JOIN `jpemeric_waterfall`.`waterfall` ON `waterfall`.`watercourse` = `watercourse`.`id` AND\n `waterfall`.`is_public` = :public\n WHERE `watercourse`.`parent` <> :no_parent\n GROUP BY `watercourse`.`id`\n ) AS `parent_count`\n INNER JOIN `jpemeric_waterfall`.`watercourse` ON `watercourse`.`id` = `parent_count`.`id` AND\n `watercourse`.`has_page` = :has_page\n ) UNION ALL (\n SELECT `watercourse`.`name`, `watercourse`.`alias`, COUNT(1) AS `count`\n FROM `jpemeric_waterfall`.`watercourse`\n INNER JOIN `jpemeric_waterfall`.`waterfall` ON `waterfall`.`watercourse` = `watercourse`.`id` AND\n `waterfall`.`is_public` = :public\n WHERE `watercourse`.`parent` = :no_parent AND `watercourse`.`has_page` = :has_page\n GROUP BY `watercourse`.`id`\n )) AS `sum_table`\n GROUP BY `alias`\n ORDER BY `name`"; $bindings = ['public' => 1, 'no_parent' => 0, 'has_page' => 1]; return $this->connections->getRead()->fetchAll($query, $bindings); }