In a post to his site Christoph Rumpel shows the Laravel (well, Eloquent) users out there how to use groupBy at multiple levels for customizing and sorting the results of your database queries.

Since Laravel v5.5.29 you can group collections by multiple levels. Let's see what this means and how this works.

The post starts with some of the current functionality, showing how the groupBy could be used before with a simple collection to group student data by skill level. It then gets into the new functionality, modifying the example to group by both the skill level and by the "teacher" value. The result is a sorted set of results where the top level is the skill level and, inside of each, the teachers with students under that. The post finishes by "going crazy" and showing the grouping by event more criteria, adding "name" and "email" columns to the list.

Geoff Wozniak has written up a post on the "Curried lambda" site sharing his opinion on ORMs (object relational mappers) for working with databases and how, after using them in his own development work, that they're a good side benefit but shouldn't replace knowing SQL.

I've come to the conclusion that, for me, ORMs are more detriment than benefit. In short, they can be used to nicely augment working with SQL in a program, but they should not replace it.

[...] Neward, in his well known essay, lays out many cogent reasons why ORMs turn into quagmires. In my experience, I've had to deal directly with a fair number of them: entity identity issues, dual-schema problem, data retrieval mechanism concern, and the partial-object problem. I want to talk briefly about my experiences with these issues and add one of my own.

He breaks the rest of the article up into several sections, for each sharing some of his own experiences with the feature and how it could be resolved using other query methods:

Partial objects, attribute creep, and foreign keys

Data retrieval

Dual schema dangers

Identities

Transactions

He ends the post with a look forward, thinking about where he'll end up, mentioning stored procedures, queries as APIs and how "easy" isn't always best when it comes to ORMs.

Romans Malinovskis has a post on his Medium.com site sharing what he calls a "pragmatic approach to reinventing ORM" with his thoughts about a different approach to this commonly used database interface.

It’s been over a year, since I posted my first highly debated post on Reddit:
Reinventing the faulty ORM concept. Gathered information helped me design and implement an alternative pattern to ORM with many important advantages and that has reinforced my belief that ORM is faulty.

He's broken the article up into a few sections with details and thoughts for each along the way:

Why ORM is important?

Why ORM is broken?

How Agile Data differ in approach?

How Agile Data qualify production/enterprise use?

Notes on architectural decisions in Agile Data (OOP vs Decoupling)

That final section focuses mostly on the decoupling aspect and the Agile Data library (a Data Mapper) that can be used and solves some of the common ORM problems he mentioned in the earlier sections.

Today, we released zend-db 2.9.0! This is our first new feature release in over 18 months, and contains 7 bug fixes, 6 new features, numerous unit test additions, and many documentation improvements.

zend-db is an important component of many PHP projects, and we know that its support is crucial for many people. As such, we allocated a number of weeks to triaging the various open issues and patches (more than 50) to ensure we would provide a stable release.

The post lists out the issues fixed and the features added, linking to the bugs that were files related to each. It then talks about the future of the component, noting that a v3.0 release is expected to come in 2018 and will include a lot of new features. This will help to make working with more modern features of the various databases easier directly from the code (without as much manual query creation or hackery). The post finishes with some thanks to those that contributed to this v2.9.0 release.

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.

ORMs are great. In theory, they insure you against potential changes in your RDBMS and offer an easy plug’n’play solution for integration your domain code with database. In practice, however, since ORMs have to be compatible with all major vendors, their possibilities are often limited. Perks and tricks of specific RDBMS are left out.

One of such perks is so-called range types in PostgreSQL. There are plenty of cases when range types are applicable, but people (in my experience) often stick to old solution with two separate DATETIME columns. Last time I asked a fellow developer about the reason behind it and they said: ‘I use Doctrine in my project, and Doctrine does not support it’. So here we are.

He starts with a brief introduction to a custom type in Doctrine and a ranged type in PostgreSQL. He then shows how to create a custom "DateReangeType" to work with the ranged types and how add it to your Symfony configuration as a custom type. He ends the post with an example of it in use in an entity and in the client code making the database request. Full code can be found in this Github repository.

I want to create a cloud-based PostgreSQL database and connect it to Laravel's Eloquent in a Cloud Foundry application. This is how to do it.

He starts off by showing how to create the database instance on the Cloud Foundry platform via their command-line tool. Next he shows how to bind the database service to the application instance and get the credentials you'll need to connect from your application. Finally he moves over to the PHP side and shows how to configure Eloquent to connect to the database service using these credentials and make a sample query.

There is no such thing as disconnected information, no matter where you look – people, events, places, things, documents, applications and the information about them is all heavily connected. As the volume of data grows, so does the number and dynamicity of its connections. And if you’ve tried in the past to store and query that highly connected, semi-structured data in any database, you probably experienced a lot of challenges.

The tutorial talks some about graph databases, how they work and what kind of data fits into them best. It also briefly covers the Cypher Query Language followed by an introduction to the bundle and what it has to offer. Code examples of putting the bundle to use for queries are provided including the models to handle the results, relationships and an example application you can use to start with a working example.

We are very excited to announce that Phinx has joined the CakePHP team. The Github project has already been moved to the CakePHP organisation. The project itself will stay MIT-licensed but be gradually transformed into a Cake Software Foundation project. Other great news is that the current way to install and update Phinx remains unchanged.

As you are aware, CakePHP has been using Phinx since 3.0.0 for database migrations. The CakePHP Core team welcomes the opportunity to look after and maintain the project and will now start making changes to bring the code in line with the CakePHP (our) coding standards. As well as cleaning up issues and PR’s soon. We will be following up with our plans for the code and setting roadmaps in the coming weeks.

Rob Morgan, the original author of the library has also added some of his own commentary in a post to his site:

I’ve been busy lately. Juggling startups and open source work is no easy feat. I managed to do it for the past 5 years, but beyond 30 its proving to be more difficult. Phinx is not accelerating at the pace I’d like it to be. In fact so far this year we’ve only managed to ship 6 releases. I decided that the best strategy moving forwards is to find a new home for Phinx. One that has an active and loyal community and one that delivers great software. I’m pleased to announce that I’ve found the right fit.

He points out that the only real thing changing for now is the location of the repository. He looks back on the journey that got him and the project to where it is today and how much the support from the PHP community meant during that time.

You know how to search database fields, but the question is, How do we securely encrypt database fields but still use these fields in search queries?

Our secure solution is rather straightforward, but the path between most teams asking that question and discovering our straightforward solution is fraught with peril: bad designs, academic research projects, misleading marketing, and poor threat modeling.

They start off with some of the examples of bad ways to perform the searching of encrypted information, mostly around either using poor encryption levels or custom created encryption solutions. With those out of the way, the tutorial moves on to their recommended method: using an authenticated encryption scheme (libsodium) and blind indexing. The key to the method is to use a secondary column for the actual searching process, encrypting the value provided and running the search against that, not the encrypted value itself. The article then covers two questions that need to be asked before putting this method to use. The article ends with a method to enhance the previous searching to allow for "fuzzier" searching through the generation of some additional index values in a joined table.