From 7ba1416e6416b826017e483704a985547f39802f Mon Sep 17 00:00:00 2001 From: Michael Vogel Date: Fri, 8 Nov 2013 08:11:39 +0100 Subject: [PATCH] SQL: Query for showing group posts is improved, order by comment date now work again --- include/api.php | 13 ------------- include/onepoll.php | 1 - mod/content.php | 47 +++++++++------------------------------------ mod/network.php | 40 ++++++-------------------------------- mod/search.php | 12 ------------ 5 files changed, 15 insertions(+), 98 deletions(-) diff --git a/include/api.php b/include/api.php index 292d87f0f2..97f1435e2b 100644 --- a/include/api.php +++ b/include/api.php @@ -1068,19 +1068,6 @@ $myurl = str_replace('www.','',$myurl); $diasp_url = str_replace('/profile/','/u/',$myurl); -/* if (get_config('system','use_fulltext_engine')) - $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where (MATCH(`author-link`) AGAINST ('".'"%s"'."' in boolean mode) or MATCH(`tag`) AGAINST ('".'"%s"'."' in boolean mode) or MATCH(tag) AGAINST ('".'"%s"'."' in boolean mode))) ", - dbesc(protect_sprintf($myurl)), - dbesc(protect_sprintf($myurl)), - dbesc(protect_sprintf($diasp_url)) - ); - else - $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where ( `author-link` like '%s' or `tag` like '%s' or tag like '%s' )) ", - dbesc(protect_sprintf('%' . $myurl)), - dbesc(protect_sprintf('%' . $myurl . ']%')), - dbesc(protect_sprintf('%' . $diasp_url . ']%')) - ); -*/ $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where `author-link` IN ('https://%s', 'http://%s') OR `mention`)", dbesc(protect_sprintf($myurl)), dbesc(protect_sprintf($myurl)) diff --git a/include/onepoll.php b/include/onepoll.php index 12fab4c90c..2a7815fa52 100644 --- a/include/onepoll.php +++ b/include/onepoll.php @@ -423,7 +423,6 @@ function onepoll_run(&$argv, &$argc){ // If it seems to be a reply but a header couldn't be found take the last message with matching subject if(!x($datarray,'parent-uri') and $reply) { - //$r = q("SELECT `uri` , `parent-uri` FROM `item` WHERE MATCH (`title`) AGAINST ('".'"%s"'."' IN BOOLEAN MODE) AND `uid` = %d ORDER BY `created` DESC LIMIT 1", $r = q("SELECT `uri` , `parent-uri` FROM `item` WHERE `title` = \"%s\" AND `uid` = %d ORDER BY `created` DESC LIMIT 1", dbesc(protect_sprintf($datarray['title'])), intval($importer_uid)); diff --git a/mod/content.php b/mod/content.php index 6deee57a5b..f974eecdd1 100644 --- a/mod/content.php +++ b/mod/content.php @@ -173,23 +173,6 @@ function content_content(&$a, $update = 0) { if (get_config('system','only_tag_search')) $tag = true; - /*if (get_config('system','use_fulltext_engine')) { - if(strpos($search,'#') === 0) - $sql_extra .= sprintf(" AND (MATCH(tag) AGAINST ('%s' in boolean mode)) ", - dbesc(protect_sprintf($search)) - ); - else - $sql_extra .= sprintf(" AND (MATCH(`item`.`body`, `item`.`title`) AGAINST ('%s' in boolean mode)) ", - dbesc(protect_sprintf($search)), - dbesc(protect_sprintf($search)) - ); - } else { - $sql_extra .= sprintf(" AND ( `item`.`body` like '%s' OR `item`.`tag` like '%s' ) ", - dbesc(protect_sprintf('%' . $search . '%')), - dbesc(protect_sprintf('%]' . $search . '[%')) - ); - }*/ - if($tag) { //$sql_extra = sprintf(" AND `term`.`term` = '%s' AND `term`.`otype` = %d AND `term`.`type` = %d ", // dbesc(protect_sprintf($search)), intval(TERM_OBJ_POST), intval(TERM_HASHTAG)); @@ -216,18 +199,6 @@ function content_content(&$a, $update = 0) { $myurl = substr($myurl,strpos($myurl,'://')+3); $myurl = str_replace('www.','',$myurl); $diasp_url = str_replace('/profile/','/u/',$myurl); - /*if (get_config('system','use_fulltext_engine')) - $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from $sql_table where (MATCH(`author-link`, `tag`) AGAINST ('%s' in boolean mode) or MATCH(tag) AGAINST ('%s' in boolean mode))) ", - dbesc(protect_sprintf($myurl)), - dbesc(protect_sprintf($myurl)), - dbesc(protect_sprintf($diasp_url)) - ); - else - $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from $sql_table where ( `author-link` like '%s' or `tag` like '%s' or tag like '%s' )) ", - dbesc(protect_sprintf('%' . $myurl)), - dbesc(protect_sprintf('%' . $myurl . ']%')), - dbesc(protect_sprintf('%' . $diasp_url . ']%')) - );*/ $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where `author-link` IN ('https://%s', 'http://%s') OR `mention`)", dbesc(protect_sprintf($myurl)), @@ -395,7 +366,7 @@ function render_content(&$a, $items, $mode, $update, $preview = false) { // array with html for each thread (parent+comments) $threads = array(); $threadsid = -1; - + if($items && count($items)) { if($mode === 'network-new' || $mode === 'search' || $mode === 'community') { @@ -423,11 +394,11 @@ function render_content(&$a, $items, $mode, $update, $preview = false) { } else $nickname = $a->user['nickname']; - + // prevent private email from leaking. if($item['network'] === NETWORK_MAIL && local_user() != $item['uid']) continue; - + $profile_name = ((strlen($item['author-name'])) ? $item['author-name'] : $item['name']); if($item['author-link'] && (! $item['author-name'])) $profile_name = $item['author-link']; @@ -582,8 +553,8 @@ function render_content(&$a, $items, $mode, $update, $preview = false) { // We've already parsed out like/dislike for special treatment. We can ignore them now - if(((activity_match($item['verb'],ACTIVITY_LIKE)) - || (activity_match($item['verb'],ACTIVITY_DISLIKE))) + if(((activity_match($item['verb'],ACTIVITY_LIKE)) + || (activity_match($item['verb'],ACTIVITY_DISLIKE))) && ($item['id'] != $item['parent'])) continue; @@ -594,7 +565,7 @@ function render_content(&$a, $items, $mode, $update, $preview = false) { // (author collapsing is currently disabled) // If a single author has more than 3 consecutive top-level posts, squash the remaining ones. // If there are more than two comments, squash all but the last 2. - + if($toplevelpost) { $item_writeable = (($item['writable'] || $item['self']) ? true : false); @@ -603,7 +574,7 @@ function render_content(&$a, $items, $mode, $update, $preview = false) { $comments_collapsed = false; $comment_lastcollapsed = false; $comment_firstcollapsed = false; - + $threadsid++; $threads[$threadsid]['id'] = $item['item_id']; $threads[$threadsid]['private'] = $item['private']; @@ -666,7 +637,7 @@ function render_content(&$a, $items, $mode, $update, $preview = false) { $owner_photo = $a->page_contact['thumb']; $owner_name = $a->page_contact['name']; $template = $wallwall; - $commentww = 'ww'; + $commentww = 'ww'; } if((! $item['wall']) && $item['owner-link']) { @@ -684,7 +655,7 @@ function render_content(&$a, $items, $mode, $update, $preview = false) { // well that it's the same Bob Smith. // But it could be somebody else with the same name. It just isn't highly likely. - + $owner_url = $item['owner-link']; $owner_photo = $item['owner-avatar']; diff --git a/mod/network.php b/mod/network.php index ff79fb2cff..f4f191d715 100644 --- a/mod/network.php +++ b/mod/network.php @@ -599,7 +599,9 @@ function network_content(&$a, $update = 0) { info( t('Group is empty')); } - $sql_extra = " AND `item`.`parent` IN ( SELECT DISTINCT(`parent`) FROM `item` WHERE 1 $sql_options AND ( `contact-id` IN ( $contact_str ) OR `allow_gid` like '" . protect_sprintf('%<' . intval($group) . '>%') . "' ) and deleted = 0 ) "; + $sql_table = "`item` INNER JOIN (SELECT DISTINCT(`parent`) FROM `item` WHERE 1 $sql_options AND (`contact-id` IN ($contact_str) OR `allow_gid` like '".protect_sprintf('%<'.intval($group).'>%')."') and deleted = 0 ORDER BY `created` DESC) AS `temp1` ON item.parent = `temp1`.parent "; + $sql_extra = ""; + //$sql_extra = " AND `item`.`parent` IN ( SELECT DISTINCT(`parent`) FROM `item` WHERE 1 $sql_options AND ( `contact-id` IN ( $contact_str ) OR `allow_gid` like '" . protect_sprintf('%<' . intval($group) . '>%') . "' ) and deleted = 0 ) "; $o = '

' . t('Group: ') . $r[0]['name'] . '

' . $o; } elseif($cid) { @@ -641,7 +643,8 @@ function network_content(&$a, $update = 0) { $sql_extra2 = (($nouveau) ? '' : " AND `item`.`parent` = `item`.`id` "); $sql_extra3 = (($nouveau) ? '' : $sql_extra3); - $sql_order = "`item`.`received`"; + //$sql_order = "`item`.`received`"; + $sql_order = ""; if ($sql_table == "") $sql_table = "`item`"; @@ -657,23 +660,6 @@ function network_content(&$a, $update = 0) { if (get_config('system','only_tag_search')) $tag = true; - /*if (get_config('system','use_fulltext_engine')) { - if(strpos($search,'#') === 0) - $sql_extra .= sprintf(" AND (MATCH(tag) AGAINST ('".'"%s"'."' in boolean mode)) ", - dbesc(protect_sprintf($search)) - ); - else - $sql_extra .= sprintf(" AND (MATCH(`item`.`body`, `item`.`title`) AGAINST ('%s' in boolean mode)) ", - dbesc(protect_sprintf($search)), - dbesc(protect_sprintf($search)) - ); - } else { - $sql_extra .= sprintf(" AND ( `item`.`body` like '%s' OR `item`.`tag` like '%s' ) ", - dbesc(protect_sprintf('%' . $search . '%')), - dbesc(protect_sprintf('%]' . $search . '[%')) - ); - }*/ - if($tag) { //$sql_extra = sprintf(" AND `term`.`term` = '%s' AND `term`.`otype` = %d AND `term`.`type` = %d ", // dbesc(protect_sprintf($search)), intval(TERM_OBJ_POST), intval(TERM_HASHTAG)); @@ -705,20 +691,6 @@ function network_content(&$a, $update = 0) { $myurl = substr($myurl,strpos($myurl,'://')+3); $myurl = str_replace('www.','',$myurl); $diasp_url = str_replace('/profile/','/u/',$myurl); - /*if (get_config('system','use_fulltext_engine')) - $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where (MATCH(`author-link`) AGAINST ('".'"%s"'."' in boolean mode) or MATCH(`tag`) AGAINST ('".'"%s"'."' in boolean mode) or MATCH(tag) AGAINST ('".'"%s"'."' in boolean mode))) ", - //$sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where (`author-link` IN ('https://%s', 'http://%s') OR MATCH(`tag`) AGAINST ('".'"%s"'."' in boolean mode) or MATCH(tag) AGAINST ('".'"%s"'."' in boolean mode))) ", - //$sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where `author-link` IN ('https://%s', 'http://%s') OR `mention`)", - dbesc(protect_sprintf($myurl)), - dbesc(protect_sprintf($myurl)), - dbesc(protect_sprintf($diasp_url)) - ); - else - $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where ( `author-link` like '%s' or `tag` like '%s' or tag like '%s' )) ", - dbesc(protect_sprintf('%' . $myurl)), - dbesc(protect_sprintf('%' . $myurl . ']%')), - dbesc(protect_sprintf('%' . $diasp_url . ']%')) - );*/ $sql_extra .= sprintf(" AND `item`.`parent` IN (SELECT distinct(`parent`) from item where `author-link` IN ('https://%s', 'http://%s') OR `mention`)", dbesc(protect_sprintf($myurl)), @@ -781,7 +753,7 @@ function network_content(&$a, $update = 0) { $simple_update AND `contact`.`blocked` = 0 AND `contact`.`pending` = 0 $sql_extra $sql_nets - ORDER BY $sql_order DESC $pager_sql ", + ORDER BY `item`.`received` DESC $pager_sql ", intval($_SESSION['uid']) ); diff --git a/mod/search.php b/mod/search.php index 04abdf04d5..cda9f68d2d 100644 --- a/mod/search.php +++ b/mod/search.php @@ -127,18 +127,6 @@ function search_content(&$a) { if (get_config('system','only_tag_search')) $tag = true; - /*if (get_config('system','use_fulltext_engine')) { - if($tag) - $sql_extra = sprintf(" AND MATCH (`item`.`tag`) AGAINST ('".'"%s"'."' in boolean mode) ", '#'.dbesc(protect_sprintf($search))); - else - $sql_extra = sprintf(" AND MATCH (`item`.`body`) AGAINST ('".'"%s"'."' in boolean mode) ", dbesc(protect_sprintf($search))); - } else { - if($tag) - $sql_extra = sprintf(" AND `item`.`tag` REGEXP '%s' ", dbesc('\\]' . protect_sprintf(preg_quote($search)) . '\\[')); - else - $sql_extra = sprintf(" AND `item`.`body` REGEXP '%s' ", dbesc(protect_sprintf(preg_quote($search)))); - }*/ - if($tag) { //$sql_extra = sprintf(" AND `term`.`term` = '%s' AND `term`.`otype` = %d AND `term`.`type` = %d", //$sql_extra = sprintf(" AND `term`.`term` = '%s' AND `term`.`otype` = %d AND `term`.`type` = %d group by `item`.`uri` ", -- 2.39.5