wp-forge / wp-query-builder
A lightweight and efficient SQL query builder for WordPress.
Installs: 54 609
Dependents: 0
Suggesters: 0
Security: 0
Stars: 2
Watchers: 1
Forks: 1
Open Issues: 0
Requires
- php: >=5.4
README
A lightweight and efficient SQL query builder for WordPress.
This is a fork of the original project (https://github.com/sultann/wp-query-builder) which appears to be unmaintained.
Installation
WP Query Builder uses PSR-4
autoloading and can be installed using composer:
$ composer require wp-forge/wp-query-builder
Documentation
Initialization
The init method takes a string argument. You can use that later to use actions/filters specific to your query builder instance.
Without argument.
$query = \WP_Forge\QueryBuilder\Query::init();
With argument
$query = \WP_Forge\QueryBuilder\Query::init('query_users');
Select
This will build the query, execute and returns all users from users table with applying table prefix automatically. by default, it selects all(*) but you can define what to select from the query; If you are selecting all then you can omit the select statement.
$results = $query->select('*') ->from('users') ->get();
Select specific column
$results = $query->select('ID') ->from('users') ->get();
Select multiple column
$results = $query->select('user_login, user_email') ->from('users') ->get();
Where conditions
For the next few examples, lets assume a larger dataset so that the queries make sense.
$results = $query->select('*') ->from('users') ->where('user_email', 'like', '%gmail.com') ->get();
Notice how omitting the operator in the first condition ->where('user_url', '') makes this default to =. By default all where conditions are defined with the and operator.
Different where operators:
$results = $query->select('*') ->from('users') ->where('user_email', 'like', '%gmail.com') ->orWhere('user_email', 'like', '%yahoo.com') ->get();
There are few more builtin Where conditions available
andWhere()
whereIn()
whereNotIn()
whereNull()
whereNotNull()
orWhereNull()
orWhereNotNull()
whereBetween()
whereNotBetween()
whereDateBetween()
whereRaw()
Where scopes
Allow you to group conditions:
$results = $query->select('*') ->from('posts') ->where('post_status', 'publish') ->where(function($q) { $q->where('menu_order', '>', 21); $q->where('menu_order', '<', 99); }) ->orWhere('post_type', 'page') ->get();
Where Between
$results = $query->select('*') ->from('posts') ->whereBetween('menu_order', 1, 20) ->get();
Where Not Between
$results = $query->select('*') ->from('posts') ->whereNotBetween('menu_order', 20, 30) ->get();
Where Date Between
$results = $query->select('*') ->from('posts') ->whereDateBetween('post_date', '2010-04-22 10:16:21', '2020-05-04') ->get();
Joins
By default, all joins are Left Join. Available join types 'LEFT', 'RIGHT', 'INNER', 'CROSS', 'LEFT OUTER', 'RIGHT OUTER' Joining tables:
$results = $query->select( '*' ) ->from( 'posts p' ) ->join( 'users u', 'u.ID', '=','p.post_author' ) ->get();
Joins scopes
Allow you to group conditions:
$results = $query->select( '*' ) ->from( 'posts p' ) ->join( 'users u', 'u.ID', '=','p.post_author' ) ->join('usermeta um', function($q) { $q->where('um.meta_key', 'first_name'); $q->where('um.met_value', 'like', '%sultan%'); }) ->get();
There are few more builtin join conditions available
leftJoin()
rightJoin()
innerJoin()
outerJoin()
Grouping
Grouping data:
$results = $query->select('*') ->from('posts') ->group_by('post_status') ->get();
Having
Group by with having data:
$results = $query->select('*') ->from('posts') ->group_by('post_status') ->having('count(ID)>1') ->get();
Ordering
Ordering data:
$results = $query->select('*') ->from('posts') ->order_by('post_title', 'DESC') ->get();
Limiting data
Limit and offset:
$results = $query->select('*') ->from('posts') ->limit(20, 10) ->get();
Only limit
$results = $query->select('*') ->from('posts') ->limit(20) ->get();
Offset as separate
$results = $query->select('*') ->from('posts') ->limit(20) ->offset(10) ->get();
Pagination
shortcut of limit and offset
$results = $query->select('*') ->from('posts') ->page(1, 20)//page number & page size ->get();
Find
find item with column value
$results = $query->select('*') ->from('posts') ->find(1, 'ID');
First
Get first item from the posts table
$results = $query->select('*') ->from('posts') ->first();
Last
Get last item from the posts table
$results = $query->select('*') ->from('posts') ->last();
Counting
count total rows
$results = $query->select('*') ->from('posts') ->count();
toSql
Out the query instead of executing
$results = $query->from('posts as p') ->join('users as u', 'p.post_author', 'u.ID') ->join('usermeta um', function($q) { $q->where('um.meta_key', 'first_name'); $q->where('um.met_value', 'like', '%sultan%'); }) ->toSql();
Update
Update a row
$results = $query->table('posts') ->where('ID', 20) ->update(['post_title' => 'updated']);
Delete
Delete a row
$results = $query->from('posts') ->where('ID', 20) ->delete();
Search
Search a value from columns
$results = $query->from('posts') ->search('Hello Word', array('post_title', 'post_content')) // it will search Hello & World both ->delete();