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

Here's my take at it

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

http://patternry.com/p=search-pagination/

ikdoeict.be