Serverside Webscripting [JLW384]

06.silex.part4

Pagination

Pagination?

  • When having too many items to show, split them into several pages
    • e.g. search results, filter results
    • Typically placed beneath the results

Examples

  • Bad examples:


  • Good example:

    • Always indicate how many results there are
    • Always indicate how many pages there are
    • Show a reasonable number of pages
      • The number row automgically adjusts itself based on the current page
      • First two and last two pages always shown
      • All, except the current page, clickable
    • Always indicate current active page
      • Don't link it though!
    • Always show next/previous buttons
      • Only clickable when necessary
    • Don't show First/Last links (no direct value)

Howto: Needed parameters

  • From database: Total number of items
    $numItems = $db->fetchColumn('SELECT COUNT(*) FROM table');
  • From configuration: Number of items to show per page
    $numItemsPerPage = 10;
  • From url: current page (default = 1)
    $curPage = max(1, (int) $request->query->get('p'));
  • To calculate: number of pages
    $numPages = ceil($numItems / $numItemsPerPage);

Howto: Selecting subset of items

  • Pass $curPage and $numitemsPerPage to your repository and add it use it in a LIMIT clause
    public function findAll($curPage = 1, $numItemsPerPage = 10) {
    
    	return $this->db->fetchAll('
    		SELECT * FROM datasets
    		ORDER BY id DESC
    		LIMIT ' . (int) (($curPage - 1) * $numItemsPerPage) . ',' .
    		(int) ($numItemsPerPage)
    	);
    
    }

Howto: Parameters for Rendering

  • The current page and number of items
    $curPage; // To indicate the active page
    $numItems; // To indicate the number of items
  • The current URL
    $baseUrl = 'http://example.dev/news/'; // to prepend to the pagination params
  • An array with all numbers to show on screen, eg:
    $pagination = array(1,2,'...',6,7,8,9,10); // Subset of pages to show

Howto: Rendering (Twig)

{% set prevPage = curPage - 1 %}
{% set nextPage = curPage + 1 %}

{% if numPages > 1 %}
<div class="text-center">
	<ul class="pagination pagination-sm">

		{% if curPage > 1 %}
			<li class="previousPage"><a href="{{ baseUrl }}?p={{ prevPage }}" data-p="{{ prevPage }}">< Previous</a></li>
		{% else %}
			<li class="previousPage disabled"><span>< Previous</span></li>
		{% endif %}

		{% for page in pagination %}
		<li {% if page == curPage %} class="active"{% endif %}{% if page == '..' %} class="disabled"{% endif %}>
			{% if page == curPage %}
				<span>{{ page }}</span>
			{% elseif page == '..' %}
				<span>…</span>
			{% else %}
				<a href="{{ baseUrl }}?p={{ page }}" data-p="{{ page }}">{{ page }}</a>
			{% endif %}
		</li>
		{% endfor %}

		{% if curPage < numPages %}
			<li class="nextPage"><a href="{{ baseUrl }}?p={{ nextPage }}" data-p="{{ nextPage }}">Next ></a></li>
		{% else %}
			<li class="nextPage disabled"><span>Next ></span></li>
		{% endif %}

	</ul>

	<p class="text-muted">
		<em>{{ numItems }} items</em>
	</p>
</div>
{% endif %}

And now?

  • If you're really smart, create a reusable component (PHP class + template) from it and then assign the rendered HTML into your page template
    <?php
    
    // Get pagination params
    $numItemsPerPage = 10;
    $curPage = max(1, (int) $request->query->get('p'));
    $numPages = ceil($app['datasets']->numDatasets() / $numItemsPerPage);
    
    
    // Get datasets (for current page)
    $datasets = $app['datasets']->findAll(
    	$curPage,
    	$numItemsPerPage
    );
    
    // Build pagination
    $pagination = new \Taku\Pagination\Pagination(
    	$app['url_generator']->generate('datasets.overview'), // = baseUrl
    	$curPage,
    	$numPages
    );
    
    // Render our template
    return $app['twig']->render('datasets/overview.twig', array(
    	'datasets' => $datasets,
    	'pagination' => $pagination->getHtml()
    ));

Filtering

Filtering?

  • Allow a visitor to refine a set of results
    • A form with checkboxes, dropdowns, etc. per defining property of the resultset
    • Typically placed on the left hand side, next to the results

Filtering scenarios

  • Filter via $_GET params
    • Pro: links can be shared/bookmarked
    • Con: links become cluttered
    • Con: filter not preserved after having navigated away
  • Filter via $_SESSION params
    • Pro: links don't become cluttered
    • Pro: filter preserved after having navigated away
    • Con: links can't be shared/bookmarked

How to: Filter via session (1/3)

  • Force a default filter being present, with empty values:
    public function forceFilter(Application $app) {
    	if ($app['session']->get('filter_products') == null) {
    		$app['session']->set('filter_products', array(
    			'title' => '',
    			'type' => '',
    			'brand' => ''
    		));
    	}
    }
  • Provide a function to get the filter
    public function getFilter(Application $app) {
    	return $app['session']->get('filter_products');
    }

How to: Filter via session (2/3)

  • Pass filter to the filterform (= persistence)
    $filterform = $app['form.factory']
    	->createNamed('filterform', 'form', $this->getFilter($app))
    	->...
  • Store the filter on filterform submission
    public function setFilter(Application $app, $filter) {
    	$app['session']->set('filter_products', $filter);
    }
    
    if ('POST' == $app['request']->getMethod()) {
    	$filterform->handleRequest($app['request']);
    	if ($filterform->isValid()) {
    		$this->setFilter($app, $filterform->getData());
    		// return $app->redirect('...'); // Not required
    	}
    }

How to: Filter via session (3/3)

  • Pass the filter to the repository
    $products = $app['db.products']->findFiltered(
    	$this->getFilter($app), $curPage, $numItemsPerPage
    );
  • Adjust the repository to use the filter
    public function findFiltered($filter, $curPage = 1, $numItemsPerPage = 10) {
    
    	// @TODO: some magic here?!
    
    	return $this->db->fetchAll('
    		SELECT * FROM products
    		WHERE products.deleted = ?
    		ORDER BY id DESC
    		LIMIT ' . (int) (($curPage - 1) * $numItemsPerPage) . ',' .
    		(int) ($numItemsPerPage),
    		array('N')
    	);
    
    }

How to: Selecting filtered dataset

  • A filterparam on can cause two adjustments to the query
    1. Extra where clause part
    2. Extra table to join with
  • In code:
    public function findFiltered($filter, $curPage = 1, $numItemsPerPage = 10) {
    
    	$extraJoins = '';
    	$extraWhere = '';
    
    	// Title set via Filter
    	if ($filter['title'] != '') {
    		$extraWhere .= ' AND products.title LIKE ' . $this->db->quote('%'.$filter['title'].'%', \PDO::PARAM_STR);
    	}
    
    	// Type set via Filter
    	if ($filter['type'] != '') {
    		$extraJoins .= ' INNER JOIN product_types ON products.type_id = product_types.id';
    		$extraWhere .= ' AND products.type_id = ' . $this->db->quote($filter['type'], \PDO::PARAM_INT);
    	}
    
    	// Brand set via filter
    	if ($filter['brand'] != '') {
    		$extraJoins .= ' INNER JOIN brands ON products.brand_id = brands.id';
    		$extraWhere .= ' AND products.brand_id = ' . $this->db->quote($filter['brand'], \PDO::PARAM_INT);
    	}
    
    	return $this->db->fetchAll('
    		SELECT * FROM products' . $extraJoins . '
    		WHERE products.deleted = ?' . $extraWhere . '
    		ORDER BY id DESC
    		LIMIT ' . (int) (($curPage - 1) * $numItemsPerPage) . ',' .
    		(int) ($numItemsPerPage),
    		array('N')
    	);
    
    }

Caution!

  • !!! BEWARE OF SQL INJECTION !!!
  • Don't forget to adjust the pagination
    • (including the query to define $numItems!)
  • Don't autosubmit on change/select (lag)

Want more?

  • Split of the code the create the $extraJoins and $extraWhere parts to make it reusable
  • Augment your filter with Ajax
    • Change/select in filter = XHR request to fetch filtered subset
    • Response consists of two parts:
      1. Returned rows (as JSON)
      2. Pagination (as pre-rendered HTML)
  • Add autocomplete to text inputs

Questions?

Sources

ikdoeict.be