Imagine for a moment that our bookstore website is quite successful, so we think of building a mobile app to increase our market. Of course, we would want to use the same database that we use for our website, as we need to sync the books that people borrow or buy from both apps. We do not want to be in a position where two people buy the same last copy of a book!
Not only the database, but the queries used to get books, update them, and so on, have to be the same too, otherwise we would end up with unexpected behavior. Of course, one apparently easy option would be to replicate the queries in both codebases, but that has a huge maintainability problem. What if we change one single field of our database? We need to apply the same change to at least two different codebases. That does not seem to be useful at all.
Business logic plays an important role here too. Think of it as decisions you need to take that affect your business. In our case, that a premium customer is able to borrow 10 books and a normal one only 3, is business logic. This logic should be put in a common place too, because, if we want to change it, we will have the same problems as with our database queries.
We hope that by now we've convinced you that data and business logic should be separated from the rest of the code in order to make it reusable. Do not worry if it is hard for you to define what should go as part of the model or as part of the controller; a lot of people struggle with this distinction. As our application is very simple, and it does not have a lot of business logic, we will just focus on adding all the code related to MySQL queries.
As you can imagine, for an application integrated with MySQL, or any other database system, the database connection is an important element of a model. We chose to use PDO in order to interact with MySQL, and as you might remember, instantiating that class was a bit of a pain. Let's create a singleton class that returns an instance of PDO to make things easier. Add this code to src/Core/Db.php:
<?php
namespace BookstoreCore;
use PDO;
class Db {
private static $instance;
private static function connect(): PDO {
$dbConfig = Config::getInstance()->get('db');
return new PDO(
'mysql:host=127.0.0.1;dbname=bookstore',
$dbConfig['user'],
$dbConfig['password']
);
}
public static function getInstance(){
if (self::$instance == null) {
self::$instance = self::connect();
}
return self::$instance;
}
}This class, defined in the preceding code snippet, just implements the singleton pattern and wraps the creation of a PDO instance. From now on, in order to get a database connection, we just need to write Db::getInstance().
Although it might not be true for all models, in our application, they will always have to access the database. We could create an abstract class where all models extend. This class could contain a $db protected property that will be set on the constructor. With this, we avoid duplicating the same constructor and property definition across all our models. Copy the following class into src/Models/AbstractModel.php:
<?php
namespace BookstoreModels;
use PDO;
abstract class AbstractModel {
private $db;
public function __construct(PDO $db) {
$this->db = $db;
}
}Finally, to finish the setup of the models, we could create a new exception (as we did with the NotFoundException class) that represents an error from the database. It will not contain any code, but we will be able to differentiate where an exception is coming from. We will save it in src/Exceptions/DbException.php:
<?php
namespace BookstoreExceptions;
use Exception;
class DbException extends Exception {
}Now that we've set the ground, we can start writing our models. It is up to you to organize your models, but it is a good idea to mimic the domain objects structure. In this case, we would have three models: CustomerModel, BookModel, and SalesModel. In the following sections, we will explain the contents of each of them.
Let's start with the easiest one. As our application is still very primitive, we will not allow the creation of new costumers, and work with the ones we inserted manually into the database instead. That means that the only thing we need to do with customers is to query them. Let's create a CustomerModel class in src/Models/CustomerModel.php with the following content:
<?php
namespace BookstoreModels;
use BookstoreDomainCustomer;
use BookstoreDomainCustomerCustomerFactory;
use BookstoreExceptionsNotFoundException;
class CustomerModel extends AbstractModel {
public function get(int $userId): Customer {
$query = 'SELECT * FROM customer WHERE customer_id = :user';
$sth = $this->db->prepare($query);
$sth->execute(['user' => $userId]);
$row = $sth->fetch();
if (empty($row)) {
throw new NotFoundException();
}
return CustomerFactory::factory(
$row['type'],
$row['id'],
$row['firstname'],
$row['surname'],
$row['email']
);
}
public function getByEmail(string $email): Customer {
$query = 'SELECT * FROM customer WHERE email = :user';
$sth = $this->db->prepare($query);
$sth->execute(['user' => $email]);
$row = $sth->fetch();
if (empty($row)) {
throw new NotFoundException();
}
return CustomerFactory::factory(
$row['type'],
$row['id'],
$row['firstname'],
$row['surname'],
$row['email']
);
}
}The CustomerModel class, which extends from the AbstractModel class, contains two methods; both of them return a Customer instance, one of them when providing the ID of the customer, and the other one when providing the e-mail. As we already have the database connection as the $db property, we just need to prepare the statement with the given query, execute the statement with the arguments, and fetch the result. As we expect to get a customer, if the user provided an ID or an e-mail that does not belong to any customer, we will need to throw an exception—in this case, a NotFoundException is just fine. If we find a customer, we use our factory to create the object and return it.
Our BookModel class gives us a bit more of work. Customers had a factory, but it is not worth having one for books. What we use for creating them from MySQL rows is not the constructor, but a fetch mode that PDO has, and that allows us to map a row into an object. To do so, we need to adapt the Book domain object a bit:
- The names of the properties have to be the same as the names of the fields in the database
- There is no need for a constructor or setters, unless we need them for other purposes
- To go with encapsulation, properties should be private, so we will need getters for all of them
The new Book class should look like the following:
<?php
namespace BookstoreDomain;
class Book {
private $id;
private $isbn;
private $title;
private $author;
private $stock;
private $price;
public function getId(): int {
return $this->id;
}
public function getIsbn(): string {
return $this->isbn;
}
public function getTitle(): string {
return $this->title;
}
public function getAuthor(): string {
return $this->author;
}
public function getStock(): int {
return $this->stock;
}
public function getCopy(): bool {
if ($this->stock < 1) {
return false;
} else {
$this->stock--;
return true;
}
}
public function addCopy() {
$this->stock++;
}
public function getPrice(): float {
return $this->price;
}
}We retained the getCopy and addCopy methods even though they are not getters, as we will need them later. Now, when fetching a group of rows from MySQL with the fetchAll method, we can send two parameters: the constant PDO::FETCH_CLASS that tells PDO to map rows to a class, and the name of the class that we want to map to. Let's create the BookModel class with a simple get method that fetches a book from the database with a given ID. This method will return either a Book object or throw an exception in case the ID does not exist. Save it as src/Models/BookModel.php:
<?php
namespace BookstoreModels;
use BookstoreDomainBook;
use BookstoreExceptionsDbException;
use BookstoreExceptionsNotFoundException;
use PDO;
class BookModel extends AbstractModel {
const CLASSNAME = 'BookstoreDomainBook';
public function get(int $bookId): Book {
$query = 'SELECT * FROM book WHERE id = :id';
$sth = $this->db->prepare($query);
$sth->execute(['id' => $bookId]);
$books = $sth->fetchAll(
PDO::FETCH_CLASS, self::CLASSNAME
);
if (empty($books)) {
throw new NotFoundException();
}
return $books[0];
}
}There are advantages and disadvantages of using this fetch mode. On one hand, we avoid a lot of dull code when creating objects from rows. Usually, we either just send all the elements of the row array to the constructor of the class, or use setters for all its properties. If we add more fields to the MySQL table, we just need to add the properties to our domain class, instead of changing everywhere where we were instantiating the objects. On the other hand, you are forced to use the same names for the fields in both the table's as well as the class' properties, which means high coupling (always a bad idea). This also causes some conflicts when following conventions, because in MySQL, it is common to use book_id, but in PHP, the property is $bookId.
Now that we know how this fetch mode works, let's add three other methods that fetch data from MySQL. Add the following code to your model:
public function getAll(int $page, int $pageLength): array {
$start = $pageLength * ($page - 1);
$query = 'SELECT * FROM book LIMIT :page, :length';
$sth = $this->db->prepare($query);
$sth->bindParam('page', $start, PDO::PARAM_INT);
$sth->bindParam('length', $pageLength, PDO::PARAM_INT);
$sth->execute();
return $sth->fetchAll(PDO::FETCH_CLASS, self::CLASSNAME);
}
public function getByUser(int $userId): array {
$query = <<<SQL
SELECT b.*
FROM borrowed_books bb LEFT JOIN book b ON bb.book_id = b.id
WHERE bb.customer_id = :id
SQL;
$sth = $this->db->prepare($query);
$sth->execute(['id' => $userId]);
return $sth->fetchAll(PDO::FETCH_CLASS, self::CLASSNAME);
}
public function search(string $title, string $author): array {
$query = <<<SQL
SELECT * FROM book
WHERE title LIKE :title AND author LIKE :author
SQL;
$sth = $this->db->prepare($query);
$sth->bindValue('title', "%$title%");
$sth->bindValue('author', "%$author%");
$sth->execute();
return $sth->fetchAll(PDO::FETCH_CLASS, self::CLASSNAME);
}The methods added are as follows:
getAllreturns an array of all the books for a given page. Remember thatLIMITallows you to return a specific number of rows with an offset, which can work as a paginator.getByUserreturns all the books that a given customer has borrowed—we will need to use a join query for this. Note that we returnb.*, that is, only the fields of thebooktable, skipping the rest of the fields.- Finally, there is a method to search by either title or author, or both. We can do that using the operator
LIKEand enclosing the patterns with%. If we do not specify one of the parameters, we will try to match the field with%%, which matches everything.
So far, we have been adding methods to fetch data. Let's add methods that will allow us to modify the data in our database. For the book model, we will need to be able to borrow books and return them. Here is the code for those two actions:
public function borrow(Book $book, int $userId) {
$query = <<<SQL
INSERT INTO borrowed_books (book_id, customer_id, start)
VALUES(:book, :user, NOW())
SQL;
$sth = $this->db->prepare($query);
$sth->bindValue('book', $book->getId());
$sth->bindValue('user', $userId);
if (!$sth->execute()) {
throw new DbException($sth->errorInfo()[2]);
}
$this->updateBookStock($book);
}
public function returnBook(Book $book, int $userId) {
$query = <<<SQL
UPDATE borrowed_books SET end = NOW()
WHERE book_id = :book AND customer_id = :user AND end IS NULL
SQL;
$sth = $this->db->prepare($query);
$sth->bindValue('book', $book->getId());
$sth->bindValue('user', $userId);
if (!$sth->execute()) {
throw new DbException($sth->errorInfo()[2]);
}
$this->updateBookStock($book);
}
private function updateBookStock(Book $book) {
$query = 'UPDATE book SET stock = :stock WHERE id = :id';
$sth = $this->db->prepare($query);
$sth->bindValue('id', $book->getId());
$sth->bindValue('stock', $book->getStock());
if (!$sth->execute()) {
throw new DbException($sth->errorInfo()[2]);
}
}When borrowing a book, you are adding a row to the borrower_books table. When returning books, you do not want to remove that row, but rather to set the end date in order to keep a history of the books that a user has been borrowing. Both methods need to change the stock of the borrowed book: when borrowing it, reducing the stock by one, and when returning it, increasing the stock. That is why, in the last code snippet, we created a private method to update the stock of a given book, which will be used from both the borrow and returnBook methods.
Now we need to add the last model to our application: the SalesModel. Using the same fetch mode that we used with books, we need to adapt the domain class as well. We need to think a bit more in this case, as we will be doing more than just fetching. Our application has to be able to create new sales on demand, containing the ID of the customer and the books. We can already add books with the current implementation, but we need to add a setter for the customer ID. The ID of the sale will be given by the autoincrement ID in MySQL, so there is no need to add a setter for it. The final implementation would look as follows:
<?php
namespace BookstoreDomain;
class Sale {
private $id;
private $customer_id;
private $books;
private $date;
public function setCustomerId(int $customerId) {
$this->customer_id = $customerId;
}
public function getId(): int {
return $this->id;
}
public function getCustomerId(): int {
return $this->customer_id;
}
public function getBooks(): array {
return $this->books;
}
public function getDate(): string {
return $this->date;
}
public function addBook(int $bookId, int $amount = 1) {
if (!isset($this->books[$bookId])) {
$this->books[$bookId] = 0;
}
$this->books[$bookId] += $amount;
}
public function setBooks(array $books) {
$this->books = $books;
}
}The SalesModel will be the most difficult one to write. The problem with this model is that it includes manipulating different tables: sale and sale_book. For example, when getting the information of a sale, we need to get the information from the sale table, and then the information of all the books in the sale_book table. You could argue about whether to have one unique method that fetches all the necessary information related to a sale, or to have two different methods, one to fetch the sale and the other to fetch the books, and let the controller to decide which one to use.
This actually starts a very interesting discussion. On one hand, we want to make things easier for the controller—having one unique method to fetch the entire Sale object. This makes sense as the controller does not need to know about the internal implementation of the Sale object, which lowers coupling. On the other hand, forcing the model to always fetch the whole object, even if we only need the information in the sale table, is a bad idea. Imagine if the sale contains a lot of books; fetching them from MySQL will decrease performance unnecessarily.
You should think how your controllers need to manage sales. If you will always need the entire object, you can have one method without being concerned about performance. If you only need to fetch the entire object sometimes, maybe you could add both methods. For our application, we will have one method to rule them all, since that is what we will always need.
Note
Lazy loading
As with any other design challenge, other developers have already given a lot of thought to this problem. They came up with a design pattern named lazy load. This pattern basically lets the controller think that there is only one method to fetch the whole domain object, but we will actually be fetching only what we need from database.
The model fetches the most used information for the object and leaves the rest of the properties that need extra database queries empty. Once the controller uses a getter of a property that is empty, the model automatically fetches that data from the database. We get the best of both worlds: there is simplicity for the controller, but we do not spend more time than necessary querying unused data.
Add the following as your src/Models/SaleModel.php file:
<?php
namespace BookstoreModels;
use BookstoreDomainSale;
use BookstoreExceptionsDbException;
use PDO;
class SaleModel extends AbstractModel {
const CLASSNAME = 'BookstoreDomainSale';
public function getByUser(int $userId): array {
$query = 'SELECT * FROM sale WHERE s.customer_id = :user';
$sth = $this->db->prepare($query);
$sth->execute(['user' => $userId]);
return $sth->fetchAll(PDO::FETCH_CLASS, self::CLASSNAME);
}
public function get(int $saleId): Sale {
$query = 'SELECT * FROM sale WHERE id = :id';
$sth = $this->db->prepare($query);
$sth->execute(['id' => $saleId]);
$sales = $sth->fetchAll(PDO::FETCH_CLASS, self::CLASSNAME);
if (empty($sales)) {
throw new NotFoundException('Sale not found.');
}
$sale = array_pop($sales);
$query = <<<SQL
SELECT b.id, b.title, b.author, b.price, sb.amount as stock, b.isbn
FROM sale s
LEFT JOIN sale_book sb ON s.id = sb.sale_id
LEFT JOIN book b ON sb.book_id = b.id
WHERE s.id = :id
SQL;
$sth = $this->db->prepare($query);
$sth->execute(['id' => $saleId]);
$books = $sth->fetchAll(
PDO::FETCH_CLASS, BookModel::CLASSNAME
);
$sale->setBooks($books);
return $sale;
}
}Another tricky method in this model is the one that takes care of creating a sale in the database. This method has to create a sale in the sale table, and then add all the books for that sale to the sale_book table. What would happen if we have a problem when adding one of the books? We would leave a corrupted sale in the database. To avoid that, we need to use transactions, starting with one at the beginning of the model's or the controller's method, and either rolling back in case of error, or committing it at the end of the method.
In the same method, we also need to take care of the ID of the sale. We do not set the ID of the sale when creating the sale object, because we rely on the autoincremental field in the database. But when inserting the books into sale_book, we do need the ID of the sale. For that, we need to request the PDO for the last inserted ID with the lastInsertId method. Let's add then the create method into your SaleModel:
public function create(Sale $sale) {
$this->db->beginTransaction();
$query = <<<SQL
INSERT INTO sale(customer_id, date)
VALUES(:id, NOW())
SQL;
$sth = $this->db->prepare($query);
if (!$sth->execute(['id' => $sale->getCustomerId()])) {
$this->db->rollBack();
throw new DbException($sth->errorInfo()[2]);
}
$saleId = $this->db->lastInsertId();
$query = <<<SQL
INSERT INTO sale_book(sale_id, book_id, amount)
VALUES(:sale, :book, :amount)
SQL;
$sth = $this->db->prepare($query);
$sth->bindValue('sale', $saleId);
foreach ($sale->getBooks() as $bookId => $amount) {
$sth->bindValue('book', $bookId);
$sth->bindValue('amount', $amount);
if (!$sth->execute()) {
$this->db->rollBack();
throw new DbException($sth->errorInfo()[2]);
}
}
$this->db->commit();
}One last thing to note from this method is that we prepare a statement, bind a value to it (the sale ID), and then bind and execute the same statement as many times as the books in the array. Once you have a statement, you can bind the values as many times as you want. Also, you can execute the same statement as many times as you want, and the values stay the same.