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'); }