In this article by Calin Rada, the author of the book Learning Phalcon PHP, we will go through a few of the ORM CRUD operations (update, and delete) and database transactions
(For more resources related to this topic, see here.)
By using the ORM, there is virtually no need to write any SQL in your code. Everything is OOP, and it is using the models to perform operations. The first, and the most basic, operation is retrieving data. In the old days, you would do this:
$result = mysql_query(“SELECT * FROM article”);
The class that our models are extending is PhalconMvcModel. This class has some very useful methods built in, such as find(), findFirst(), count(), sum(), maximum(), minimum(), average(), save(), create(), update(), and delete().
CRUD – updating data
Updating data is as easy as creating it. The only thing that we need to do is find the record that we want to update. Open the article manager and add the following code:
public function update($id, $data)
{
$article = Article::findFirstById($id);
if (!$article)
{
throw new Exception('Article not found', 404);
}
$article->setArticleShortTitle($data[
'article_short_title']);
$article->setUpdatedAt(new PhalconDbRawValue('NOW()'));
if (false === $article->update()) {
foreach ($article->getMessages() as $message) {
$error[] = (string) $message;
}
throw new Exception(json_encode($error));
}
return $article;
}As you can see, we are passing a new variable, $id, to the update method and searching for an article that has its ID equal to the value of the $id variable. For the sake of an example, this method will update only the article title and the updated_at field for now.
Next, we will create a new dummy method as we did for the article, create. Open modules/Backoffice/Controllers/ArticleController.php and add the following code:
public function updateAction($id)
{
$this->view->disable();
$article_manager = $this->getDI()->get(
'core_article_manager');
try {
$article = $article_manager->update($id, [
'article_short_title' => 'Modified article 1'
]);
echo $article->getId(), " was updated.";
} catch (Exception $e) {
echo $e->getMessage();
}
}If you access http://www.learning-phalcon.localhost/backoffice/article/update/1 now, you should be able to see the 1 was updated. response. Going back to the article list, you will see the new title, and the Updated column will have a new value.
CRUD – deleting data
Deleting data is easier, since we don’t need to do more than calling the built-in delete() method. Open the article manager, and add the following code:
public function delete($id)
{
$article = Article::findFirstById($id);
if (!$article)
{
throw new Exception('Article not found', 404);
}
if (false === $article->delete())
{
foreach ($article->getMessages() as $message) {
$error[] = (string) $message;
}
throw new Exception(json_encode($error));
}
return true;
}
We will once again create a dummy method to delete records. Open modules/Backoffice/Controllers/ArticleControllers.php, and add the following code:
public function deleteAction($id)
{
$this->view->disable();
$article_manager = $this->getDI()->get('core_article_manager');
try {
$article_manager->delete($id);
echo "Article was deleted.";
} catch (Exception $e)
{
echo $e->getMessage();
}
}To test this, simply access http://www.learning-phalcon.localhost/backoffice/article/delete/1. If everything went well, you should see the Article was deleted. message. Going back to, article list, you won’t be able to see the article with ID 1 anymore.
These are the four basic methods: create, read, update, and delete. Later in this book, we will use these methods a lot.
If you need/want to, you can use the Phalcon Developer Tools to generate CRUD automatically. Check out https://github.com/phalcon/phalcon-devtools for more information.
Using PHQL
Personally, I am not a fan of PHQL. I prefer using ORM or Raw queries. But if you are going to feel comfortable with it, feel free to use it. PHQL is quite similar to writing raw SQL queries. The main difference is that you will need to pass a model instead of a table name, and use a models manager service or directly call the PhalconMvcModelQuery class. Here is a method similar to the built-in find() method:
public function find()
{
$query = new PhalconMvcModelQuery("SELECT * FROM AppCoreModelsArticle", $this->getDI());
$articles = $query->execute();
return $articles;
}To use the models manager, we need to inject this new service. Open the global services file, config/service.php, and add the following code:
$di['modelsManager'] = function () {
return new PhalconMvcModelManager();
};Now let’s rewrite the find() method by making use of the modelsManager service:
public function find()
{
$query = $this->modelsManager->createQuery(
"SELECT * FROM AppCoreModelsArticle");
$articles = $query->execute();
return $articles;
}If we need to bind parameters, the method can look like this one:
public function find()
{
$query = $this->modelsManager->createQuery(
"SELECT * FROM AppCoreModelsArticle WHERE id = :id:");
$articles = $query->execute(array(
'id' => 2
));
return $articles;
}We are not going to use PHQL at all in our project. If you are interested in it, you can find more information in the official documentation at http://docs.phalconphp.com/en/latest/reference/phql.html.
Using raw SQL
Sometimes, using raw SQL is the only way of performing complex queries. Let’s see what a raw SQL will look like for a custom find() method and a custom update() method :
<?php
use PhalconMvcModelResultsetSimple as Resultset;
class Article extends Base
{
public static function rawFind()
{
$sql = "SELECT * FROM robots WHERE id > 0";
$article = new self();
return new Resultset(null, $article,
$article->getReadConnection()->query($sql));
}
public static function rawUpdate()
{
$sql = "UPDATE article SET is_published = 1";
$this->getReadConnection()->execute($sql);
}
}As you can see, the rawFind() method returns an instance of PhalconMvcModelResultsetSimple. The rawUpdate() method just executes the query (in this example, we will mark all the articles as published). You might have noticed the getReadConnection() method. This method is very useful when you need to iterate over a large amount of data or if, for example, you use a master-slave connection. As an example, consider the following code snippet:
<?php
class Article extends Base
{
public function initialize()
{
$this->setReadConnectionService('a_slave_db_connection_service'); // By default is 'db'
$this->setWriteConnectionService('db');
}
}Working with models might be a complex thing. We cannot cover everything in this book, but we will work with many common techniques to achieve this part of our project. Please spare a little time and read more about working with models at http://docs.phalconphp.com/en/latest/reference/models.html.
Database transactions
If you need to perform multiple database operations, then in most cases you need to ensure that every operation is successful, for the sake of data integrity. A good database architecture in not always enough to solve potential integrity issues. This is the case where you should use transactions. Let’s take as an example a virtual wallet that can be represented as shown in the next few tables.
The User table looks like the following:
| ID | NAME |
| 1 | John Doe |
The Wallet table looks like this:
| ID | USER_ID | BALANCE |
| 1 | 1 | 5000 |
The Wallet transactions table looks like the following:
| ID | WALLET_ID | AMOUNT | DESCRIPTION |
| 1 | 1 | 5000 | Bonus credit |
| 2 | 1 | -1800 | Apple store |
How can we create a new user, credit their wallet, and then debit it as the result of a purchase action? This can be achieved in three ways using transactions:
- Manual transactions
- Implicit transactions
- Isolated transactions
A manual transactions example
Manual transactions are useful when we are using only one connection and the transactions are not very complex. For example, if any error occurs during an update operation, we can roll back the changes without affecting the data integrity:
<?php
class UserController extends PhalconMvcController
{
public function saveAction()
{
$this->db->begin();
$user = new User();
$user->name = "John Doe";
if (false === $user->save()
{
$this->db->rollback();
return;
}
$wallet = new Wallet();
$wallet->user_id = $user->id;
$wallet->balance = 0;
if (false === $wallet->save())
{
$this->db->rollback();
return;
}
$walletTransaction = new WalletTransaction();
$walletTransaction->wallet_id = $wallet->id;
$walletTransaction->amount = 5000;
$walletTransaction->description = 'Bonus credit';
if (false === $walletTransaction1->save())
{
$this->db->rollback();
return;
}
$walletTransaction1 = new WalletTransaction();
$walletTransaction1->wallet_id = $wallet->id;
$walletTransaction1->amount = -1800;
$walletTransaction1->description = 'Apple store';
if (false === $walletTransaction1->save())
{
$this->db->rollback();
return;
}
$this->db->commit();
}
}An implicit transactions example
Implicit transactions are very useful when we need to perform operations on related tables / exiting relationships:
<?php
class UserController extends PhalconMvcController
{
public function saveAction()
{
$walletTransactions[0] = new WalletTransaction();
$walletTransactions[0]->wallet_id = $wallet->id;
$walletTransactions[0]->amount = 5000;
$walletTransactions[0]->description = 'Bonus credit';
$walletTransactions[1] = new WalletTransaction();
$walletTransactions[1]->wallet_id = $wallet->id;
$walletTransactions[1]->amount = -1800;
$walletTransactions[1]->description = 'Apple store';
$wallet = new Wallet();
$wallet->user_id = $user->id;
$wallet->balance = 0;
$wallet->transactions = $walletTransactions;
$user = new User();
$user->name = "John Doe";
$user->wallet = $wallet;
}
}An isolated transactions example
Isolated transactions are always executed in a separate connection, and they require a transaction manager:
<?php
use PhalconMvcModelTransactionManager as TxManager,
PhalconMvcModelTransactionFailed as TxFailed;
class UserController extends PhalconMvcController
{
public function saveAction()
{
try {
$manager = new TxManager();
$transaction = $manager->get();
$user = new User();
$user->setTransaction($transaction);
$user->name = "John Doe";
if ($user->save() == false) {
$transaction->rollback("Cannot save user");
}
$wallet = new Wallet();
$wallet->setTransaction($transaction);
$wallet->user_id = $user->id;
$wallet->balance = 0;
if ($wallet->save() == false) {
$transaction->rollback("Cannot save wallet");
}
$walletTransaction = new WalletTransaction();
$walletTransaction->setTransaction($transaction);;
$walletTransaction->wallet_id = $wallet->id;
$walletTransaction->amount = 5000;
$walletTransaction->description = 'Bonus credit';
if ($walletTransaction1->save() == false) {
$transaction->rollback("Cannot create transaction");
}
$walletTransaction1 = new WalletTransaction();
$walletTransaction1->setTransaction($transaction);
$walletTransaction1->wallet_id = $wallet->id;
$walletTransaction1->amount = -1800;
$walletTransaction1->description = 'Apple store';
if ($walletTransaction1->save() == false) {
$transaction->rollback("Cannot create transaction");
}
$transaction->commit();
} catch(TxFailed $e) {
echo "Error: ", $e->getMessage();
}
}Summary
In this article, you learned something about ORM in general and how to use some of the main built-in methods to perform CRUD operations. You also learned about database transactions and how to use PHQL or raw SQL queries.
Resources for Article:
Further resources on this subject:
- Using Phalcon Models, Views, and Controllers[article]
- Your first FuelPHP application in 7 easy steps[article]
- PHP Magic Features [article]
