Download the PHP package stellarwp/db without Composer
On this page you can find all versions of the php package stellarwp/db. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Package db
Short Description A WPDB wrapper and query builder library.
License GPL-2.0
Informations about the package db
StellarWP DB
A WPDB wrapper and query builder library. Authored by the development team at StellarWP and provided free for the WordPress community.
Inspired and largely forked from the GiveWP codebase!
Installation
It's recommended that you install DB as a project dependency via Composer:
We actually recommend that this library gets included in your project using Strauss.
Luckily, adding Strauss to your
composer.json
is only slightly more complicated than adding a typical dependency, so checkout our strauss docs.
Table of contents
-
Quick start
-
Configuration
-
DB
-
Select statements
-
From Clause
-
Joins
- LEFT Join
- RIGHT Join
- INNER Join
- Join Raw
- Advanced Join Clauses
-
Unions
-
Where Clauses
- Where
- Where IN
- Where BETWEEN
- Where LIKE
- Where IS NULL
- Where EXISTS
- Subquery Where Clauses
- Nested Where Clauses
-
Ordering, Grouping, Limit & Offset
- Ordering
- Grouping
- Limit & Offset
-
Special methods for working with meta tables
- attachMeta
- configureMetaTable
-
CRUD
- Insert
- Update
- Upsert
- Delete
- Get
-
Inherited from
$wpdb
get_var()
get_col()
esc_like()
remove_placeholder_escape()
- Aggregate Functions
- Count
- Sum
- Avg
- Min
- Max
Quick start
Getting up and running with this library is easy. You'll want to initialize the DB
class. Doing so during the plugins_loaded
action is a reasonable location, though you can do it anywhere that feels appropriate.
For this example and all future ones, let's assume you have included this library with Strauss and your project's namespace is Boom\Shakalaka
.
The two main classes that make up the core of this library are the DB
class and the QueryBuilder
class. Here are their namespaces:
Configuration
This library provides default hooks and exceptions, however, if you have additional needs for your own application, you can override one or both via the StellarWP\DB\Config
class:
DB
DB
class is a static decorator for the $wpdb
class, but it has a few methods that are exceptions to that.
Methods DB::table()
and DB::raw()
.
DB::table()
is a static facade for the QueryBuilder
class, and it accepts two string arguments, $tableName
and $tableAlias
.
Under the hood, DB::table()
will create a new QueryBuilder
instance, and it will use QueryBuilder::from
method to set the table name. Calling QueryBuilder::from
when using DB::table
method will return an unexpected result. Basically, we are telling the QueryBuilder
that we want to select data from two tables.
Important
When using DB::table(tableName)
method, the tableName
is prefixed with $wpdb->prefix
. To bypass that, you can
use DB::raw
method which will tell QueryBuilder
not to prefix the table name.
Select statements
Available methods - select / selectRaw / distinct
By using the QueryBuilder::select
method, you can specify a custom SELECT
statement for the query.
Generated SQL
You can also specify the column alias by providing an array [column, alias] to the QueryBuilder::select
method.
Generated SQL:
The distinct method allows you to force the query to return distinct results:
You can also specify a custom SELECT
statement with QueryBuilder::selectRaw
method. This method accepts an optional array of
bindings as its second argument.
Generated SQL
By default, all columns will be selected from a database table.
Generated SQL
From clause
By using the QueryBuilder::from()
method, you can specify a custom FROM
clause for the query.
Set multiple FROM
clauses
Generated SQL
Important
Table name is prefixed with $wpdb->prefix
. To bypass that, you can
use DB::raw
method which will tell QueryBuilder
not to prefix the table name.
Joins
The Query Builder may also be used to add JOIN
clauses to your queries.
Available methods - leftJoin / rightJoin / innerJoin / joinRaw / join
LEFT Join
LEFT JOIN
clause.
Generated SQL
RIGHT Join
RIGHT JOIN
clause.
Generated SQL
INNER Join
INNER JOIN
clause.
Generated SQL
Join Raw
Insert a raw expression into query.
Generated SQL
Advanced Join Clauses
The closure will receive a Give\Framework\QueryBuilder\JoinQueryBuilder
instance
Generated SQL
Unions
The Query Builder also provides a convenient method to "union" two or more queries together.
Available methods - union / unionAll
Union
Generated SQL:
Where Clauses
You may use the Query Builder's where
method to add WHERE
clauses to the query.
Where
Available methods - where / orWhere
Generated SQL
Using where
multiple times.
Generated SQL
Where IN Clauses
Available methods - whereIn / orWhereIn / whereNotIn / orWhereNotIn
The QueryBuilder::whereIn
method verifies that a given column's value is contained within the given array:
Generated SQL
You can also pass a closure as the second argument which will generate a subquery.
The closure will receive a Give\Framework\QueryBuilder\QueryBuilder
instance
Generated SQL
Where BETWEEN Clauses
The QueryBuilder::whereBetween
method verifies that a column's value is between two values:
Available methods - whereBetween / orWhereBetween / whereNotBetween / orWhereNotBetween
Generated SQL
Where LIKE Clauses
The QueryBuilder::whereLike
method searches for a specified pattern in a column.
Available methods - whereLike / orWhereLike / whereNotLike / orWhereNotLike
Generated SQL
Where IS NULL Clauses
The QueryBuilder::whereIsNull
method verifies that a column's value is NULL
Available methods - whereIsNull / orWhereIsNull / whereIsNotNull / orWhereIsNotNull
Generated SQL
Where EXISTS Clauses
The QueryBuilder::whereExists
method allows you to write WHERE EXISTS
SQL clauses. The QueryBuilder::whereExists
method accepts a closure which will receive a QueryBuilder
instance.
Available methods - whereExists / whereNotExists
Generated SQL
Subquery Where Clauses
Sometimes you may need to construct a WHERE
clause that compares the results of a subquery to a given value.
Generated SQL
Nested Where Clauses
Sometimes you may need to construct a WHERE
clause that has nested WHERE clauses.
The closure will receive a Give\Framework\QueryBuilder\WhereQueryBuilder
instance
Generated SQL
Ordering, Grouping, Limit & Offset
Ordering
The QueryBuilder::orderBy
method allows you to sort the results of the query by a given column.
Generated SQL
Sorting result by multiple columns
Generated SQL
Grouping
The QueryBuilder::groupBy
and QueryBuilder::having*
methods are used to group the query results.
Available methods - groupBy / having / orHaving / havingCount / orHavingCount / havingMin / orHavingMin / havingMax / orHavingMax / havingAvg / orHavingAvg / havingSum / orHavingSum / havingRaw
Generated SQL
Limit & Offset
Limit the number of results returned from the query.
Available methods - limit / offset
Generated SQL
Special methods for working with meta tables
Query Builder has a few special methods for abstracting the work with meta tables.
attachMeta
attachMeta
is used to include meta table _metakey column values as columns in the SELECT
statement.
Under the hood QueryBuilder::attachMeta
will add join clause for each defined meta_key
column. And each column will be
added in select statement as well, which means the meta columns will be returned in query result. Aliasing meta columns
is recommended when using QueryBuilder::attachMeta
method.
Generated SQL:
Returned result:
Fetch multiple instances of the same meta key
Sometimes we need to fetch multiple instances of the same meta key. This is possible by setting the third parameter to true
, example ['additional_email', 'additionalEmails', true]
Generated SQL:
Returned result:
Instances with the same key, in this case additional_email
, will be concatenated into JSON array string.
configureMetaTable
By default, QueryBuilder::attachMeta
will use meta_key
, and meta_value
as meta table column names, but that sometimes might not be the case.
With QueryBuilder::configureMetaTable
you can define a custom meta_key
and meta_value
column names.
Generated SQL
CRUD
Insert
The QueryBuilder also provides QueryBuilder::insert
method that may be used to insert records into the database table.
Update
In addition to inserting records into the database, the QueryBuilder can also update existing records using the QueryBuilder::update
method.
Upsert
The QueryBuilder::upsert
method may be used to update an existing record or create a new record if it doesn't exist.
Delete
The QueryBuilder::delete
method may be used to delete records from the table.
Get
Available methods - get / getAll
Get single row
Get all rows
Inherited from $wpdb
As this is a wrapper for $wpdb
, you are able to call all of the methods that $wpdb
exposes as well. You simply will need to match the signature of the $wpdb
methods when doing so.
While all methods are supported, get_var()
, get_col()
, esc_like()
, and remove_placeholder_escape()
are likely of the most interest as there are not equilavents within the library itself.
get_var()
Gets the single meta_value
column for the given query.
get_col()
Returns an array of values for the column for the given query.
esc_like()
Escapes a string with a percent sign in it so it can be safely used with Where LIKE without the percent sign being interpreted as a wildcard character.
remove_placeholder_escape()
Removes the placeholder escape strings from a SQL query.
$wpdb
generates placeholders such as {abb19424319f69be9475708db0d2cbb780cb2dc2375bcb2657c701709ff71a9f}
that it escapes %
with when generating a SQL query. This library, as a $wpdb
wrapper, does that as well.
Using DB::remove_placeholder_escape()
will swap those back out for %
, which can be useful if you ever need to display the query in a more human-friendly format.
Aggregate Functions
The Query Builder also provides a variety of methods for retrieving aggregate values like count
, sum
, avg
, min
and max
.
Count
Count rows where provided column is not null.
Sum
Avg
Min
Max
Acknowledgements
Props to the GiveWP team for creating this library!