Download the PHP package michel/php-sql-mapper without Composer

PHP SQL Mapper

A powerful PHP library that not only builds complex SQL queries but also maps the results into structured object graphs. It simplifies handling relational data by automatically transforming flat result sets from joins into nested arrays, making it ideal for working with one-to-one and one-to-many relationships.

Installation

You can install this library via Composer. Make sure your project meets the minimum PHP version requirement of 7.4 or higher.

Usage

The SQL Query Builder library allows you to build SQL queries fluently using an object-oriented approach. Here are some examples of usage:

Creating a SELECT Query

Types of SQL Joins with QueryBuilder

The SQL Query Builder library supports various types of JOIN operations to combine rows from multiple tables based on a related column between them. Below are examples of different JOIN types you can use with QueryBuilder:

1. INNER JOIN

An INNER JOIN returns records that have matching values in both tables.

2. LEFT JOIN

A LEFT JOIN returns all records from the left table (first table) and the matched records from the right table (second table). If there is no match, the result is NULL on the right side.

3. RIGHT JOIN

A RIGHT JOIN returns all records from the right table (second table) and the matched records from the left table (first table). If there is no match, the result is NULL on the left side.

Creating a SELECT Query with DISTINCT

You can use the distinct() method to specify a SELECT DISTINCT query with QueryBuilder.

Creating a SELECT Query with GROUP BY

You can use the groupBy() method to specify a GROUP BY clause with QueryBuilder.

Creating a SELECT Query with HAVING Clause

You can use the having() method to specify a HAVING clause with QueryBuilder.

Creating an INSERT Query

Creating an UPDATE Query

Creating an DELETE Query

Creating a SELECT Query with Custom Expression

List of Available Expressions (Expr)

Here is a comprehensive list of available static methods in the Expr class along with examples demonstrating their usage:

Expr::equal(string $key, string $value)

Expr::notEqual(string $key, string $value)

Expr::greaterThan(string $key, string $value)

Expr::greaterThanEqual(string $key, string $value)

Expr::lowerThan(string $key, string $value)

Expr::lowerThanEqual(string $key, string $value)

Expr::isNull(string $key)

Expr::isNotNull(string $key)

Expr::in(string $key, array $values)

Expr::notIn(string $key, array $values)

These examples demonstrate how to use each Expr class method to generate SQL expressions for various comparison and conditional operations. Incorporate these methods into your SQL Query Builder usage to construct complex and precise SQL queries effectively.

Relational Data Mapping with JoinQL

While the QueryBuilder is excellent for creating raw SQL queries, the JoinQL class provides a higher-level abstraction to automatically handle relational data. It is designed to transform flat result sets from complex queries with JOINs into structured, nested arrays (or object graphs), making it incredibly easy to work with one-to-one and one-to-many relationships.

How It Works

JoinQL builds upon the Select query builder but adds a layer of "graph-aware" logic. When you define a join, you also specify the nature of the relationship (e.g., one-to-many) and the desired key for the nested data. After executing the query, JoinQL processes the results and intelligently groups child rows under their parent entities.

Example: Fetching a User and All Their Posts (One-to-Many)

Imagine you have a users table and a posts table. Here’s how you can fetch a user and embed all their posts directly in the result, without manually looping through the results.

Example: Fetching a Post and Its Author (One-to-One)

Here is how to handle a one-to-one relationship, where a post has a single author.

By using JoinQL, you delegate the complex task of structuring relational data to the library, resulting in cleaner, more readable application code.

Features

• Fluent generation of SELECT, INSERT, UPDATE, and DELETE queries.
• Secure SQL query building to prevent SQL injection vulnerabilities.
• Support for WHERE, ORDER BY, GROUP BY, HAVING, LIMIT, and JOIN clauses.
• Simplified methods for creating custom SQL expressions.

License

This library is open-source software licensed under the MIT license.