/**
* @brief Finds all bookmarks, matching the filter
* @param $userid UserId
* @param IDb $db Database Interface
* @param int $offset offset
* @param string $sqlSortColumn result with this column
* @param string|array $filters filters can be: empty -> no filter, a string -> filter this, a string array -> filter for all strings
* @param bool $filterTagOnly true, filter affects only tags, else filter affects url, title and tags
* @param int $limit limit of items to return (default 10) if -1 or false then all items are returned
* @param bool $public check if only public bookmarks should be returned
* @param array $requestedAttributes select all the attributes that should be returned. default is * + tags
* @param string $tagFilterConjunction select wether the filterTagOnly should filter with an AND or an OR conjunction
* @return Collection of specified bookmarks
*/
public static function findBookmarks($userid, IDb $db, $offset, $sqlSortColumn, $filters, $filterTagOnly, $limit = 10, $public = false, $requestedAttributes = null, $tagFilterConjunction = "and")
{
$CONFIG_DBTYPE = \OCP\Config::getSystemValue('dbtype', 'sqlite');
if (is_string($filters)) {
$filters = array($filters);
}
$toSelect = '*';
$tableAttributes = array('id', 'url', 'title', 'user_id', 'description', 'public', 'added', 'lastmodified', 'clickcount');
$returnTags = true;
if ($requestedAttributes != null) {
$key = array_search('tags', $requestedAttributes);
if ($key == false) {
$returnTags = false;
} else {
unset($requestedAttributes[$key]);
}
$toSelect = implode(",", array_intersect($tableAttributes, $requestedAttributes));
}
if ($CONFIG_DBTYPE == 'pgsql') {
$sql = "SELECT " . $toSelect . " FROM (SELECT *, (select array_to_string(array_agg(`tag`),',')\n\t\t\t\tfrom `*PREFIX*bookmarks_tags` where `bookmark_id` = `b2`.`id`) as `tags`\n\t\t\t\tFROM `*PREFIX*bookmarks` `b2`\n\t\t\t\tWHERE `user_id` = ? ) as `b` WHERE true ";
} else {
$sql = "SELECT " . $toSelect . ", (SELECT GROUP_CONCAT(`tag`) from `*PREFIX*bookmarks_tags`\n\t\t\t\tWHERE `bookmark_id` = `b`.`id`) as `tags`\n\t\t\t\tFROM `*PREFIX*bookmarks` `b`\n\t\t\t\tWHERE `user_id` = ? ";
}
$params = array($userid);
if ($public) {
$sql .= ' AND public = 1 ';
}
if (count($filters) > 0) {
Bookmarks::findBookmarksBuildFilter($sql, $params, $filters, $filterTagOnly, $tagFilterConjunction, $CONFIG_DBTYPE);
}
if (!in_array($sqlSortColumn, $tableAttributes)) {
$sqlSortColumn = 'lastmodified';
}
$sql .= " ORDER BY " . $sqlSortColumn . " DESC ";
if ($limit == -1 || $limit === false) {
$limit = null;
$offset = null;
}
$query = $db->prepareQuery($sql, $limit, $offset);
$results = $query->execute($params)->fetchAll();
$bookmarks = array();
foreach ($results as $result) {
if ($returnTags) {
$result['tags'] = explode(',', $result['tags']);
} else {
unset($result['tags']);
}
$bookmarks[] = $result;
}
return $bookmarks;
}
