How to list and search a database table using a collection

[mabdelaziz77]

A client asked me to develop a simple database table with a search module. Is this something can be done by Pages?

[johanjanssens]

Yes, you can do that very easily. Pages can connect to a database table; it's smart enough to understand the table structure and offers filtering and search out of the box, either through the PHP API, or the build in REST API.

To work with data Pages uses collections and specifically for database tables, there is a base database collection you can use. To set things up that all you need is to define a database collection.

For this example, I will use the Joomla users table, but it works with any database table, and generate a list of the users by name, then show you how to search based on name and email

Rendering the users list

Create the file /joomlatools-pages/pages/users.html.php and add the following code:

----
collection:
    model: database?table=users
    state:
        sort: name
---

<ul>
<? foreach(collection() as $user): ?>
<li><?= $user->name ?> (<?= $user->email ?>)</li>
<? endforeach ?>
</ul>

You can now call this page using: http://example.com/users and it will render a list of user names. The collection is set up to be sorted based on the name so the users will be listed alphabetically by name.

Searching for users

To be able to search you need to define the data properties searching is allowed on, in case of a database table, this means the database columns you wish to search.

For example to allow to search on the name and/or email of a user we define the name and email property as searchable in the collection definition.

----
collection:
    model: database?table=users
    state:
        sort: name
    config:
        search: [name, email]
---

Searching through PHP

Anywhere in your code, you can now re-use the users collection and search on it to retrieve a subset of the data.

Searching on all defined properties

<? $users = collection('users', ['search' => 'jack') ?>

Searching on a specific property

It's also possible to search, on a specific property only, by prepending the search word with the property name separated by a colon

<? $users = collection('users', ['search' => 'name:jack') ?> (search for all users that have jack in their name)

or

<? $users = collection('users', ['search' => 'email:foo.com') ?> (search for all users that have an email that is part of the foo.com domain)

Searching through the URL

You can also use the URL to list a subset of the users by searching it.

Searching on all defined properties

http://example.com/users?search=jack

Searching on a specific property

It's also possible to search on a specific property only by prepending the search word with the property name separated by a colon

http://example.com/users?search=name:jack (search for all users that have jack in their name)

or

http://example.com/users?search=email:foo.com (search for all users that have an email that is part of the foo.com domain)

[johanjanssens]

@mabdelaziz77 Pages 0.19.4 is released which included the changes needed for the search. More info: https://github.com/joomlatools/joomlatools-pages/releases/tag/v0.19.4