PHP Nette - Paginator - SQL JOIN - Call to a member function count() on array

Good day,

I have a VisualPaginator project in Nette and it works fine. However, if I want to use SQL with JOIN across multiple tables, I cannot pass this SQL through a paginator:

$rows = $this->database->fetchAll('SELECT dochazka.*, clenove.*, treninky.*, druzstva.* FROM dochazka
JOIN clenove ON dochazka.doch_cl_id = clenove.cl_id
JOIN treninky ON dochazka.doch_tr_id = treninky.tr_id
JOIN druzstva ON dochazka.doch_dr_id = druzstva.dr_id
ORDER BY dochazka.doch_id DESC');

$visualPaginator = $this['visualPaginator'];
$paginator = $visualPaginator->getPaginator();
$paginator->itemsPerPage = 30;
$paginator->itemCount = $rows->count('*');
$rows->limit($paginator->itemsPerPage, $paginator->offset);
$this->template->rows = $rows;

Error
Call to a member function count() on array

$paginator->itemCount = $rows->count('*');

Can you advise someone how to put the paginator into a classic SQL query over multiple tables (via JOIN)? Thanks

REPLY

Hello,

in Nette project with VisualPaginator you will need to modify SQL queries like this and it will work, I have verified it:

$count = $this->database->query('SELECT dochazka.*, clenove.*, treninky.*, druzstva.* FROM dochazka
JOIN clenove ON dochazka.doch_cl_id = clenove.cl_id
JOIN treninky ON dochazka.doch_tr_id = treninky.tr_id
JOIN druzstva ON dochazka.doch_dr_id = druzstva.dr_id
ORDER BY dochazka.doch_id DESC');

$visualPaginator = $this['visualPaginator'];
$paginator = $visualPaginator->getPaginator();
$paginator->itemsPerPage = 30;
$paginator->itemCount = count($count->fetchAll());

$this->template->rows = $this->database->query('SELECT dochazka.*, clenove.*, treninky.*, druzstva.* FROM dochazka
JOIN clenove ON dochazka.doch_cl_id = clenove.cl_id
JOIN treninky ON dochazka.doch_tr_id = treninky.tr_id
JOIN druzstva ON dochazka.doch_dr_id = druzstva.dr_id
ORDER BY dochazka.doch_id DESC LIMIT ? OFFSET ?', $paginator->itemsPerPage, $paginator->offset);