README

About WpMVC Database

WpMVC Database is a robust and versatile SQL query builder designed specifically for WordPress plugins. It provides a similar experience to Laravel's Eloquent Query Builder, a well-known and widely-used PHP framework.

About WpMVC Database
Installation
Create Eloquent Model
Insert Data
Update Data
Delete Data
Read Data
Relationships
License

Installation

To install the WpMVC Database package, simply run the following command via Composer:

composer require wpmvc/database

Create Eloquent Model

To create an Eloquent model, you can use the following code snippet.

<?php

namespace WpMVC\App\Models;

use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Resolver;

class Post extends Model {

	public static function get_table_name():string {
		return 'posts';
	}

	public function resolver():Resolver {
		return new Resolver;
	}
}

Insert Data

You can insert data into the posts table using the query builder provided by Eloquent. Here's an example of how to insert a single item:

Post::query()->insert([
	'post_author' => wp_get_current_user()->ID,
	'post_title' => "Test Post"
	...
]);

To insert multiple items at once, simply pass an array of arrays:

$post_author = wp_get_current_user()->ID;

Post::query()->insert([
	[
		'post_author' => $post_author,
		'post_title' => "Test Post 1"
		...
	],
	[
		'post_author' => $post_author,
		'post_title' => "Test Post 2"
		...
	]
]);

You can also insert an item and retrieve its ID in one step using the insert_get_id method:

$post_id = Post::query()->insert_get_id([
	'post_author' => wp_get_current_user()->ID,
	'post_title' => "Test Post"
	// ...
]);

Update Data

To update a post where the post id is 100, use the following code:

Post::query()->where('post_id', 100)->update([
	'post_title' => "Test Post"
]);

Delete Data

To delete a post where the post id is 100, use the following code:

Post::query()->where('post_id', 100)->delete();

Read Data

To retrieve data, the WpMVC Database offers a variety of methods: Get all posts

Aggregates

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

$posts = Post::query()->count();

Retrieving Models

All Records

To get all the posts, use the get method as shown below:

$posts = Post::query()->get();

Single Record

To retrieve a single record from the database, use the first method as shown below:

$posts = Post::query()->where('id', 100)->first();

Select Statements

You may not always want to select all columns from a database table. Using the select method, you can specify a custom "select" clause for the query:

$posts = Post::query()->select('post_title', 'post_date')->get();

The distinct method allows you to force the query to return distinct results:

$posts = Post::query()->distinct()->select('post_title', 'post_date')->get();

Join

Inner Join Clause

To add join clauses to your SQL queries using the query builder, you can use the join method. This method is used to perform an inner join between two or more database tables. The first argument passed to the join method is the name of the table you want to join, and the remaining arguments specify the column constraints for the join.

$users = User::query()
                ->join('contacts', 'users.id', '=', 'contacts.user_id')
                ->select('users.*', 'contacts.phone', 'contacts.email')
                ->get();

In this example, we are joining the users table with the contacts table on the id column of users and the user_id column of contacts. We are also selecting all columns from users and the phone and email columns from contacts.

You can even join multiple tables in a single query using the join method multiple times. For example:

$users = User::query()
                ->join('contacts', 'users.id', '=', 'contacts.user_id')
                ->join('orders', 'users.id', '=', 'orders.user_id')
                ->select('users.*', 'contacts.phone', 'orders.price')
                ->get();

In this example, we are joining the users table with the contacts table and the orders table. We are selecting all columns from users, the phone column from contacts, and the price column from orders.

Here is an example of how to use the join method to join two tables:

Left Join / Right Join Clause

To perform a "left join" or "right join" instead of an "inner join", you can use the left_join or right_join methods respectively. These methods have the same signature as the join method, which means that you need to pass the name of the table you want to join as the first argument, and then specify the column constraints for the join as the remaining arguments.

For example, to perform a "left join" on the users and posts tables using the user_id column as the join constraint, you can do the following:

$users = User::query()
            ->left_join('posts', 'users.id', '=', 'posts.user_id')
            ->get();

This will return all the rows from the users table along with their matching rows from the posts table based on the user_id column. If a user has no matching posts, the values from the posts table will be NULL.

Similarly, to perform a "right join" on the users and posts tables using the user_id column as the join constraint, you can do the following:

$users = User::query()
            ->right_join('posts', 'users.id', '=', 'posts.user_id')
            ->get();

This will return all the rows from the posts table along with their matching rows from the users table based on the user_id column. If a post has no matching user, the values from the users table will be NULL.

Advanced Join Clauses

You may also specify more advanced join clauses. To get started, pass a closure as the second argument to the join method. The closure will receive a WpMVC\Database\Query\JoinClause instance which allows you to specify constraints on the "join" clause:

use WpMVC\Database\Query\JoinClause;

$posts = Post::query()->join('postmeta', function (JoinClause $join) {
	$join->on('postmeta.post_id', '=', 'posts.ID')->orOn(/* ... */);
})->get();

If you would like to use a "where" clause on your joins, you may use the where and or_where methods provided by the JoinClause instance. Instead of comparing two columns, these methods will compare the column against a value:

$posts = Post::query()->join('postmeta', function (JoinClause $join) {
	$join->on('postmeta.post_id', '=', 'posts.ID')->where('postmeta.meta_value', '>', 500);
})->get();

Basic Where Clauses

Where Clauses

To get only published posts, use the where method as shown below:

$posts = Post::query()->where('post_status', 'publish')->get();

Or Where Clauses

To get only published posts or if post_title is test post, use the where method as shown below:

$posts = Post::query()->where('post_status', 'publish')->orWhere('post_title', 'test post')->get();

Advanced Where Clauses

Where Exists Clauses

The where_exists and where_column methods are useful when you need to retrieve data from two different tables that have a common column.

To get all posts if the post has meta data, you can use either of the following two processes:

Process One: In this process, we use a closure function to define a subquery that selects 1 from the postmeta table where the post_id column in postmeta table is equal to the ID column in the posts table. The closure function is passed as an argument to the where_exists method to filter the posts.
```
$posts = Post::query()->where_exists(function(Builder $query) {
	$query->select(1)->from('postmeta')->where_column('postmeta.post_id', 'posts.id')->limit(1);
})->get();
```
Alternatively Process: In this process, we first define a variable $post_meta that selects 1 from the postmeta table where the post_id column in postmeta table is equal to the ID column in the posts table. Then we use the where_exists method and pass the $post_meta variable as an argument to filter the posts.
```
$post_meta = PostMeta::query()->select(1)->where_column('postmeta.post_id', 'posts.id')->limit(1);
$posts     = Post::query()->where_exists($post_meta)->get();
```

In both of these processes, we use the where_column method to specify the column names in the two tables that should be compared. This allows us to filter the posts based on whether or not they have meta data.

Additional Where Clauses

where_between / or_where_between

The where_between method verifies that a column's value is between two values:

$posts = Post::query()->where_between('ID', [1, 100])->get();

where_not_between / or_where_not_between

The where_not_between method verifies that a column's value lies outside of two values:

$posts = Post::query()->where_not_between('ID', [1, 100])->get();

where_in / where_not_in / or_where_in / or_where_not_in

The where_in method verifies that a given column's value is contained within the given array:

$posts = Post::query()->where_in('ID', [100, 105])->get();

The where_not_in method verifies that the given column's value is not contained in the given array:

$posts = Post::query()->where_not_in('ID', [100, 105])->get();

Ordering, Grouping, Limit & Offset

Ordering

The order_by Method

The order_by method allows you to sort the results of the query by a given column. The first argument accepted by the order_by method should be the column you wish to sort by, while the second argument determines the direction of the sort and may be either asc or desc:

$posts = Post::query()->order_by('post_title')->get();

To sort by multiple columns, you may simply invoke order_by as many times as necessary:

$posts = Post::query()->order_by('post_title')->order_by_desc('post_status')->get();

Grouping

The group_by & having Methods

As you might expect, the group_by and having methods may be used to group the query results. The having method's signature is similar to that of the where method:

$posts = Post::query()->group_by('post_author')->having('post_author', '>', 100)->get();

Limit & Offset

The limit & offset Methods

You may use the limit and offset methods to limit the number of results returned from the query or to skip a given number of results in the query:

$posts = Post::query()->offset(10)->limit(5)->get();

Relationships

Database tables are often related to one another. For example, a blog post may have many comments or an order could be related to the user who placed it. Eloquent makes managing and working with these relationships easy, and supports a variety of common relationships:

One To One

A one-to-one relationship is a very basic type of database relationship. For example, a User model might be associated with one Phone model. To define this relationship, we will place a Phone method on the User model. The Phone method should call the has_one method and return its result. The has_one method is available to your model via the model's WpMVC\Database\Eloquent\Model base class:

<?php

namespace WpMVC\App\Models;

use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Eloquent\Relations\HasOne;

class User extends Model {

	/**
     * Get the phone associated with the user.
     */
    public function phone(): HasOne
    {
        return $this->has_one(Phone::class, 'ID', 'user_id');
    }
}

Eloquent assumes that the foreign key should have a value matching the primary key column of the parent. In other words, Eloquent will look for the value of the user's ID column in the user_id column of the Phone record.

Now, let's retrieve all Users and their phone:

$users = User::query()->with('phone')->get();

One To Many

A one-to-many relationship is used to define relationships where a single model is the parent to one or more child models. For example, a blog post may have an infinite number of meta. Like all other Eloquent relationships, one-to-many relationships are defined by defining a method on your Eloquent model:

<?php

namespace WpMVC\App\Models;

use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Eloquent\Relations\HasMany;

class Post extends Model {

	/**
     * Get the all meta associated with the user.
     */
    public function meta(): HasMany
    {
        return $this->has_many(PostMeta::class, 'ID', 'post_id');
    }
}

One To Many (Inverse) / Belongs To

Now that we can access all of a post's meta, let's define a relationship to allow a meta to access its parent post. To define the inverse of a has_many relationship, define a relationship method on the child model which calls the belongs_to_one method:

<?php

namespace WpMVC\App\Models;

use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Eloquent\Relations\BelongsToOne;

class PostMeta extends Model {

	/**
     * Get the post that owns the meta.
     */
    public function post(): BelongsToOne
    {
        return $this->belongs_to_one(Post::class, 'post_id', 'ID');
    }
}

Constraining Query Loads

Sometimes you may wish to a relationship but also specify additional query conditions for the relationship query. You can accomplish this by passing an array of relationships to the with method where the array key is a relationship name and the array value is a closure that adds additional constraints to the relationship query:

use WpMVC\Database\Query\Builder;

$posts = Post::query()->with('meta', function(Builder $query) {
	$query->where('meta_id', 672);
})->get();

In with method you can pass array for multiple relationship

$posts = Post::query()->with([
			'meta' => function (Builder $query) {
				$query->where('meta_id', 672);
			},
			'user'
		])->get();

License

WpMVC Database is open-sourced software licensed under the MIT license.

wpmvc / database

Maintainers

Details