public static function find(array $filter)
{
if (!isset($filter['search']) || !isset($filter['values'])) {
return null;
}
parent::baseFilter($filter, $id, $limit, $page, $sort);
if (isset($filter['fields'])) {
$fields = parent::filterFields($filter['fields']);
$cols = array_map(function ($k) {
return 'posts.' . $k . ' AS ' . $k;
}, explode(',', $fields));
$fields = implode(',', $cols);
} else {
$fields = 'posts.body AS body, posts.summary AS summary';
}
# TODO breadrumbs
# https://sqlite.org/lang_with.html
# https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree?rq=1
#p.title AS parent_title, p.status AS parent_status,
#p.summary AS parent_summary,
#r.title AS root_title, r.status AS root_status,
#r.summary AS root_summary,
#LFET JOIN posts AS p ON posts.parent_id = p.id
#LFET JOIN posts AS r ON posts.root_id = r.id
# GROUP_CONCAT(parent.parent_id AS bread_id, ',')
$params = array();
$sql = "SELECT posts.id AS id, posts.title AS title, \n\t\tposts.slug AS slug,\n\t\tposts.root_id AS root_id, posts.parent_id AS parent_id, \n\t\tposts.status AS status, posts.created_at AS created_at, \n\t\tposts.updated_at AS updated_at,\t\n\t\tposts.reply_count AS reply_count, \n\t\tposts.reply_at AS reply_at, posts.user_id AS user_id, \n\t\tCOALESCE( u.display, u.username, 'Anonymous' ) AS author,\n\t\tu.username AS username, {$fields}\n\t\t\n\t\tFROM posts \n\t\tJOIN post_family AS parent ON posts.id = parent.child_id \n\t\tJOIN post_family AS family ON parent.child_id = family.child_id\n\t\tLEFT JOIN posts AS p on parent.parent_id = p.id \n\t\t\n\t\tLEFT JOIN users AS u ON posts.user_id = u.id ";
if ($id > 0) {
$sql .= 'WHERE posts.parent_id = :id ';
$params[':id'] = $id;
} else {
$sql .= 'WHERE posts.parent_id = posts.id ';
}
$params[':limit'] = $limit;
$params[':offset'] = ($page - 1) * $limit;
$sql .= ' LIMIT :limit OFFSET :offset;';
if ($id > 0) {
return parent::query($sql, $params, 'class');
}
return parent::query($sql, $params, 'class');
}
