Download the PHP package jayrods/query-builder without Composer

QueryBuilder

A lightweight, straight-forward and easy-to-use SQL query builder for DML and DQL queries.

About

Writing SQL queries by hand is subject of great concern amongst developers! It not just let your code 'dirty' (as some PHP purists might say) but also affects testability, simplicity and impose more work and time over development. With this in mind, this package comes in handy with a simple approach of wrapping SQL queries into PHP classes and methods, providing an abstraction with easy-to-use syntax and extra features to assert the queries are being written accordingly.

Installation

Installation is super-easy via Composer:

or add it by hand to your composer.json file.

Upgrading

We follow semantic versioning, which means breaking changes may occur between major releases. We would introduce upgrading guides whenever major version releases becomes available here.

Getting Started

Before start using the component, it's important to know how it is structured.

The QueryBuilder component is divided into 4 different use-cases, each representing one CRUD operation (Create, Read, Update and Delete). For simplicity sake, the component uses a QueryBuilder factory object to create all builder kinds with no trouble.

Follow bellow an example script using the component:

Example 01

Output:

NOTE: The create() method demands an argument informing which use-case to apply. In this case it is strongly recommended to use the available QueryBuilder object constants, as follow bellow:

NOTE: By convention, the component works ONLY with parameterized values, following by the notation :parameter or ?, as could be seen in Parameterized options.

NOTE: The build() method returns the built query and save it internaly, providing a query() method to retrieve the query whenever you whish. It also resets all other object's properties to default, enabling it to promptly start building another query if necessary.

ATTENTION! Calling the build() method a second time will override the previously saved query.

NOTE: The component also enables partial construction of the query, providing more flexibility for the user:

Output:

Partial construction simplifies certain cases where a SQL query depends on certain conditions as in the example bellow:

Output:

NOTE: It is important to say that EACH USE-CASE INSTANCE HAS ITS OWN SET OF METHODS, that could differ from each other either if the methods have the same name. In the next examples you could notice the difference of called methods for each use-case:

Example 02

Output:

Example 03

Output:

Example 04

Output:

More detailed explanation for each use-case could be seen on the sections bellow:

• Building DELETE Queries.
• Building INSERT Queries.
• Building SELECT Queries.
• Building UPDATE Queries.

DELETE Queries

Methods

Examples

Example 01

Output:

Example 02

Output:

Example 03

Output:

INSERT Queries

Methods

Examples

Example 01

Output:

SELECT Queries

Methods

Examples

Example 01

Output:

NOTE: In the SELECT use-case, whenever the user don't assign any columns, the builder understands that all columns should be retrieved, applying the * syntaxs.

Example 02

Output:

Example 03

Output:

UPDATE Queries

Methods

Examples

Example 01

Output:

Advanced Options

The QueryBuilder component also provides features to help assert the correct building of a query, by throwing errors on inconsistency, echoing console warnings on inconsistency and ignoring unexpected on wrong build methods calling.

The principal helper feature is the Constrained QueryBuilder Mode See Constrained Mode. It's major function is to ignore missplaced build methods to avoid wrong query writing, and also allows the component to inform the user throught Exceptions thrown or console messages echoing during runtime.

All different modes could be enabled using Environment variables or config file settings, as could be seen Here - Environment Variables and Here - Configuration File, respectively

Constrained Mode

The constrained mode's major function is to ignore missplaced build methods, avoiding wrong query writing. It also allows the component to inform the errors occuried and the action taken throught Exceptions thrown or console messages echoing during runtime.

The constrained mode could be enabled/disabled by two ways:

First, setting the environment variable QB_ENABLE_CONSTRAINED_MODE in a .env file;

Second, overriding the config file ENABLE_CONSTRAINED_MODE param. More about config file here

NOTE: By default, the constrained mode is set to true.

With the constrained mode on (QB_ENABLE_CONSTRAINED_MODE=true), the user have two more available options:

Enable\disable Constrained mode to throw an Exception on fail, throught the env variable QB_FAIL_ON_WRONG_METHOD_CALL;

Enable\disable Constrained mode to echo message on fail, throught the env variable QB_ECHO_WARNINGS_ON_WRONG_METHOD_CALL;

NOTE: By default:

IMPORTANT! As the constrained mode is used to assert the right construction of the query, we recommend to use it only for development, and disable the constrained mode for production to make the component more performatic.

Environment Variables Options

Follow bellow all the available environment variables with their default value:

Parameterized Options

Config File

In addition, it is possible to set all component's options throught a queryBuilderConfig.php file. By default, the component uses its config file available in the package ./config folder, however there is a possibility to puslish such file in the root ./config folder, or even in a user-defined directory See.

To use a config file in a user-defined directory, it makes necessary to inform the user-defined path to the QueryBuilder object, like the example bellow:

Publishing Config File

The component make available a bin script to publish correctly the queryBuilderConfig.php file. The user can puslish the config file by typing the following commands at the console:

Without argument, attempt to create config file on ./config/ folder, if exists

With argument, attempt to create on the passed folder, if exists

Contributing

We appreciate the kindness of any developer willing to contribute to this project to make it complete enough to real-project use, by suggesting or adding new features, covering vulnerability and other nature issues. If you are willing to contribute to this project, please send an email to jayrods.

Security

If you discover a security vulnerability within this package, please report the issue or send an email to jayrods. All security vulnerabilities will be promptly addressed. We appreciate your concern.

License

QueryBuilder is licensed under The GPL V3.0 License.