Bat Halliday Bat Halliday - 1 month ago 9
PHP Question

Symfony2 DatagridBundle / SonataNewsBundle Pager nbResults not calculated correctly

I have extended Sonata NewsBundle to include a region for news. This is to show UK visitors UK news, US visitors US news and so on.

Despite the query being used in the Pager object being correct, the nbResults count and page is counting news across all regions - not just the region in the query. This is causing the number of pages to be incorrect.

pager.getResults() is fetching the correct posts. Here is the twig dump of the Pager object for reference. This is the UK news which shows only 2 posts on the archive but has a count of 26 across 3 pages:

Pager {#1493 ▼
#queryBuilder: null
#page: 1
#maxPerPage: 10
#lastPage: 3.0
#nbResults: "26"
#cursor: 1
#parameters: []
#currentMaxLink: 1
#maxRecordLimit: false
#maxPageLinks: 0
#results: null
#resultsCounter: 0
#query: ProxyQuery {#1504 ▼
#queryBuilder: QueryBuilder {#1492 ▼
-_em: EntityManager {#537 …11}
-_dqlParts: array:9 [▶]
-_type: 0
-_state: 1
-_dql: "SELECT p, t FROM Acme\NewsBundle\Entity\Post p LEFT JOIN p.tags t WITH t.enabled = true LEFT JOIN p.author a WITH a.enabled = true LEFT JOIN p.regions r WHERE p.enabled = :enabled AND p.publicationDateStart <= :startDate AND p.collection = :collectionid AND r.id = :region AND p.id IN ('2','1') ORDER BY p.publicationDateStart DESC"
-parameters: ArrayCollection {#1500 ▶}
-_firstResult: null
-_maxResults: null
-joinRootAliases: array:3 [▶]
#cacheable: false
#cacheRegion: null
#cacheMode: null
#lifetime: 0
}
#sortBy: null
#sortOrder: null
#firstResult: 0
#maxResults: 10
#results: null
}
#countColumn: array:1 [▶]
}


This the twig markup (slightly abbreviated)

<div class="container news-list">
{% set posts = pager.getResults() %}
{% for post in posts %}
<div class="news-item">
// Mark up for post item
</div>
{% else %}
{{ 'no_post_found'|trans({}, 'SonataNewsBundle') }}
{% endfor %}

<ul class="pager">
<li class="previous">
<a class="btn {% if pager.page == pager.firstPage %} disabled{% endif %}"
{% if pager.page != pager.firstPage %}
href="{{ url(route, route_parameters|merge({'page': pager.previouspage})) }}"
{% endif %}
title="{{ 'link_previous_page'|trans({}, 'SonataNewsBundle') }}">
{{ 'link_previous_page'|trans({}, 'SonataNewsBundle') }}
</a>
</li>
<li class="next">
<a class="btn {% if pager.page == pager.lastPage %} disabled{% endif %}"
{% if pager.page != pager.lastPage %}
href="{{ url(route, route_parameters|merge({'page': pager.nextpage})) }}"
{% endif %}
title="{{ 'link_next_page'|trans({}, 'SonataNewsBundle') }}">
{{ 'link_next_page'|trans({}, 'SonataNewsBundle') }}
</a>
</li>
</ul>
</div>


How can I get the pager to show the correct page information?

Answer

The solution turned out to be a one-liner. Where the pager was being extended to add the criteria, it needed to be reinitialised:

public function getPager(array $criteria, $page, $limit = 10, array $sort = array())
{
    $criteria['date'] = [
        'query' => 'p.publicationDateStart <= :startDate',
        'params' => [
            'startDate' => new \DateTime(),
        ]
    ];

    $pager = parent::getPager($criteria, $page, $limit, $sort);
    /** @var QueryBuilder $query */
    $query = $pager->getQuery()->getQueryBuilder();
    $parameters = $query->getParameters();

    if (isset($criteria['title'])) {
        $query->andWhere('p.title = :title');
        $parameter = new Parameter('title', $criteria['title']);
        $parameters->add($parameter);
    }

    if (isset($criteria['region'])) {
        $query->leftJoin('p.regions', 'r');
        $query->andWhere('r.id = :region');
        $parameter = new Parameter('region', $criteria['region']);
        $parameters->add($parameter);
    }

    /** THIS NEEDED TO HAPPEN IN ORDER TO RE-INITIALISE **/
    $pager->init();

    return $pager;
}
Comments