Download the PHP package kaizencoders/wp-fluent without Composer
On this page you can find all versions of the php package kaizencoders/wp-fluent. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download kaizencoders/wp-fluent
More information about kaizencoders/wp-fluent
Files in kaizencoders/wp-fluent
Package wp-fluent
Short Description A lightweight, expressive database query builder for WordPress which can be referred to as a Database Abstraction Layer. WP Fluent uses the same **wpdb** instance and takes care of query sanitization, table prefixing and many other things with a unified API.
License LGPL-3.0-or-later
Homepage https://github.com/kaizencoders/wp-fluent
Informations about the package wp-fluent
A lightweight, expressive database query builder for WordPress which can be referred to as a Database Abstraction Layer. WP Fluent uses the same wpdb instance and takes care of query sanitization, table prefixing and many other things with a unified API.
It has some advanced features like:
- Query Events
- Nested Criteria
- Sub Queries
- Nested Queries
The syntax is quite similar to Laravel's query builder.
Example
Simple Query:
The query below returns the row where id = 3, null
if no rows.
Full Queries:
Query Events:
After the code below, every time a select query occurs on posts
table,
it will add this where criteria, so drafted posts don't surface.
There are so many advanced options documented below. Sold? Let's install.
Full Usage API
Table of Contents
- Connection
- Alias
- Query
- Select
- Get Easily
- Multiple Selects
- Select Distinct
- Get All
- Get First Row
- Get Rows Count
- Where
- Where In
- Where Between
- Where Null
- Grouped Where
- Group By and Order By
- Having
- Limit and Offset
- Join
- Multiple Join Criteria
- Raw Query
- Raw Expressions
- Insert
- Batch Insert
- Insert with ON DUPLICATE KEY statement
- Update
- Delete
- Transactions
- Get Built Query
- Sub Queries and Nested Queries
- Get PDO Instance
- Fetch results as objects of specified class
- Query Events
- Available Events
- Registering Events
- Removing Events
- Some Use Cases
- Notes
Connection
WP Fluent supports multiple database connections but you can use alias for only one connection at a time. Just pass the global wpdb and necessary configurations during connection.
Alias
When you create a connection:
MyAlias
is the name for the class alias you want to use e.g. MyAlias::table(...)
You can use any name (with Namespace also, MyNamespace\\MyClass
) you like or
you may skip it if you don't need an alias. Alias gives you the ability
to easily access the QueryBuilder class across your application.
When not using an alias you can instantiate the QueryBuilder handler separately, helpful for Dependency Injection and Testing.
Query
You must use table()
method before every query, except raw query()
.
To select from multiple tables just pass an array.
Get Easily
The query below returns the first row where id = 3, null
if no rows.
Access your row like, echo $row->name
. If your field name is not id
then
pass the field name as second parameter DB::table('my_table')->find(3, 'person_id');
The query below returns all the rows where name = 'Frost'
, null
if no rows.
Select
Multiple Selects
Using select method multiple times select('a')->select('b')
will also select a
and b
. It can be useful if you want to do conditional selects (within a PHP if
).
Select Distinct
Get All
Return an array.
You can loop through it like:
Get First Row
Returns the first row, or null
if there is no record. Using this method you can also
make sure if a record exists. Access it like $row->name
Get Rows Count
Where
Basic syntax is (fieldname, operator, value)
, if you give two parameters then =
operator is assumed. So where('name', 'admin')
and where('name', '=', 'admin')
are the same.
Where In
Where Between
Where Null
Grouped Where
Sometimes queries get complex, where you need grouped criteria, for example
WHERE age = 10 and (name like '%frost%' or description LIKE '%najrul%')
WP Fluent allows you to do so, you can nest as many closures as you need, like below.
Group By and Order By
Multiple Group By
Using groupBy()
or orderBy()
methods multiple times groupBy('a')->groupBy('b')
will also group by first a
and than b
. Can be useful if you want to do conditional
grouping (within a PHP if
). Same applies to orderBy()
.
Having
Limit and Offset
Join
Available methods,
- join() or innerJoin
- leftJoin()
- rightJoin()
If you need FULL OUTER
join or any other join, just pass it as 5th parameter to
join
method.
Multiple Join Criteria
If you need more than one criterion to join a table then pass a closure as second parameter.
Raw Query
You can always use raw queries if you need,
You can also pass your bindings
Raw Expressions
When you wrap an expression with raw()
method, WP Fluent doesn't try to sanitize these.
NOTE: Queries that run through query()
method are not sanitized until you pass
all values through bindings. Queries that run through raw()
method are not sanitized
either, you have to do it yourself. And of course these don't add table prefix too,
but you can use the addTablePrefix()
method.
Insert
insert()
method returns the insert id.
Batch Insert
In case of batch insert, it will return an array of insert ids.
Insert with ON DUPLICATE KEY statement
Update
It will update the name
field to Najrul
and description
field to
Famous Bengali poet.
where id
= 5
.
Delete
It will delete all the rows where id
is greater than 5
.
Transactions
WP Fluent has the ability to run database "transactions", in which all database changes are not saved until committed. That way, if something goes wrong or differently then you intend, the database changes are not saved and no changes are made.
Here's a basic transaction:
If this were to cause any errors (such as a duplicate name or some other such error), neither data set would show up in the database. If not, the changes would be successfully saved.
If you wish to manually commit or rollback your changes, you can use the
commit()
and rollback()
methods accordingly:
Get Built Query
Sometimes you may need to get the query string, its possible.
getQuery()
will return a query object, from this you can get sql, bindings or raw sql.
Sub Queries and Nested Queries
Rarely but you may need to run sub queries or nested queries. WP Fluent is powerful
enough to do this for you. You can create different query objects and use the
DB::subQuery()
method.
This will produce a query like this:
SELECT * FROM (SELECT `cb_my_table`.*, (SELECT `details` FROM `cb_person_details` WHERE `person_id` = 3) as table_alias1 FROM `cb_my_table`) as table_alias2
NOTE: WP Fluent doesn't use bindings for sub queries and nested queries.
Get wpdb Instance
If you need to get the wpdb instance you can do so.
Query Events
WP Fluent comes with powerful query events to supercharge your application. These events
are like database triggers, you can perform some actions when an event occurs. For
example you can hook after-delete
event of a table and delete related data from
another table.
Available Events
- before-select
- after-select
- before-insert
- after-insert
- before-update
- after-update
- before-delete
- after-delete
Registering Events
Now every time a select query occurs on users
table, it will add this where criteria,
so banned users don't get access.
The syntax is registerEvent('event type', 'table name', action in a closure)
.
If you want the event to be performed when any table is being queried, provide
':any'
as table name.
Other examples:
After inserting data into my_table
, details will be inserted into another table
Whenever data is inserted into person_details
table, set the timestamp field
created_at
, so we don't have to specify it everywhere:
After deleting from my_table
delete the relations:
WP Fluent passes the current instance of query builder as first parameter of your
closure so you can build queries with this object, you can do anything like usual
query builder (DB
).
If something other than null
is returned from the before-*
query handler, the value
will be result of execution and DB will not be actually queried (and thus, corresponding
after-*
handler will not be called either).
Only on after-*
events you get three parameters: first is the query builder,
third is the execution time as float and the second varies:
- On
after-select
you get theresults
obtained fromselect
. - On
after-insert
you get the insert id (or array of ids in case of batch insert) - On
after-delete
you get the query object (same as what you get fromgetQuery()
), from it you can get SQL and Bindings. - On
after-update
you get the query object likeafter-delete
.
Removing Events
Some Use Cases
Here are some cases where Query Events can be extremely helpful:
- Restrict banned users.
- Get only
deleted = 0
records. - Implement caching of all queries.
- Trigger user notification after every entry.
- Delete relationship data after a delete query.
- Insert relationship data after an insert query.
- Keep records of modification after each update query.
- Add/edit created_at and updated _at data after each entry.
Notes
- Query Events go recursively, for example after inserting into
table_a
your event inserts intotable_b
, now you can have another event registered withtable_b
which inserts intotable_c
. - Of course Query Events don't work with raw queries.
- This is forked from awesome @usmanhalalit vai's Pixie and modified to support WordPress.