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

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/

SitePoint PHP Blog:
Monitoring WordPress Apps with the ELK Stack
Jun 13, 2016 @ 13:21:25

On the SitePoint PHP blog there's a new tutorial from Daniel Berman showing you how to use the ELK stack to monitor WordPress applications, a combination of Elastic Search, Logstash and Kibana. You can find out about setting this stack up in this previous tutorial.

When something does go wrong, one of the first things you’re going to want to look at are the log files. Not because you enjoy it — log files are not easy to decipher — but because they contain valuable information that can shed light on what exactly occurred.

While [you can use the WP Log Viewer], analyzing WordPress and PHP logs is simply not enough. There are also web server and database logs to sift through. To successfully query huge volumes of log messages coming in from various sources and identify correlations, a more solid solution is required.

Enter the ELK Stack.

He shows how to enable the logging features in your WordPress application (in the wp-config file) and ensure they're not output to the users of your site. He then shows you how to install Filebeat to ship the logs over to the ELK stack. He switches over to the ELK side and shows the configuration needed on Logstash to properly handle the WordPress log format. The remainder of the post helps you get started analyzing the log results and limit it down to only what you need via the Kibana query syntax.

tagged: elk stack monitor wordpress elasticsearch kibana logstash tutorial query

Link: https://www.sitepoint.com/monitoring-wordpress-apps-with-the-elk-stack/

Symfony Finland:
GraphQL with PHP and the Symfony Framework
May 16, 2016 @ 12:19:09

The Symfony Finland site has a recent post giving an overview of GraphQL and Symfony, combing the GraphQL query language (RESTish handling) from Facebook with your application.

The origins of GraphQL stem from the needs that Facebook's mobile applications had (and continue to have). They needed a data-fetching API that was flexible enough to describe all the different kinds of data that the social network had available. [...] Back in September 2015 GraphQL was already powering Billions of API calls a day at Facebook. [...] The core idea of GraphQL is to send a simple string to the server. This string is then interpreted by the server and it sends back a JSON payload that responds to follows the structure of the query itself.

The post includes an example of what the request and response from a GraphQL query might look like for a social network's data. They also link to several PHP libraries that have come up around the functionality making it easier to integrate. There's also links to some Symfony bundles that provide functionality to make your own GraphQL servers.

tagged: graphql symfony bundle introduction facebook rest query json library

Link: https://www.symfony.fi/entry/graphql-with-php-and-the-symfony-framework