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);
}
$config_path = __DIR__ . '/config.json';
$handle = @fopen($config_path, 'r');
if ($handle === false) {
throw new RuntimeException("Could not load config");
}
$config = fread($handle, filesize($config_path));
fclose($handle);
$config = json_decode($config);
$last_json_error = json_last_error();
if ($last_json_error !== JSON_ERROR_NONE) {
throw new RuntimeException("Could not parse config - JSON error detected");
}
// timezones are fun
date_default_timezone_set('America/Phoenix');
// configure the db connections holder
$db = new Aura\Sql\ConnectionLocator();
$db->setDefault(function () use($config) {
$connection = $config->database->slave;
return new Aura\Sql\ExtendedPdo("mysql:host={$connection->host}", $connection->user, $connection->password);
});
$db->setWrite('master', function () use($config) {
$connection = $config->database->master;
return new Aura\Sql\ExtendedPdo("mysql:host={$connection->host}", $connection->user, $connection->password);
});
$db->setRead('slave', function () use($config) {
$connection = $config->database->slave;
$pdo = new Aura\Sql\ExtendedPdo("mysql:host={$connection->host}", $connection->user, $connection->password);
$profiler = new Aura\Sql\Profiler();
$profiler->setActive(true);
$pdo->setProfiler($profiler);
return $pdo;
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);
}