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.

Tim MacDonald has a new post to his site sharing methods that the Laravel Eloquent users (either in the framework or outside of it) can use to load in the counts of relationships without having to fetch the entire relationship data set.

It is often useful to show the number of related models a given instance has, but not actually need any specific information about the related models, just how many exist. In this scenario you do not want to load all of the related models into memory to count them, we want our database to do the heavy lifting for us. Laravel offers a number of ways to retrieve relationship counts. 2 have been around for a while, but there is a new kid on the block.

He looks at three methods you can use to get these counts: via the query builder manually, directly on the relationship and, more recently added, from an eloquent collection. He goes through each of these methods, providing a summary of the technique and code examples showing how it's implemented.

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.

On the Sticher.io blog Brent has written up a post covering the use of MySQL views in Eloquent, the database ORM that's included with the Laravel framework.

MySQL views are a way of storing queries on the database level, and producing virtual tables with them. In this post we'll look at why you want to use them and how they can be integrated in Laravel with Eloquent models.

If you're already convinced of the power of MySQL views, or just want to know how to implement them in Laravel, you're free to skip ahead.

For those not familiar with the concept of "views" in MySQL, he spends a little time explaining what they are and what benefits they bring to the table. This includes a code example of a migration to create one and how something similar could be achieved with event hooks on a Laravel model. He then gets into the use of the views with Laravel, refactoring a more complex SELECT query into a view and creating/removing it using the same migration methods as any other table in the database.

The Pineco.de blog has a tutorial posted showing Laravel users how to append API and web routes to Eloquent models directly without the need for setting up additional route handling.

As a level zero, we can agree on using the RESTful URI scheme. That means every model has an associated route. We can perform different actions on the model, depending on the request type.

It can be a bit painful to concatenate the strings and IDs all the time, so we could append this URL as an attribute of the model. That means we could calculate the URL behind the scenes, and use it on the PHP or the JS side as well. [...] Then only difference (in this simple example) between the API and the web routes is that the API routes have a /api prefix in the URL.

The tutorial then introduces their custom Routable trait that can be included in any model to handle requests on both the API and web side (differentiated by the "api" prefix). The last step is to append the routes to the model itself using the appends Eloquent property.

In a new post to his site site Joseph Silber shows you how, when using Eloquent in a Laravel application, to decouple your application from your database by removing hard-coded class names on polymorphic relationships.

Polymorphic relations let you set up a relationship between many different model types, without the need for extra tables. This works by storing the "morphable type" (explained below) in the database, in addition to the morphable type's ID.

By default, the morphable type stored in the database is the model's full class name. While this works, it tightly couples your database to your PHP application. Let's look at how we can instruct Eloquent to use more generic values for these morphable types.

He starts in with a "short refresher" on polymorphic relationships and what Eloquent models look like for a simple customer-to-address relationship. He then talks some about the "morph" type and how Eloquent stores the name of the relating model directly in the record (like AppCustomer or AppWarehouse). He shows how to customize the morph type to map the types to values in a morphMap setting to remove the need for those hard-coded class names. He wraps up the post answering the question many ask: "why doesn't Eloquent do this automatically?"

In this new post to his site Freek Van der Herten shares a time when he was working on a data import that ended in some unexpected results thanks to an interesting race condition.

Recently I was working on a client project where a data import was performed via queues. Each record was imported in its own queued job using multiple queue workers. After the data import was done we had more rows than expected in the database. In this blogpost I'd like to explain why that happened.

He starts by digging into the code that made use of the firstOrCreate method in Laravel's Eloquent handling to find if an entry had already been created for the given data. The method uses two queries, one to determine if the record exists and another to create it if not. The issue was with the fact that it was being handled in a queue meaning that the select could happen and return false while another process was creating the record. He even created a demo app to show it happening and includes screenshots showing the result. He recommends moving the process to a separate queue and having only one worker executing at a time. There's not a good code-based solution for it as it's more of an issue with the architecture than the application itself.

Junior Grossi has posted a tutorial that covers the idea of data filtering with Eloquent models. In this case, the filtering is based on user input from a URL with parameters matching the properties on the model.

Filtering models was, for a very long time, a hard task for me. I admit that I could not think in some easy way to do that. I tried, refactored some code, created custom classes for that, but I never thought how this could be easily implemented.

Watching a Laracast’s video from 2016 about the Laravel’s Eloquent ORM I faced of with a bunch of classes and a trait that removed a lot of trash from my controller actions. That was called by Jeffrey Way the QueryFilter.

He then gets into some of the goals behind the filtering and the expected input method (URL parameters). He then creates a simple Laravel application making use of Corcel to integrate with his current WordPress backend database. He includes code examples showing the creation of a Post model and controller and returning only the desired fields using a JSON response and a toArray method. He then moves on to the filtering, starting with a more hard-coded version of the search: adding a where statement to the query manually before the get.

To replace this with something more flexible, he implements the QueryFilter class that can be extended to match the requirements for the model type. He then implements the PostFilter class, adding methods for "status" and "title" fields. Finally he adds in a scopeFilter method that makes it simpler to call the filtering directly from the model instance.

The Laravel News site has a quick tutorial posted showing you how you can use Eloquent functionality to help prepare your URLs and make them easier to maintain across the application. The key is in the use of "presenters".

It’s not uncommon to have tens, if not hundreds of views in a Laravel application. Something that soon gets out of hand is the various references to routes. [...] If for whatever reason we have to make a change to either the route alias or default query string values you’ll soon find yourself doing mass string replacements across your entire application which brings the risk of breakage within many files.

What can we do to possibly better handle this? There are a couple of different approaches.

They provide two approaches, one being slightly more complex (but flexible) than the other. The first makes use of only Eloquent to define a getUrlAttributes method in the model. The second method abstracts this functionality out to a "URL Presenter", a class that defines methods for each of the CRUD actions and returns the correct route for each. The getUrlAttribute then returns an instance of this instead, making it easy to reference the method and route required in the Blade template.

On the Laravel News site there's a new post sharing twenty Eloquent tips for the Laravel users out there.

Eloquent ORM seems like a simple mechanism, but under the hood, there’s a lot of semi-hidden functions and less-known ways to achieve more with it. In this article, I will show you a few tricks.

Among the tips and "hidden features" mentioned are tips about:

• Increments and Decrements
• Model boot() method
• Model properties: timestamps, appends etc.
• Order by relationship
• Order by Mutator
• Raw query methods
• Create additional things when creating a model

...and many more. There's code examples for each of them showing them in use too.