Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

Matthias Noback:
Test-driving repository classes - Part 1: Queries
Sep 25, 2018 @ 10:28:31

Matthias Noback has kicked off a new series of posts on his site covering the use of the repository design pattern in different situations. In this first post he focuses on "test driving" classes for handing database queries and their results.

A test for a repository can't be a unit test; that wouldn't make sense. You'd leave a lot of assumptions untested. So, no mocking is allowed.

[...] But how do you test everything that is going on in a repository? Well, I found out a nice way of doing so, one that even allows you to use some kind of test-driven approach. In this article I'll cover one of the two main use cases for repositories: querying the database, and returning some objects for it. The other use case - storing and loading objects - will be discussed in another article.

He starts by getting everyone on the same page with a definition of a "query" and how it relates back to a repository class. He then walks through the process of how to test the class, first as a general "get all" query then with a check on the "active" state. Once the test goes green (successful), he adds more variations to both the tests and fixtures. There's not a lot of code examples in this post but it does show some good concepts to get you headed down the right path.

tagged: tutorial repository designpattern query database part1 series

Link: https://matthiasnoback.nl/2018/09/test-driving-repository-classes-part-1-queries/

Use Laravel Eloquent Query Builder In Any PHP Project
Sep 04, 2018 @ 10:30:49

On the CodeWall.co.uk site there's a new tutorial showing you how to use Eloquent outside of Laravel applications thanks to its "capsule" functionality. Eloquent is an ORM layer that's a part of the Laravel framework and makes it easier to work with records and sets of data from your database.

OWASP (Open Web Application Security Project) is a project that notes down the current threats to a web application. And I have been researching on their site and I have found this similarity in their 2010, 2013 and 2017 report that, SQL Injection or any other type of Injection is number 1 on this list, every time.

And that’s a part to worry.

This can cause you to get out of business, so this is pretty serious and your organisation should take care of the this issues and prevent yourself from it.

The tutorial starts with a brief introduction of what SQL injection is including some example SQL to show how the injection happens. It then covers how to prevent this issue with base PHP code (no framework or package) using prepared statements and bound parameters. Eloquent takes care of this for you and provides a lot of other handy features. The article goes on to show:

  • how to install the Eloquent packages with Composer
  • the code to create the "capsule" that's used as a bridge into the Eloquent code
  • the creation of migrations for two tables: users and posts
  • how to make models for these two tables

The post wraps up with a look at using these models to create a new user and post record using the ORM interface rather than manual SQL statements.

tagged: tutorial laravel eloquent query orm introduction model capsule

Link: https://www.codewall.co.uk/use-laravel-eloquent-query-builder-in-any-php-project/

Implementing Custom Logic With Raw SQL In Laravel’s Query Builder
Jul 04, 2018 @ 12:19:35

The Pineco.de site has a new tutorial posted showing you how to implement custom logic with raw SQL in the Eloquent query builder in the Laravel framework.

Laravel’s query builder offers a nice way to work with raw SQL. We can use them in our where conditions and also in our orderings as well. Let’s see some examples where we can use raw SQL to implement custom logic for ordering the results.

The post starts with a brief mention of the difference between sorting and ordering results, noting that one happens on the SQL server and the other on the results collection. Next they show examples using raw SQL to order a query using both a simple and more complex condition. There's also a link to the official documentation for the raw methods for more information and examples.

tagged: custom logic raw sql laravel query builder tutorial

Link: https://pineco.de/implementing-custom-logic-with-raw-sql-in-laravels-query-builder/

Junior Grossi:
Querying and Eager Loading complex relations in Laravel
May 15, 2018 @ 10:15:05

Junior Grossi has a tutorial posted to his site for the Laravel (well, Eloquent) users out there showing how to work with querying and eager loading complex relationships to access the data from your database.

Laravel is a PHP framework that uses Eloquent, a powerful and amazing ORM that allows you to do complex SQL queries in a very easy way. But sometimes you need more, and here I’m gonna give you an interesting tip that can bring you a lot of flexibility.

He sets up the situation where, as an application grows its needs for interaction with the data evolves and becomes more complex. Laravel (Eloquent) comes equipped with some tools that can help with this. To illustrate, he outlines a basic "blog" application with Post and Comment types and their relations. While it's simple to get the comments for a post, querying them can get a little more complex. He provides some examples using whereHas/orWhereHas but points out an issue with the results (all comments are returned, not just the ones matching the queried posts).

The solution he proposes is to eager load them instead. His example code still uses the whereHas but adds the comments to a temporary variable which is then filtered via a with on the query.

tagged: laravel complex relationship query filter tutorial

Link: https://blog.jgrossi.com/2018/querying-and-eager-loading-complex-relations-in-laravel/

Robert Basic:
Prooph query bus
Dec 21, 2017 @ 09:52:19

In a post to his site Robert Basic continues his series looking at the Phrooph package, this time focusing on the query bus. This functionality allows you to dispatch an event to a single "finder" in the CQRS/event souring framework.

Continuing on with the The query bus allows the handler to do whatever it needs to do to return the result, synchronously or asynchronously.

He starts by talking about the return value of the bus - a ReactPHP promise for the async handling - and the plugin system that allows for more advanced handling. He then starts on the example, showing how to create a simple bus object and define the routing to a specific query handler. This is then dispatched and a closure is defined as the "done" operation. From this basic example he then moves to something a bit more useful - an example query to determine how may Calls for Papers are currently open on the Joind.in service (fetched via the API).

tagged: prooph example tutorial querybus query routing handler

Link: https://robertbasic.com/blog/prooph-query-bus/

Delicious Brains:
SQL Query Optimization for Faster Sites
Nov 07, 2017 @ 09:53:47

On the Delicious Brains site they've posted a tutorial that provides some helpful tips and tricks for optimizing your SQL queries on your WordPress sites with the help of some basic techniques and software to analyze your current use.

With dynamic database-driven websites like WordPress, you might still have one problem on your hands: database queries slowing down your site.

In this post, I’ll take you through how to identify the queries causing bottlenecks, how to understand the problems with them, along with quick fixes and other approaches to speed things up. I’ll be using an actual query we recently tackled that was slowing things down on the customer portal of deliciousbrains.com.

They then walk through some of the methods for locating the queries that could be causing issues including the use of the QueryMonitor plugin and the MySQL slow query log. They provide some tips for understanding what makes the query slow and how to use the MySQL Workbench tool to determine the structure of the database and look for optimizations. Finally they get into some of the things they did to solve their own issue including adding caching, better understanding the need of the query and "thinking outside the box" with indexes and normalization.

tagged: sql query optimization performance mysqlworkbench slowquerylog mysql wordpress

Link: https://deliciousbrains.com/sql-query-optimization/

SitePoint PHP Blog:
How to Optimize MySQL: Indexes, Slow Queries, Configuration
Oct 31, 2017 @ 10:46:11

On the SitePoint PHP blog Bruno Skvorc has a post that offers some helpful advice about optimizing your MySQL database through the use of indexes, monitoring slow queries and configuration options.

MySQL is still the world’s most popular relational database, and yet, it’s still the most unoptimized – many people leave it at default values, not bothering to investigate further. In this article, we’ll look at some MySQL optimization tips we’ve covered previously, and combine them with novelties that came out since.

He starts off with the configuration changes that can be used to optimize the database, tweaking settings for Innodb pool, handling variable inspection and using a tuning tool to determine the best settings. Next up comes the look at indexes covering the different kinds first: fulltext, descending, unique/primary and regular indexes. Finally he covers some of the usual bottlenecks seen in MySQL's use in web applications, showing how to monitor for them via the slow query log.

tagged: optimize mysql database application tutorial index slowquery query configuration

Link: https://www.sitepoint.com/optimize-mysql-indexes-slow-queries-configuration/

Laravel News:
Optimize Eloquent Queries with Eager Loading
Aug 11, 2017 @ 09:23:29

On the Laravel News site they've posted a tutorial sharing some of the basics around the optimizing Eloquent queries with the help of its own "eager loading" feature.

Object Relational mapping (ORM) makes working with databases amazingly simple. While defining database relationships in an object-oriented way makes it easy to query related model data, developers might not pay attention to the underlying database calls.

A standard database optimization for an ORM is eager-loading related data. We will set up some example relationships and then walk through how queries change with and without eager loading. I like to get my hands directly on code and experiment with things, and I hope to illustrate how eager loading works with some examples will further help you understand how to optimize your queries.

They start with a classic example of the "N+1 problem" when working with database records and how, without you knowing, you might be causing it with lazy loading. The article then talks about eager loading vs lazy loading and how they differ in most ORMs. It then covers Eloquent, setting up some migrations for an example blog application and creating the relationships between Author (user) and the Posts. The models are created and seeders are built to populate the tables with Faker data. Finally it gets to the use of eager loading, making use of the "with" functionality to modify the query structure behind the scenes. The post finishes with mentions of two other eager loading types - lazy eager loading and nested eager loading.

tagged: optimize query eager loading laravel eloquent performance nplusone

Link: https://laravel-news.com/eloquent-eager-loading

Laravel News:
Utilizing Laravel’s Cache with Query Params
Apr 04, 2017 @ 09:27:53

On the Laravel News site there's a quick tutorial posted showing you how to use Laravel's query cache with query params to help increase the performance of your application by caching query responses.

Laravel provides a very intuitive and useful means of caching the responses of your projects, whatever your project is (RESTful API, Web Platform, etc.). In general, Laravel can store in the cache system whatever data you send (HTML, JSON, collections, Eloquent instances, and similar) accordingly with a provided expiration time.

[...] The question here is “How does Laravel determine when to store data?”

They give an example of saving a "remember" key value to the cache manually using the "remember" method on the Cache facade. He then talks about what happens internally when the "remember" method is called to know if the data was previously cached. It then moves into the caching of data based on URL values and how query params would confuse things and not provide much benefit to the caching. Thanks to some internal handling the caching ignores the query params and returns the same data as before. This is an issue if you want the updated data but is relatively easily solved with a bit of code to sort the params and normalize the URL being used as the "remember" key.

tagged: laravel cache query params tutorial sort normalize

Link: https://laravel-news.com/cache-query-params

SitePoint PHP Blog:
Beaver in Action: Practical MySQL Optimization
Nov 11, 2016 @ 10:30:49

On the SitePoint PHP blog there's a tutorial posted showing how to optimize your MySQL handling with the help of the Beaver query logger package and the details it provides.

Clients with an existing application sometimes ask me to fix bugs, improve efficiency by speeding up the application, or add a new feature to some existing software. The first stage of this is researching the original code – so-called reverse engineering. With SQL databases, it is not always immediately obvious which SQL queries MySQL executed – especially if these queries were generated by a framework or some kind of external library. In this article, I will talk specifically about MySQL and present a common optimization use case which might come in handy if you run into a similar problem one day.

He shows how to update your MySQL installation to log all queries out to the log location of your choice. This log can then, in turn, be parsed by the Beaver package and provide details about what's happening in the query and where it could be optimized. The article also provides a more "real world" example of a query happening in a Yii2 application resulting in a large number of queries being generated. He shows how to update the query handling to make the loading more efficient (through joins rather than individual queries) and what the resulting statement looked like.

tagged: beaver package mysql optimization query analyze tutorial

Link: https://www.sitepoint.com/beaver-in-action-practical-mysql-optimization/