/** * Generate search query based on set filters and obtain count of results */ function query_init() { global $current_User; // Call reset to init the ItemQuery // This prevents from adding the same conditions twice if the ItemQuery was already initialized $this->reset(); if (empty($this->filters)) { // Filters have not been set before, we'll use the default filterset: // If there is a preset filter, we need to activate its specific defaults: $this->filters['filter_preset'] = param($this->param_prefix . 'filter_preset', 'string', $this->default_filters['filter_preset'], true); $this->activate_preset_filters(); // Use the default filters: $this->set_filters($this->default_filters); } // echo '<br />ItemListLight query'; //pre_dump( $this->filters ); // GENERATE THE QUERY: /* * Filtering stuff: */ if (!is_null($this->Blog)) { // Get the posts only for current Blog $this->ItemQuery->where_chapter2($this->Blog, $this->filters['cat_array'], $this->filters['cat_modifier'], $this->filters['cat_focus'], $this->filters['coll_IDs']); } else { // If we want to get the posts from all blogs // Save for future use (permission checks..) $this->ItemQuery->blog = 0; $this->ItemQuery->Blog = $this->Blog; } $this->ItemQuery->where_tags($this->filters['tags']); $this->ItemQuery->where_author($this->filters['authors']); $this->ItemQuery->where_author_logins($this->filters['authors_login']); $this->ItemQuery->where_assignees($this->filters['assignees']); $this->ItemQuery->where_assignees_logins($this->filters['assignees_login']); $this->ItemQuery->where_author_assignee($this->filters['author_assignee']); $this->ItemQuery->where_locale($this->filters['lc']); $this->ItemQuery->where_statuses($this->filters['statuses']); $this->ItemQuery->where_types($this->filters['types']); $this->ItemQuery->where_keywords($this->filters['keywords'], $this->filters['phrase'], $this->filters['exact'], $this->filters['keyword_scope']); $this->ItemQuery->where_ID($this->filters['post_ID'], $this->filters['post_title']); $this->ItemQuery->where_ID_list($this->filters['post_ID_list']); $this->ItemQuery->where_datestart($this->filters['ymdhms'], $this->filters['week'], $this->filters['ymdhms_min'], $this->filters['ymdhms_max'], $this->filters['ts_min'], $this->filters['ts_max']); $this->ItemQuery->where_datecreated($this->filters['ts_created_max']); $this->ItemQuery->where_visibility($this->filters['visibility_array'], $this->filters['coll_IDs']); $this->ItemQuery->where_featured($this->filters['featured']); /* * ORDER BY stuff: */ if ($this->filters['post_ID_list'] && $this->filters['orderby'] == 'ID_list') { $order_by = 'FIELD(' . $this->Cache->dbIDname . ', ' . $this->filters['post_ID_list'] . ')'; } elseif ($this->filters['orderby'] == 'ID_list') { // Use blog setting here because 'orderby' might be set to 'ID_list' as default filter $this->filters['orderby'] = $this->Blog->get_setting('orderby'); } if (empty($order_by)) { $available_fields = array_keys(get_available_sort_options()); // Extend general list to allow order posts by these fields as well for some special cases $available_fields[] = 'creator_user_ID'; $available_fields[] = 'assigned_user_ID'; $available_fields[] = 'pst_ID'; $available_fields[] = 'datedeadline'; $available_fields[] = 'T_categories.cat_name'; $available_fields[] = 'T_categories.cat_order'; $order_by = gen_order_clause($this->filters['orderby'], $this->filters['order'], $this->Cache->dbprefix, $this->Cache->dbIDname, $available_fields); } $this->ItemQuery->order_by($order_by); /* * GET TOTAL ROW COUNT: */ if ($this->single_post) { // Single post: no paging required! $this->total_rows = 1; $this->total_pages = 1; $this->page = 1; } elseif ($this->filters['unit'] == 'days' || $this->filters['unit'] == 'all') { $this->total_rows = NULL; // unknown! $this->total_pages = 1; $this->page = 1; } elseif ($this->filters['unit'] == 'posts') { // Calculate a count of the posts if ($this->ItemQuery->get_group_by() == '') { // SQL query without GROUP BY clause $sql_count = 'SELECT COUNT( DISTINCT ' . $this->Cache->dbIDname . ' )' . $this->ItemQuery->get_from() . $this->ItemQuery->get_where() . $this->ItemQuery->get_limit(); } else { // SQL query with GROUP BY clause, Summarize a count of each grouped result $sql_count = 'SELECT SUM( cnt_tbl.cnt ) FROM ( SELECT COUNT( DISTINCT ' . $this->Cache->dbIDname . ' ) AS cnt ' . $this->ItemQuery->get_from() . $this->ItemQuery->get_where() . $this->ItemQuery->get_group_by() . $this->ItemQuery->get_limit() . ' ) AS cnt_tbl '; } parent::count_total_rows($sql_count); } else { debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)'); } /* * Paging LIMITs: */ if ($this->single_post) { // Single post: no paging required! } elseif ($this->filters['unit'] == 'all') { // We want ALL results! } elseif ($this->filters['unit'] == 'posts') { // TODO: dh> check if $limit is NULL!? - though it should not arrive at $page>1 then.. // echo 'LIMIT POSTS '; $pgstrt = ''; if ($this->page > 1) { // We have requested a specific page number $pgstrt = (intval($this->page) - 1) * $this->limit . ', '; } $this->ItemQuery->LIMIT($pgstrt . $this->limit); } elseif ($this->filters['unit'] == 'days') { // We are going to limit to x days: // echo 'LIMIT DAYS '; if (empty($this->filters['ymdhms_min'])) { // We have no start date, we'll display the last x days: if (!empty($this->filters['keywords']) || !empty($this->filters['cat_array']) || !empty($this->filters['authors'])) { // We are in DAYS mode but we can't restrict on these! (TODO: ?) $limits = ''; } else { // We are going to limit to LAST x days: $lastpostdate = $this->get_lastpostdate(); $lastpostdate = mysql2date('Y-m-d 00:00:00', $lastpostdate); $lastpostdate = mysql2date('U', $lastpostdate); // go back x days $otherdate = date('Y-m-d H:i:s', $lastpostdate - ($this->limit - 1) * 86400); $this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart > \'' . $otherdate . '\''); } } else { // We have a start date, we'll display x days starting from that point: // $dstart_mysql has been calculated earlier // TODO: this is redundant with previous dstart processing: // Add trailing 0s: YYYYMMDDHHMMSS $dstart0 = $this->filters['ymdhms_min'] . '00000000000000'; $dstart_mysql = substr($dstart0, 0, 4) . '-' . substr($dstart0, 4, 2) . '-' . substr($dstart0, 6, 2) . ' ' . substr($dstart0, 8, 2) . ':' . substr($dstart0, 10, 2) . ':' . substr($dstart0, 12, 2); $dstart_ts = mysql2timestamp($dstart_mysql); // go forward x days $enddate_ts = date('Y-m-d H:i:s', $dstart_ts + $this->limit * 86400); $this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart < \'' . $enddate_ts . '\''); } } else { debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)'); } }
/** * Run Query: GET DATA ROWS *** HEAVY *** */ function query() { global $DB, $Session, $localtimenow; if (!is_null($this->rows)) { // Query has already executed: return; } // INIT THE QUERY: $this->query_init(); // We are going to proceed in two steps (we simulate a subquery) // 1) we get the IDs we need // 2) we get all the other fields matching these IDs // This is more efficient than manipulating all fields at once. // *** STEP 1 *** $user_IDs = isset($this->filters['users']) ? $this->filters['users'] : array(); if ($this->refresh_query || $localtimenow - $Session->get($this->filterset_name . '_refresh_time') > 7200) { // We should create new list of user IDs global $Timer; $Timer->start('Users_IDs', false); $step1_SQL = new SQL(); $step1_SQL->SELECT('T_users.user_ID, IF( user_avatar_file_ID IS NOT NULL, 1, 0 ) as has_picture, COUNT( DISTINCT blog_ID ) AS nb_blogs'); if (!empty($this->filters['reported']) && $this->filters['reported']) { // Filter is set to 'Reported users' $step1_SQL->SELECT_add(', COUNT( DISTINCT urep_reporter_ID ) AS user_rep'); } $step1_SQL->FROM($this->UserQuery->get_from('')); $step1_SQL->WHERE($this->UserQuery->get_where('')); $step1_SQL->GROUP_BY($this->UserQuery->get_group_by('')); $step1_SQL->ORDER_BY($this->UserQuery->get_order_by('')); $step1_SQL->LIMIT(0); // Get list of the IDs we need: $user_IDs = $DB->get_col($step1_SQL->get(), 0, 'UserList::Query() Step 1: Get ID list'); // Update filter with user IDs $this->filters['users'] = $user_IDs; $this->save_filterset(); $Timer->stop('Users_IDs'); } // GET TOTAL ROW COUNT: parent::count_total_rows(count($user_IDs)); // Pagination, Get user IDs from array for current page $user_IDs_paged = array_slice($user_IDs, ($this->page - 1) * $this->limit, $this->limit); // *** STEP 2 *** $step2_SQL = $this->UserQuery; if (!empty($user_IDs_paged)) { // Init sql query to get users by IDs $step2_SQL->WHERE($this->Cache->dbIDname . ' IN (' . implode(',', $user_IDs_paged) . ') '); $step2_SQL->ORDER_BY('FIND_IN_SET( user_ID, "' . implode(',', $user_IDs_paged) . '" )'); } else { // No users $step2_SQL->WHERE('user_ID IS NULL'); } $this->sql = $step2_SQL->get(); // ATTENTION: we skip the parent on purpose here!! fp> refactor DataObjectList2::query(false, false, false, 'UserList::Query() Step 2'); }
/** * * * @todo count? */ function query_init() { global $current_User; if (empty($this->filters)) { // Filters have not been set before, we'll use the default filterset: // If there is a preset filter, we need to activate its specific defaults: $this->filters['filter_preset'] = param($this->param_prefix . 'filter_preset', 'string', $this->default_filters['filter_preset'], true); $this->activate_preset_filters(); // Use the default filters: $this->set_filters($this->default_filters); } // echo '<br />ItemListLight query'; //pre_dump( $this->filters ); // GENERATE THE QUERY: /* * filtering stuff: */ if (!is_null($this->Blog)) { // Get the posts only for current Blog $this->ItemQuery->where_chapter2($this->Blog, $this->filters['cat_array'], $this->filters['cat_modifier'], $this->filters['cat_focus']); } else { // If we want to get the posts from all blogs // Save for future use (permission checks..) $this->ItemQuery->blog = 0; $this->ItemQuery->Blog = $this->Blog; } $this->ItemQuery->where_tags($this->filters['tags']); $this->ItemQuery->where_author($this->filters['authors']); $this->ItemQuery->where_author_logins($this->filters['authors_login']); $this->ItemQuery->where_assignees($this->filters['assignees']); $this->ItemQuery->where_assignees_logins($this->filters['assignees_login']); $this->ItemQuery->where_author_assignee($this->filters['author_assignee']); $this->ItemQuery->where_locale($this->filters['lc']); $this->ItemQuery->where_statuses($this->filters['statuses']); $this->ItemQuery->where_types($this->filters['types']); $this->ItemQuery->where_keywords($this->filters['keywords'], $this->filters['phrase'], $this->filters['exact']); $this->ItemQuery->where_ID($this->filters['post_ID'], $this->filters['post_title']); $this->ItemQuery->where_ID_list($this->filters['post_ID_list']); $this->ItemQuery->where_datestart($this->filters['ymdhms'], $this->filters['week'], $this->filters['ymdhms_min'], $this->filters['ymdhms_max'], $this->filters['ts_min'], $this->filters['ts_max']); $this->ItemQuery->where_datecreated($this->filters['ts_created_max']); $this->ItemQuery->where_visibility($this->filters['visibility_array']); $this->ItemQuery->where_featured($this->filters['featured']); /* * ORDER BY stuff: */ if ($this->filters['post_ID_list'] && $this->filters['orderby'] == 'ID_list') { $order_by = 'FIELD(' . $this->Cache->dbIDname . ', ' . $this->filters['post_ID_list'] . ')'; } elseif ($this->filters['orderby'] == 'ID_list') { // Use blog setting here because 'orderby' might be set to 'ID_list' as default filter $this->filters['orderby'] = $this->Blog->get_setting('orderby'); } if (empty($order_by)) { $order_by = gen_order_clause($this->filters['orderby'], $this->filters['order'], $this->Cache->dbprefix, $this->Cache->dbIDname); } $this->ItemQuery->order_by($order_by); /* * GET TOTAL ROW COUNT: */ if ($this->single_post) { // Single post: no paging required! $this->total_rows = 1; $this->total_pages = 1; $this->page = 1; } elseif ($this->filters['unit'] == 'days' || $this->filters['unit'] == 'all') { $this->total_rows = NULL; // unknown! $this->total_pages = 1; $this->page = 1; } elseif ($this->filters['unit'] == 'posts') { /* * TODO: The result is incorrect when using AND on categories * We would need to use a HAVING close and thyen COUNT, which would be a subquery * This is nto compatible with mysql 3.23 * We need fallback code. */ $sql_count = ' SELECT COUNT( DISTINCT ' . $this->Cache->dbIDname . ') ' . $this->ItemQuery->get_from() . $this->ItemQuery->get_where(); //echo $DB->format_query( $sql_count ); parent::count_total_rows($sql_count); //echo '<br />'.$this->total_rows; } else { debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)'); } /* * Paging LIMITs: */ if ($this->single_post) { // Single post: no paging required! } elseif ($this->filters['unit'] == 'all') { // We want ALL results! } elseif ($this->filters['unit'] == 'posts') { // TODO: dh> check if $limit is NULL!? - though it should not arrive at $page>1 then.. // echo 'LIMIT POSTS '; $pgstrt = ''; if ($this->page > 1) { // We have requested a specific page number $pgstrt = (intval($this->page) - 1) * $this->limit . ', '; } $this->ItemQuery->LIMIT($pgstrt . $this->limit); } elseif ($this->filters['unit'] == 'days') { // We are going to limit to x days: // echo 'LIMIT DAYS '; if (empty($this->filters['ymdhms_min'])) { // We have no start date, we'll display the last x days: if (!empty($this->filters['keywords']) || !empty($this->filters['cat_array']) || !empty($this->filters['authors'])) { // We are in DAYS mode but we can't restrict on these! (TODO: ?) $limits = ''; } else { // We are going to limit to LAST x days: $lastpostdate = $this->get_lastpostdate(); $lastpostdate = mysql2date('Y-m-d 00:00:00', $lastpostdate); $lastpostdate = mysql2date('U', $lastpostdate); // go back x days $otherdate = date('Y-m-d H:i:s', $lastpostdate - ($this->limit - 1) * 86400); $this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart > \'' . $otherdate . '\''); } } else { // We have a start date, we'll display x days starting from that point: // $dstart_mysql has been calculated earlier // TODO: this is redundant with previous dstart processing: // Add trailing 0s: YYYYMMDDHHMMSS $dstart0 = $this->filters['ymdhms_min'] . '00000000000000'; $dstart_mysql = substr($dstart0, 0, 4) . '-' . substr($dstart0, 4, 2) . '-' . substr($dstart0, 6, 2) . ' ' . substr($dstart0, 8, 2) . ':' . substr($dstart0, 10, 2) . ':' . substr($dstart0, 12, 2); $dstart_ts = mysql2timestamp($dstart_mysql); // go forward x days $enddate_ts = date('Y-m-d H:i:s', $dstart_ts + $this->limit * 86400); $this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart < \'' . $enddate_ts . '\''); } } else { debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)'); } }
/** * Initialize sql query * * @todo count? * * @param boolean */ function query_init($force_init = false) { global $DB; if (!$force_init && !empty($this->query_is_initialized)) { // Don't initialize query because it was already done return; } // Save to know the query init was done $this->query_is_initialized = true; if (empty($this->filters)) { // Filters have not been set before, we'll use the default filterset: // If there is a preset filter, we need to activate its specific defaults: $this->filters['filter_preset'] = param($this->param_prefix . 'filter_preset', 'string', $this->default_filters['filter_preset'], true); $this->activate_preset_filters(); // Use the default filters: $this->set_filters($this->default_filters); } // GENERATE THE QUERY: /* * Resrict to selected blog */ // If we dont have specific comment or post ids, we have to restric to blog if (!is_null($this->Blog) && ($this->filters['post_ID'] == NULL || !empty($this->filters['post_ID']) && substr($this->filters['post_ID'], 0, 1) == '-') && ($this->filters['comment_ID'] == NULL || !empty($this->filters['comment_ID']) && substr($this->filters['comment_ID'], 0, 1) == '-') && ($this->filters['comment_ID_list'] == NULL || !empty($this->filters['comment_ID_list']) && substr($this->filters['comment_ID_list'], 0, 1) == '-')) { // restriction for blog $this->ItemQuery->where_chapter($this->Blog->ID); } /* * filtering stuff: */ $this->CommentQuery->where_author($this->filters['author_IDs']); $this->CommentQuery->where_author_email($this->filters['author_email']); $this->CommentQuery->where_author_url($this->filters['author_url'], $this->filters['url_match'], $this->filters['include_emptyurl']); $this->CommentQuery->where_author_IP($this->filters['author_IP']); $this->ItemQuery->where_ID($this->filters['post_ID']); $this->CommentQuery->where_ID($this->filters['comment_ID'], $this->filters['author']); $this->CommentQuery->where_ID_list($this->filters['comment_ID_list']); $this->CommentQuery->where_rating($this->filters['rating_toshow'], $this->filters['rating_turn'], $this->filters['rating_limit']); $this->CommentQuery->where_keywords($this->filters['keywords'], $this->filters['phrase'], $this->filters['exact']); $this->CommentQuery->where_statuses($this->filters['statuses']); $this->CommentQuery->where_types($this->filters['types']); $this->ItemQuery->where_datestart('', '', '', '', $this->filters['timestamp_min'], $this->filters['timestamp_max']); if (!is_null($this->Blog) && isset($this->filters['user_perm'])) { // If Blog and required user permission is set, add the corresponding restriction $this->CommentQuery->user_perm_restrict($this->filters['user_perm'], $this->Blog->ID); } /* * ORDER BY stuff: */ $available_sort_options = array('date', 'type', 'author', 'author_url', 'author_email', 'author_IP', 'spam_karma', 'status', 'item_ID'); $order_by = gen_order_clause($this->filters['orderby'], $this->filters['order'], $this->Cache->dbprefix, $this->Cache->dbIDname, $available_sort_options); if ($this->filters['threaded_comments']) { // In mode "Threaded comments" we should get all replies in the begining of the list $order_by = $this->Cache->dbprefix . 'in_reply_to_cmt_ID DESC, ' . $order_by; } $this->CommentQuery->order_by($order_by); // GET Item IDs, this way we don't have to JOIN two times the items and the categories table into the comment query if (isset($this->filters['post_statuses'])) { // Set post statuses by filters $post_show_statuses = $this->filters['post_statuses']; } elseif (is_admin_page()) { // Allow all kind of post status ( This statuses will be filtered later by user perms ) $post_show_statuses = get_visibility_statuses('keys'); } else { // Allow only inskin statuses for posts $post_show_statuses = get_inskin_statuses(isset($this->Blog) ? $this->Blog->ID : NULL, 'post'); } // Restrict post filters to available statuses. When blog = 0 we will check visibility statuses for each blog separately ( on the same query ). $this->ItemQuery->where_visibility($post_show_statuses); $sql_item_IDs = 'SELECT DISTINCT post_ID' . $this->ItemQuery->get_from(); if (strpos($this->ItemQuery->get_from(), 'T_categories') === false && strpos($this->ItemQuery->get_where(), 'cat_blog_ID') !== false) { // Join categories table because it is required here for the field "cat_blog_ID" $sql_item_IDs .= ' INNER JOIN T_categories ON post_main_cat_ID = cat_ID '; } $sql_item_IDs .= $this->ItemQuery->get_where(); $item_IDs = $DB->get_col($sql_item_IDs, 0, 'Get CommentQuery Item IDs'); if (empty($item_IDs)) { // There is no item which belongs to the given blog and user may view it, so there are no comments either parent::count_total_rows(0); $this->CommentQuery->WHERE_and('FALSE'); return; } $this->CommentQuery->where_post_ID(implode(',', $item_IDs)); /* * Restrict to active comments by default, show expired comments only if it was requested * Note: This condition makes the CommentQuery a lot slower! */ $this->CommentQuery->expiry_restrict($this->filters['expiry_statuses']); /* * GET TOTAL ROW COUNT: */ $sql_count = ' SELECT COUNT( ' . $this->Cache->dbIDname . ') ' . $this->CommentQuery->get_from() . $this->CommentQuery->get_where(); parent::count_total_rows($sql_count); /* * Page set up: */ if ($this->page > 1) { // We have requested a specific page number if ($this->limit > 0) { $pgstrt = ''; $pgstrt = (intval($this->page) - 1) * $this->limit . ', '; $this->CommentQuery->LIMIT($pgstrt . $this->limit); } } else { $this->CommentQuery->LIMIT($this->limit); } }