In a recent post to his site Jonathan Reinink has written up a guide to using dynamic (Eloquent) relationships in Laravel applications by making use of subquery functionality. In it, he shows how to make use of the selectSub method to select additional information in a single query versus having the overhead of custom, hard-coded relationships.

When building web apps that interact with a database, I always have two goals in mind: keep database queries to a minimum [and] keep memory usage to a minimum. These goals can have a drastic impact on the performance of your app.

Developers are typically pretty good at the first goal. We're aware of N+1 style problems, and use techniques like eager-loading to limit database queries. However, we're not always the best at the second goalkeeping memory usage down. In fact, we sometimes do more harm than good trying to reduce database queries at the expense of memory usage.

He starts off with the challenge he's trying to solve: gathering login information for users in a performant way. He includes the schema for the users and logins table and shows the code of how a normal relationship select might look to get login information for each user (creating an N+1 issue).

To help solve the issue, they try caching the last login information but realize they can do better - this is where subqueries come in. They provide an example of using the selectSub method to get the login information, mapping it to a macro for easier use and defining scopes. Finally, the tutorial shows how to use this method to select information via dynamic relationships. It also talks about lazy-loading issues and if the same thing could be accomplished with a "has one" relationship.

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.

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.

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.

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.

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).

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.

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.

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.

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.