aboutsummaryrefslogtreecommitdiffstats
path: root/include/items.php
diff options
context:
space:
mode:
Diffstat (limited to 'include/items.php')
-rw-r--r--include/items.php457
1 files changed, 234 insertions, 223 deletions
diff --git a/include/items.php b/include/items.php
index 55d768e28..b80c5672b 100644
--- a/include/items.php
+++ b/include/items.php
@@ -4802,19 +4802,19 @@ function items_fetch($arr,$channel = null,$observer_hash = null,$client_mode = C
return $items;
}
-function webpage_to_namespace($webpage) {
+function item_type_to_namespace($item_type) {
- if($webpage == ITEM_TYPE_WEBPAGE)
+ if($item_type == ITEM_TYPE_WEBPAGE)
$page_type = 'WEBPAGE';
- elseif($webpage == ITEM_TYPE_BLOCK)
+ elseif($item_type == ITEM_TYPE_BLOCK)
$page_type = 'BUILDBLOCK';
- elseif($webpage == ITEM_TYPE_PDL)
+ elseif($item_type == ITEM_TYPE_PDL)
$page_type = 'PDL';
- elseif($webpage == ITEM_TYPE_CARD)
+ elseif($item_type == ITEM_TYPE_CARD)
$page_type = 'CARD';
- elseif($webpage == ITEM_TYPE_ARTICLE)
+ elseif($item_type == ITEM_TYPE_ARTICLE)
$page_type = 'ARTICLE';
- elseif($webpage == ITEM_TYPE_DOC)
+ elseif($item_type == ITEM_TYPE_DOC)
$page_type = 'docfile';
else
$page_type = 'unknown';
@@ -4823,12 +4823,12 @@ function webpage_to_namespace($webpage) {
}
-function update_remote_id($channel,$post_id,$webpage,$pagetitle,$namespace,$remote_id,$mid) {
+function update_remote_id($channel,$post_id,$item_type,$pagetitle,$namespace,$remote_id,$mid) {
if(! intval($post_id))
return;
- $page_type = webpage_to_namespace($webpage);
+ $page_type = item_type_to_namespace($item_type);
if($page_type == 'unknown' && $namespace && $remote_id) {
$page_type = $namespace;
@@ -5361,250 +5361,238 @@ function set_activity_mid($string) {
}
/**
- * @brief returns SQL which counts activities for an item and
- * if there is an observer also count activities authored by observer.
- * @param string $prefix (optional)
- */
-
-function item_activity_sql($prefix = 'c') {
- $sql = '';
- $observer = get_observer_hash();
-
- $thread_allow = ((local_channel()) ? PConfig::Get(local_channel(), 'system', 'thread_allow', true) : Config::Get('system', 'thread_allow', true));
-
- if ($observer) {
- $sql = <<<SQL
- COUNT(CASE WHEN $prefix.verb = 'Like' AND $prefix.author_xchan = '$observer' THEN 1 END) AS observer_liked,
- COUNT(CASE WHEN $prefix.verb = 'Dislike' AND $prefix.author_xchan = '$observer' THEN 1 END) AS observer_disliked,
- COUNT(CASE WHEN $prefix.verb = 'Announce' AND $prefix.author_xchan = '$observer' THEN 1 END) AS observer_announced,
- COUNT(CASE WHEN $prefix.verb = 'Accept' AND $prefix.author_xchan = '$observer' THEN 1 END) AS observer_accepted,
- COUNT(CASE WHEN $prefix.verb = 'Reject' AND $prefix.author_xchan = '$observer' THEN 1 END) AS observer_rejected,
- COUNT(CASE WHEN $prefix.verb = 'TentativeAccept' AND $prefix.author_xchan = '$observer' THEN 1 END) AS observer_tentativelyaccepted,
- SQL;
-
- if ($thread_allow) {
- $sql .= " COUNT(CASE WHEN $prefix.verb IN ('Create','Update') AND $prefix.author_xchan = '$observer' THEN 1 END) AS observer_commented, ";
- }
- }
-
-
- if ($thread_allow) {
- $sql .= "COUNT(CASE WHEN $prefix.verb IN ('Create','Update') THEN 1 END) AS comment_count,";
- }
-
- $sql .= <<<SQL
- COUNT(CASE WHEN $prefix.verb = 'Like' THEN 1 END) AS like_count,
- COUNT(CASE WHEN $prefix.verb = 'Dislike' THEN 1 END) AS dislike_count,
- COUNT(CASE WHEN $prefix.verb = 'Announce' THEN 1 END) AS announce_count,
- COUNT(CASE WHEN $prefix.verb = 'Accept' THEN 1 END) AS attendyes_count,
- COUNT(CASE WHEN $prefix.verb = 'Reject' THEN 1 END) AS attendno_count,
- COUNT(CASE WHEN $prefix.verb = 'TentativeAccept' THEN 1 END) AS attendmaybe_count
- SQL;
-
- return $sql;
-
-}
-
-/**
- * @brief returns an item by id belonging to local_channel()
+ * @brief returns an item by id and parent belonging to local_channel()
* including activity counts.
* @param int $id
+ * @param int $parent
*/
-function item_by_item_id(int $id): array
+function item_by_item_id(int $id, int $parent): array
{
- if (!$id) {
+ if (!$id && !$parent && !local_channel()) {
return [];
}
- $item_normal = item_normal();
- $item_normal_c = item_normal(prefix: 'c');
- $activity_sql = item_activity_sql('c');
+ $item_normal_sql = item_normal();
+
+ $reaction = item_reaction_sql($parent);
+ $reaction_cte_sql = $reaction['cte'];
+ $reaction_select_sql = $reaction['select'];
+ $reaction_join_sql = $reaction['join'];
- $ret = q("SELECT item.*,
- $activity_sql
+ return q("WITH
+ $reaction_cte_sql
+ SELECT
+ *,
+ $reaction_select_sql
FROM item
- LEFT JOIN item c
- ON c.parent = item.parent
- AND c.item_thread_top = 0
- AND c.thr_parent = item.mid
- $item_normal_c
- WHERE item.id = $id
+ $reaction_join_sql
+ WHERE
+ item.id = %d
AND item.uid = %d
- $item_normal
- GROUP BY item.id",
+ AND item.verb IN ('Create', 'Update', 'EmojiReact')
+ $item_normal_sql",
+ intval($id),
intval(local_channel())
);
-
- return $ret;
}
+
/**
* @brief returns an array of items by ids
- * ATTENTION: no permissions for the pa are checked here!!!
- * Permissions MUST be checked by the function which returns the ids.
- * @param string $ids - a string with ids separated by comma
- * @param array $thr_parents (optional) - a string with thr_parent mids separated by comma
- * which will be included
- * @param string $permission_sql (optional) - SQL provided by item_permission_sql() from the calling module
+ * ATTENTION: no permissions for the parents are checked here!!!
+ * Permissions MUST be checked by the module which calls this function.
+ * @param array $parents
+ * @param null|array $thr_parents (optional) - thr_parent mids which will be included
+ * @param string $permission_sql (optional) - SQL as provided by item_permission_sql() from the calling module
* @param bool $blog_mode (optional) - if set to yes only the parent items will be returned
*/
-function items_by_parent_ids(string $ids, array $thr_parents = [], string $permission_sql = '', bool $blog_mode = false): array
+function items_by_parent_ids(array $parents, null|array $thr_parents = null, string $permission_sql = '', bool $blog_mode = false): array
{
- if (!$ids) {
+ if (!$parents) {
return [];
}
-
+ $ids = ids_to_querystr($parents, 'item_id');
$thread_allow = ((local_channel()) ? PConfig::Get(local_channel(), 'system', 'thread_allow', true) : Config::Get('system', 'thread_allow', true));
-
$item_normal_sql = item_normal();
- $activity_sql_cte = item_activity_sql_cte();
- $activity_sql_cte_sub = item_activity_sql_cte('sub');
+ $limit = $thread_allow ? 3 : 1000;
$thr_parent_sql = (($thread_allow) ? " AND item.thr_parent = item.parent_mid " : '');
-
if ($thr_parents && $thread_allow) {
+ $limit = 300;
$thr_parent_str = stringify_array($thr_parents, true);
$thr_parent_sql = " AND item.thr_parent IN (" . protect_sprintf($thr_parent_str) . ") ";
}
+ $reaction = item_reaction_sql($ids, $permission_sql, 'final_selection');
+ $reaction_cte_sql = $reaction['cte'];
+ $reaction_select_sql = $reaction['select'];
+ $reaction_join_sql = $reaction['join'];
+
if ($blog_mode) {
- $ret = q("SELECT item.*,
- $activity_sql_cte
- FROM item
- WHERE item.id IN (%s)
- $item_normal_sql
- $permission_sql",
- dbesc($ids)
- );
+ $q = <<<SQL
+ WITH
+ final_selection AS (
+ SELECT
+ item.*
+ FROM
+ item
+ WHERE
+ item.id IN ($ids)
+ ),
+
+ $reaction_cte_sql
+
+ SELECT
+ final_selection.*,
+ $reaction_select_sql
+ FROM final_selection
+ $reaction_join_sql
+ SQL;
+
+ return dbq(trim($q));
}
- else {
- $ret = q("WITH parents AS (
- SELECT item.*,
- 0 AS rn, -- this is required for union (equal amount of coulumns)
- $activity_sql_cte
+
+ $q = <<<SQL
+ WITH
+ parent_items AS (
+ SELECT
+ item.*,
+ 0 AS rn
FROM item
- WHERE item.id IN (%s)
- $item_normal_sql
+ WHERE
+ item.id IN ($ids)
+ ),
+
+ $reaction_cte_sql,
+
+ all_comments AS (
+ SELECT
+ item.*,
+ ROW_NUMBER() OVER (PARTITION BY item.parent ORDER BY item.created DESC) AS rn
+ FROM item
+ WHERE item.parent IN ($ids)
+ AND item.verb IN ('Create', 'Update', 'EmojiReact')
+ AND item.item_thread_top = 0
+ $thr_parent_sql
$permission_sql
- ),
- comments AS (
- SELECT sub.*,
- $activity_sql_cte_sub
- FROM (
- SELECT item.*,
- ROW_NUMBER() OVER (PARTITION BY item.parent ORDER BY item.created DESC) AS rn
- FROM item
- WHERE item.parent IN (%s)
- AND item.id != item.parent
- AND (
- item.verb NOT IN ('Like', 'Dislike', 'Announce', 'Accept', 'Reject', 'TentativeAccept')
- OR (item.verb = 'Announce' AND item.item_thread_top = 1)
- )
- $thr_parent_sql
- $item_normal_sql
- $permission_sql
- ) sub
- WHERE rn <= 100 -- number of comments we want to load
- )
- SELECT * FROM parents
+ $item_normal_sql
+ ),
+
+ final_selection AS (
+ SELECT * FROM parent_items
UNION ALL
- SELECT * FROM comments",
- dbesc($ids),
- dbesc($ids)
- );
- }
+ SELECT * FROM all_comments WHERE all_comments.rn <= $limit
+ )
+
+ SELECT
+ final_selection.*,
+ $reaction_select_sql
+ FROM final_selection
+ $reaction_join_sql
+ SQL;
- return $ret;
+ return dbq(trim($q));
}
/**
- * @brief returns SQL which counts activities for an item and
- * if there is an observer also count activities authored by observer.
- * @param string $prefix (optional)
+ * @brief prepare reaction sql for items_by_parent_ids()
+ * ATTENTION: no permissions for the pa are checked here!!!
+ * Permissions MUST be checked by the function which returns the ids.
+ * @param string $ids
+ * @param string $permission_sql (optional) - SQL provided by item_permission_sql()
+ * @param string $join_prefix (optional) - prefix for the join part defaults to 'item'
*/
-function item_activity_sql_cte($prefix = 'item'): string
+function item_reaction_sql(string $ids, string $permission_sql = '', string $join_prefix = 'item'): array
{
- $thread_allow = ((local_channel()) ? PConfig::Get(local_channel(), 'system', 'thread_allow', true) : Config::Get('system', 'thread_allow', true));
+ $item_normal_sql = item_normal();
$observer = get_observer_hash();
- $sql = '';
-
- if ($observer) {
- $observer_verbs = [
- 'Like' => 'observer_liked',
- 'Dislike' => 'observer_disliked',
- 'Announce' => 'observer_announced',
- 'Accept' => 'observer_accepted',
- 'Reject' => 'observer_rejected',
- 'TentativeAccept' => 'observer_tentativelyaccepted'
- ];
- foreach($observer_verbs as $k => $v) {
- if ($sql) {
- $sql .= ",\n";
- }
+ $verbs = [
+ 'like' => ['Like'],
+ 'dislike' => ['Dislike'],
+ 'announce' => ['Announce'],
+ 'accept' => ['Accept'],
+ 'reject' => ['Reject'],
+ 'tentativeaccept' => ['TentativeAccept']
+ ];
- $sql .= <<<SQL
- (SELECT COUNT(*) FROM item AS reaction
- WHERE reaction.parent = $prefix.parent AND reaction.verb = '$k' AND reaction.author_xchan = '$observer' AND reaction.item_thread_top = 0 AND reaction.thr_parent = $prefix.mid
- ) AS $v
- SQL;
- }
+ $thread_allow = ((local_channel()) ? PConfig::Get(local_channel(), 'system', 'thread_allow', true) : Config::Get('system', 'thread_allow', true));
- if ($thread_allow) {
- $sql .= ",\n";
- $sql .= <<<SQL
- (SELECT COUNT(*) FROM item AS reaction
- WHERE reaction.parent = $prefix.parent AND reaction.verb IN ('Create', 'Update') AND reaction.author_xchan = '$observer' AND reaction.item_thread_top = 0 AND reaction.thr_parent = $prefix.mid
- ) AS observer_commented
- SQL;
- }
+ if ($thread_allow) {
+ $verbs['comment'] = ['Create', 'Update', 'EmojiReact'];
}
- $verbs = [
- 'Like' => 'like_count',
- 'Dislike' => 'dislike_count',
- 'Announce' => 'announce_count',
- 'Accept' => 'attendyes_count',
- 'Reject' => 'attendno_count',
- 'TentativeAccept' => 'attendmaybe_count'
- ];
+ $cte = '';
+ $select = '';
+ $join = '';
foreach($verbs as $k => $v) {
- if ($sql) {
- $sql .= ",\n";
+
+ $observer_sql = "0 AS observer_{$k}_count";
+ if ($observer) {
+ $observer_sql = "COUNT(CASE WHEN item.author_xchan = '$observer' THEN 1 END) AS observer_{$k}_count";
+ }
+
+ $verbs_str = stringify_array($v);
+
+ if ($cte) {
+ $cte .= ",\n";
}
- $sql .= <<<SQL
- (SELECT COUNT(*) FROM item AS reaction
- WHERE reaction.parent = $prefix.parent AND reaction.verb = '$k' AND reaction.item_thread_top = 0 AND reaction.thr_parent = $prefix.mid
- ) AS $v
+ $cte .= <<<SQL
+ reaction_{$k} AS (
+ SELECT
+ item.thr_parent,
+ -- COUNT(DISTINCT item.author_xchan) AS {$k}_count, (should we prevent multiple reactions by the same author?)
+ COUNT(*) AS {$k}_count,
+ $observer_sql
+ FROM item
+ WHERE item.verb IN ($verbs_str)
+ AND item.item_thread_top = 0
+ AND item.parent IN ($ids)
+ $item_normal_sql
+ $permission_sql
+ GROUP BY item.thr_parent
+ )
SQL;
- }
- if ($thread_allow) {
- $sql .= ",\n";
- $sql .= <<<SQL
- (SELECT COUNT(*) FROM item AS reaction
- WHERE reaction.parent = $prefix.parent AND reaction.verb IN ('Create', 'Update') AND reaction.item_thread_top = 0 AND reaction.thr_parent = $prefix.mid
- ) AS comment_count
+ if ($select) {
+ $select .= ",\n";
+ }
+
+ $select .= <<<SQL
+ COALESCE(reaction_{$k}.{$k}_count, 0) AS {$k}_count,
+ COALESCE(reaction_{$k}.observer_{$k}_count, 0) AS observer_{$k}_count
+ SQL;
+
+ $join .= <<<SQL
+ LEFT JOIN reaction_{$k} ON reaction_{$k}.thr_parent = $join_prefix.mid
SQL;
+
}
- return $sql;
+ $ret['cte'] = $cte;
+ $ret['select'] = $select;
+ $ret['join'] = $join;
+
+ return $ret;
}
+
+
/**
* @brief returns an array of items by thr_parent mid of a parent
* @param string $mid
* @param int $parent
+ * @param int|null $offset
*/
-function items_by_thr_parent(string $mid, int $parent): array
+function items_by_thr_parent(string $mid, int $parent, int|null $offset = null): array
{
if (!$mid && !$parent) {
return [];
@@ -5614,63 +5602,75 @@ function items_by_thr_parent(string $mid, int $parent): array
intval($parent)
);
- $owner_uid = intval($parent_item[0]['uid']);
+ $order_sql = "ORDER BY item.created";
+ if (isset($offset)) {
+ $order_sql = "ORDER BY item.created DESC, item.received DESC LIMIT 3 OFFSET $offset";
+ }
- $item_normal = item_normal($owner_uid);
- $item_normal_c = item_normal($owner_uid, 'c');
- $activity_sql = item_activity_sql('c');
+ $owner_uid = intval($parent_item[0]['uid']);
+ $item_normal_sql = item_normal($owner_uid);
if (local_channel() === $owner_uid) {
- $ret = q(
- "SELECT item.*,
- $activity_sql
+ $reaction = item_reaction_sql($parent);
+ $reaction_cte_sql = $reaction['cte'];
+ $reaction_select_sql = $reaction['select'];
+ $reaction_join_sql = $reaction['join'];
+
+ $ret = q("WITH
+ $reaction_cte_sql
+ SELECT
+ item.*,
+ $reaction_select_sql
FROM item
- LEFT JOIN item c ON c.parent = item.parent
- AND c.item_thread_top = 0
- AND c.thr_parent = item.mid
- $item_normal_c
- WHERE item.thr_parent = '%s'
+ $reaction_join_sql
+ WHERE
+ item.thr_parent = '%s'
AND item.uid = %d
- AND item.parent = %d
- AND item.verb NOT IN ('Like', 'Dislike', 'Announce', 'Accept', 'Reject', 'TentativeAccept')
+ AND item.verb IN ('Create', 'Update', 'EmojiReact')
AND item.item_thread_top = 0
- $item_normal
- GROUP BY item.id
- ORDER BY item.created",
+ $item_normal_sql
+ $order_sql",
dbesc($mid),
- intval(local_channel()),
- intval($parent)
+ intval($owner_uid)
);
}
-
- if (!$ret) {
+ else {
$observer_hash = get_observer_hash();
- $sql_extra = item_permissions_sql($owner_uid, $observer_hash);
-
- $ret = q(
- "SELECT item.*,
- $activity_sql
+ $permission_sql = item_permissions_sql($owner_uid, $observer_hash);
+
+ $reaction = item_reaction_sql($parent, $permission_sql);
+ $reaction_cte_sql = $reaction['cte'];
+ $reaction_select_sql = $reaction['select'];
+ $reaction_join_sql = $reaction['join'];
+
+ $ret = q("WITH
+ $reaction_cte_sql
+ SELECT
+ item.*,
+ $reaction_select_sql
FROM item
- LEFT JOIN item c ON c.parent = item.parent
- AND c.item_thread_top = 0
- AND c.thr_parent = item.mid
- $item_normal_c
- WHERE item.thr_parent = '%s'
+ $reaction_join_sql
+ WHERE
+ item.thr_parent = '%s'
AND item.uid = %d
- AND item.verb NOT IN ('Like', 'Dislike', 'Announce', 'Accept', 'Reject', 'TentativeAccept')
+ AND item.verb IN ('Create', 'Update', 'EmojiReact')
AND item.item_thread_top = 0
- $sql_extra
- $item_normal
- GROUP BY item.id
- ORDER BY item.created",
+ $permission_sql
+ $item_normal_sql
+ $order_sql",
dbesc($mid),
intval($owner_uid)
);
}
+ if (isset($offset)) {
+ $ret = array_reverse($ret);
+ }
+
return $ret;
}
+
/**
* @brief returns an array of xchan entries (partly) for activities of an item by mid of a parent.
* Also checks if observer is allowed to add activities to the item.
@@ -5702,6 +5702,7 @@ function item_activity_xchans(string $mid, int $parent, string $verb): array
AND item.verb = '%s'
AND item.item_thread_top = 0
$item_normal
+ -- GROUP BY item.author_xchan (should we prevent multiple reactions by the same author?)
ORDER BY item.created",
intval(local_channel()),
intval($parent),
@@ -5709,8 +5710,7 @@ function item_activity_xchans(string $mid, int $parent, string $verb): array
dbesc($verb)
);
}
-
- if (!$ret) {
+ else {
$sql_extra = item_permissions_sql($owner_uid, $observer_hash);
$ret = q("SELECT item.id, item.item_blocked, xchan.xchan_hash, xchan.xchan_name as name, xchan.xchan_url as url, xchan.xchan_photo_s as photo FROM item
@@ -5721,6 +5721,7 @@ function item_activity_xchans(string $mid, int $parent, string $verb): array
AND item.item_thread_top = 0
$sql_extra
$item_normal
+ -- GROUP BY item.author_xchan (should we prevent multiple reactions by the same author?)
ORDER BY item.created",
intval($owner_uid),
dbesc($mid),
@@ -5740,8 +5741,13 @@ function item_activity_xchans(string $mid, int $parent, string $verb): array
* @param array $item
*/
-function get_recursive_thr_parents(array $item): array
+function get_recursive_thr_parents(array $item): array|null
{
+ if ($item['id'] === $item['parent']) {
+ // This is a toplevel post, return null.
+ return null;
+ }
+
$thr_parents[] = $item['thr_parent'];
$mid = $item['thr_parent'];
@@ -5755,8 +5761,13 @@ function get_recursive_thr_parents(array $item): array
dbesc($mid)
);
+ if (!$x) {
+ break;
+ }
+
$mid = $x[0]['thr_parent'];
$thr_parents[] = $x[0]['thr_parent'];
+
$i++;
}
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-29 03:02:17 +0000