Getting two queries while working with Stream Entries

Question

I have this code:

public function index($offset = 0) {
    $params = array(
        'stream' => 'companies',
        'namespace' => 'companies',
        'paginate' => 'yes',
        'pag_segment' => 3
    );

    // Build regular expression to match items from DB
    $regexp = "'^[" . strtolower($this->uri->segment(4)) . strtoupper($this->uri->segment(4)) . "]'";
    $params['where'] = "name REGEXP " . $regexp;

    $entries = $this->streams->entries->get_entries($params);

    $this->template->set('items', $entries)->build('index');
}

I don't know why but I'm getting two queries, see details below:

0.0008 SELECT default_companies.*, cb_users.id as created_by||user_id, cb_users.email as created_by||email, cb_users.username as created_by||username, profiles.display_name as created_by||display_name FROM default_companies LEFT JOIN default_users as cb_users ON cb_users.id=default_companies.created_by LEFT JOIN default_profiles as profiles ON profiles.user_id=default_companies.created_by WHERE (name REGEXP '^[mM]') ORDER BY default_companies.created DESC

This query is the right one and it generate valid records but ...

0.0008 SELECT default_companies.*, cb_users.id as created_by||user_id, cb_users.email as created_by||email, cb_users.username as created_by||username, profiles.display_name as created_by||display_name FROM default_companies LEFT JOIN default_users as cb_users ON cb_users.id=default_companies.created_by LEFT JOIN default_profiles as profiles ON profiles.user_id=default_companies.created_by WHERE (name REGEXP '^[mM]') ORDER BY default_companies.created DESC LIMIT 10 OFFSET 4

This second query (don't know yet why it's executed) doesn't return any values because the OFFSET parameter so my view is always empty. What is wrong here? Did I miss something? I'm running PyroCMS 2.2.1 Community

Answer 1

The first query gets executed in system/cms/modules/streams_core/models/rows_m.php around line 658. It simple counts the total of all records in the stream table, to get the pagination right.

But I'm not exactly sure, why it sets an offset of 4. It should be set to 0, if it's missing.

You should try adding your $offset parameter to the $params array for the get_entries function and check what happens next.