PHP code example of teamone / teamone-wp-db-orm

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

teamone / teamone-wp-db-orm example snippets

$config = [
    'default'     => 'ali-rds',
    'connections' => [
        // 连接配置
        'ali-rds'     => [
            // 连接名称
            'name'      => 'ali-rds',
            // 只读
            'read'      => [
                'host' => [
                    'localhost',
                    'localhost',
                ],
            ],
            // 读写
            'write'     => [
                'host' => [
                    'localhost',
                ],
            ],
            // 连接驱动
            'driver'    => 'mysql',
            // 端口
            'port'      => 3306,
            // 数据库
            'database'  => 'blog',
            // 账户
            'username'  => 'root',
            // 密码
            'password'  => '123456',
            // 字符集
            'charset'   => 'utf8mb4',
            // 排序字符集
            'collation' => 'utf8mb4_general_ci',
            // 表前缀
            'prefix'    => 'th_',
        ],
        
    ],
];

namespace Teamone\TeamoneWpDbOrmTest\Unit;

use Teamone\TeamoneWpDbOrm\Capsule\Manager as DB;
use Teamone\TeamoneWpDbOrmTest\Config\DatabaseConfig;

trait ConnectTrait
{
    protected static $_db;

    /**
     * @desc 获取连接
     * @return \Teamone\TeamoneWpDbOrm\Connection
     */
    public static function db()
    {
        if (is_null(self::$_db)) {
            self::$_db = DB::resolverDatabaseConfig(DatabaseConfig::class)::connection('ali-rds');
        }

        return self::$_db;
    }

    /**
     * @before
     */
    public function setupSomeFixtures()
    {
        // 开启查询日志
        self::db()->enableQueryLog();
    }

    /**
     * @after
     */
    public function tearDownSomeFixtures()
    {
        // 获取查询日志
        $logs = self::db()->getQueryLog();
        echo "\r\n\r\n";
        print_r($logs);
    }
}

use PHPUnit\Framework\TestCase;

class TeamoneWpDbOrmBaseTest extends TestCase
{
    use ConnectTrait;

    /**
     * @desc 
     */
    public function test1()
    {
        $this->assertTrue(1 + 1 == 2);
    }
}

use Teamone\TeamoneWpDbOrm\Capsule\Manager as DB;

use Teamone\TeamoneWpDbOrm\Capsule\Manager as DB;

// 初始化配置
DB::resolverDatabaseConfig(DatabaseConfig::class);
// 获取连接
$db = DB::connection('ali-rds');
// 执行查询
$users = $db->table('users')->get();

$clazz = new class() implements DatabaseConfigContract{
    /**
     * @desc 数据库连接配置
     * @return array
     */
    public function getConnectionConfig()
    {
        $config = [
            // 默认配置，表示选择 connections 数组，key 为 ali-rds 的配置作为默认数据库配置
            'default'     => 'ali-rds',
            // 连接组
            'connections' => [
                // 连接 ali-rds
                'ali-rds'     => [
                    // 连接名
                    'name'      => 'ali-rds',
                    // 只读
                    'read'      => [
                        'host' => [
                            'localhost',
                            'localhost',
                        ],
                    ],
                    // 读写
                    'write'     => [
                        'host' => [
                            'localhost',
                        ],
                    ],
                    // 驱动名，目前仅支持 mysql
                    'driver'    => 'mysql',
                    // 数据库连接端口
                    'port'      => 3306,
                    // 数据库
                    'database'  => 'blog',
                    // 数据库用户
                    'username'  => 'root',
                    // 数据库密码
                    'password'  => '123456',
                    // 字符集
                    'charset'   => 'utf8',
                    // 字符排序
                    'collation' => 'utf8_unicode_ci',
                    // 表前缀
                    'prefix'    => 'th_',
                ],
                // 连接 tencent-rds
                'tencent-rds' => [
                    'name'      => 'tencent-rds',
                    'read'      => [
                        'host' => [
                            'localhost',
                            'localhost',
                        ],
                    ],
                    // 读写
                    'write'     => [
                        'host' => [
                            'localhost',
                        ],
                    ],
                    'driver'    => 'mysql',
                    'port'      => 3306,
                    'database'  => 'blog',
                    'username'  => 'root',
                    'password'  => '123456',
                    'charset'   => 'utf8',
                    'collation' => 'utf8_unicode_ci',
                    'prefix'    => 'th_',
                ],
            ],
        ];

        return $config;
    }
};

$db = DB::resolverDatabaseConfig($clazz)::connection('ali-rds');

$users = $db->table('users')->get();

$db = DB::resolverDatabaseConfig(DatabaseConfig::class)::connection('ali-rds');

// 开启查询日志
$db->enableQueryLog();

$users = $db->table('users')->get();

// 获取查询日志
$logs = $db->getQueryLog();

$db = DB::resolverDatabaseConfig(DatabaseConfig::class)::connection('ali-rds');

$users = $db->select('SELECT * FROM th_users WHERE `id` IN (?, ?)', [1, 3]);

 // 解析配置，只执行一次
DB::resolverDatabaseConfig(DatabaseConfig::class);

// 查询 1
$users = DB::connection('ali-rds')->table('users')->select('name', 'mobile')->where('id', '>', 0)->get();
print_r($users);

// 查询 2
$users = DB::connection('ali-rds')->table('users')->select('name', 'status')->where('id', '>', 0)->get();
print_r($users);

$db = DB::resolverDatabaseConfig(DatabaseConfig::class)::connection('ali-rds');
$users = $db->table('users')->where('id', '>=', 1)->explain();

$users = self::db()->table('users')->get();

foreach ($users as $user) {
    echo $user->name;
}

$user = self::db()->table('users')->where('name', '八戒')->first();

return $user->name;

$email = self::db()->table('users')->where('name', '八戒')->value('email');

$user = self::db()->table('users')->find(3);

$mobiles = self::db()->table('users')->pluck('mobile');

foreach ($mobiles as $mobile) {
    echo $mobile;
}

$mobiles = self::db()->table('users')->pluck('mobile', 'name');

foreach ($mobiles as $mobile => $name) {
    echo $name;
}

self::db()->table('users')->orderBy('id')->chunk(2, function ($users) {
    foreach ($users as $user) {
        //
    }
});

self::db()->table('users')->orderBy('id')->chunk(2, function ($users) {
    // Process the records...

    return false;
});

$count = self::db()->table('users')->count();

$maxPrice = self::db()->table('users')->max('price');

$minPrice = self::db()->table('users')->min('price');

$avgPrice = self::db()->table('users')->where('is_disable', 0)->avg('price')

$avgPrice = self::db()->table('users')->where('is_disable', 0)->average('price');

$sumPrice = self::db()->table('users')->sum('price');

$users = self::db()->table('users')->select('name', 'email as user_email')->get();

$users = self::db()->table('users')->distinct()->get();

$query = self::db()->table('users')->select('name');

$users = $query->addSelect('email')->get();

$users = self::db()->table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();

$users = self::db()->table('users')->selectRaw('price * ? as price_with_tax', [1.0825])->get();

$users = self::db()->table('users')->whereRaw('price > IF(status = "1", ?, 1)', [2])->get();

 $users = self::db()->table('users')->select('status', DB::raw('SUM(price) as total_sales'))
    ->groupBy('status')->havingRaw('SUM(price) > ?', [2.1])->get();

// $users = self::db()->table('users')->orderByRaw('updated_at - created_at DESC')->get();
$users = self::db()->table('users')->orderByRaw('price, status DESC')->get();

$users = self::db()->table('users')->select('status', 'is_disable')->groupByRaw('status, is_disable')->get();

$users = self::db()->table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'orders.*')
    ->get();

$users = self::db()->table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'orders.*')
    ->get();

$users = self::db()->table('users')
    ->rightJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'orders.*')
    ->get();

$latestPosts = self::db()->table('posts')
    ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
    ->where('is_disable', 1)
    ->groupBy('user_id');

$sql = self::db()->table('users')
    ->joinSub($latestPosts, 'latest_posts', function ($join){
        $join->on('users.id', '=', 'latest_posts.user_id');
    })->toSql();
echo "\r\n\r\n";
print_r($sql);
echo "\r\n\r\n";

$first = self::db()->table('users')->whereNull('name');

$users = self::db()->table('users')->whereNull('password')->union($first)->get();

$first = self::db()->table('users')->whereNull('name');

$users = self::db()->table('users')->whereNull('password')->unionAll($first)->get();

$users = self::db()->table('users')->where('is_disable', '=', 0)->where('status', '>', 0)->get();

$users = self::db()->table('users')->where('is_disable', 0)->get();

$users = self::db()->table('users')->where('status', '>=', 0)->get();

$users = self::db()->table('users')->where('status', '<>', 1)->get();

$users = self::db()->table('users')->where('name', 'like', '李%')->get();

$wheres = [
    'is_disable' => 0,
    'status'     => 1,
    'deleted_at' => null,
];
$users  = self::db()->table('users')->where($wheres)->get();

$users = self::db()->table('users')->where('status', '>', 2)->orWhere('name', '李白')->get();

$users = self::db()->table('users')->where('status', '>', 1)
    ->orWhere(function (Builder $query){
        $query->where('name', '哪吒')->where('status', '=', 1);
    })->get();

$users = self::db()->table('users')->whereBetween('status', [1, 3])->get();

$users = self::db()->table('users')->whereNotBetween('status', [1, 2])->get();

$users = self::db()->table('users')->whereIn('id', [1, 2, 3])->get();

$users = self::db()->table('users')->whereNotIn('id', [1, 2, 3])->get();

$users = self::db()->table('users')->whereNull('deleted_at')->get();

$users = self::db()->table('users')->whereNotNull('deleted_at')->get();

$users = self::db()->table('users')->whereDate('deleted_at', '2016-12-31')->get();

$users = self::db()->table('users')->whereMonth('deleted_at', '12')->get();

$users = self::db()->table('users')->whereDay('deleted_at', '31')->get();

$users = self::db()->table('users')->whereYear('deleted_at', '2016')->get();

$users = self::db()->table('users')->whereTime('deleted_at', '=', '11:20:45')->get();

$users = self::db()->table('users')->whereColumn('mobile', 'name')->get();

$users = self::db()->table('orders')->whereColumn('deleted_at', '>', 'created_at')->get();

$whereColumns = [
    ['first_name', '=', 'last_name'],
    ['updated_at', '>', 'created_at'],
];

$users = self::db()->table('users')->whereColumn($whereColumns)->toSql();

$users = self::db()->table('users')
    ->where('name', '=', '沙僧')
    ->where(function (Builder $query){
        $query->where('is_disable', '>', 1)->orWhere('status', '=', 1);
    })->get();

$users = self::db()->table('users')->whereExists(function ($query){
    $query->select(DB::raw(1))->from('orders')->whereColumn('orders.user_id', 'users.id');
})->get();

$users = self::db()->table('users')->where(function (Builder $query){
        $query->select('amount')->from('orders')
            ->whereColumn('orders.user_id', 'users.id')->orderByDesc('orders.created_at')->limit(1);
    }, 'Pro')->get();

$users = self::db()->table('users')->where('price', '<', function (Builder $query){
        $query->selectRaw('avg(th_i.amount)')->from('orders as i');
    })->get();

$users = self::db()->table('users')->orderBy('name', 'desc')->get();

$users = self::db()->table('users')->orderBy('name', 'desc')->orderBy('email', 'asc')->get();

$users = self::db()->table('users')->latest('deleted_at')->first();

$users = self::db()->table('users')->inRandomOrder()->first();

$query = self::db()->table('users')->orderBy('name');

$unorderedUsers = $query->reorder()->get();

// 删除所有已存在的排序并且附加新的排序，并且在方法上提供新的排序字段和顺序，用于重新排序：
$usersOrderedByEmail = $query->reorder('email', 'desc')->get();

$users = self::db()->table('users')
    ->select('status', DB::raw('count(*) statusTotal'))
    ->groupBy('status')
    ->having('statusTotal', '>', 1)
    ->get();

$users = self::db()->table('users')
    ->select('status', 'is_disable', DB::raw('count(*) statusTotal'))
    ->groupBy('status', 'is_disable')
    ->having('statusTotal', '>', 1)
    ->get();

$users = self::db()->table('users')->skip(10)->take(5)->get();

$users = self::db()->table('users')->offset(10)->limit(5)->get();

$mobile = '138' . mt_rand(10000000, 99999999);

$row = [

    'name'       => '悟空' . mt_rand(1000, 9999),
    'password'   => '123456',
    'mobile'     => $mobile,
    'email'      => $mobile . '@qq.com',
    'price'      => 1.30,
    'is_disable' => 0,
    'status'     => 2,
    'bio'        => '',
];

$result = self::db()->table('users')->insert($row);

$generateRow = function (){
    $mobile = '138' . mt_rand(10000000, 99999999);

    $row = [

        'name'       => '悟空' . mt_rand(1000, 9999),
        'password'   => '123456',
        'mobile'     => $mobile,
        'email'      => $mobile . '@qq.com',
        'price'      => 9.30,
        'is_disable' => 0,
        'status'     => 2,
        'bio'        => '',
    ];

    return $row;
};

$rows   = [];
$rows[] = $generateRow();
$rows[] = $generateRow();

$result = self::db()->table('users')->insert($rows);

$mobile = '138' . mt_rand(10000000, 99999999);
$row    = [
    'id'         => 12,
    'name'       => '悟空sb' . mt_rand(1000, 9999),
    'password'   => '123456',
    'mobile'     => $mobile,
    'email'      => $mobile . '@qq.com',
    'price'      => 1.30,
    'is_disable' => 0,
    'status'     => 2,
    'bio'        => '',
];

$result = self::db()->table('users')->insertOrIgnore($row);

$this->assertIsInt($result);

$mobile = '138' . mt_rand(10000000, 99999999);
$row    = [
    [
        'id'         => 12,
        'name'       => '悟空sb' . mt_rand(1000, 9999),
        'password'   => '123456',
        'mobile'     => $mobile,
        'email'      => $mobile . '@qq.com',
        'price'      => 1.30,
        'is_disable' => 0,
        'status'     => 2,
        'bio'        => '',
    ],
    [
        'id'         => 13,
        'name'       => '悟空sb' . mt_rand(1000, 9999),
        'password'   => '123456',
        'mobile'     => $mobile,
        'email'      => $mobile . '@qq.com',
        'price'      => 1.30,
        'is_disable' => 0,
        'status'     => 2,
        'bio'        => '',
    ],
];

$result = self::db()->table('users')->insertOrIgnore($row);

$mobile = '138' . mt_rand(10000000, 99999999);

$row = [
    'name'       => '悟空sb' . mt_rand(1000, 9999),
    'password'   => '123456',
    'mobile'     => $mobile,
    'email'      => $mobile . '@qq.com',
    'price'      => 1.30,
    'is_disable' => 0,
    'status'     => 2,
    'bio'        => '',
];

$id = self::db()->table('users')->insertGetId($row);

$row = [
    [
        'name'     => '悟空sb' . mt_rand(1000, 9999),
        'password' => '123456',
        'price'    => 1.30,
    ],
];

$result = self::db()->table('users')->upsert($row, ['name', 'password'], ['price']);

$result = self::db()->table('users')->where('id', 1)->update(['status' => 1]);

$result = self::db()->table('users')->updateOrInsert(
    // 条件
    ['email' => '[email protected]', 'name' => '李靖'],
    // 更新
    ['status' => '2']
);

$result = self::db()->table('users')->where('id', 1)->increment('price');

$result = self::db()->table('users')->where('id', 1)->increment('price', 3);

$result = self::db()->table('users')->where('id', 1)->decrement('price');

$result = self::db()->table('users')->where('id', 1)->decrement('price', 3);

$result = self::db()->table('users')->where('id', 14)->delete();

$users = self::db()->table('users')->where('status', '>', 1)->sharedLock()->get();

$db = self::db();

$db->beginTransaction();

$users = $db->table('users')->where('status', '>', 1)->lockForUpdate()->get();

$db->commit();

$users = self::db()->select('SELECT * FROM `th_users` WHERE `status` = ?', [1]);

$result = self::db()->insert('INSERT INTO `th_users`(`name`, `email`) VALUES (?, ?)', ['牛魔王', '[email protected]']);

$result = self::db()->update('UPDATE `th_users` SET `status` = 3 WHERE `name` = ?', ['白龙马']);

$result = self::db()->statement('SET NAMES utf8mb4');

$db = self::db();

// 开始事务
$db->beginTransaction();
try {
    $result1 = self::db()->update('UPDATE `th_users` SET `status` = 3 WHERE `name` = ?', ['白龙马']);

    if ( !$result1) {
        throw new \Exception();
    }

    $result2 = self::db()->update('UPDATE `th_users` SET `status` = 4 WHERE `name` = ?', ['八戒']);

    if ( !$result2) {
        throw new \Exception();
    }

    // 提交事务
    $db->commit();
} catch (\Exception $e) {
    // 回滚事务
    $db->rollBack();
}

$result = self::db()->table('users')->where('is_disable', 1)->exists();

$result = self::db()->table('users')->select(['id', 'name'])->where('name', '朱世杰')->existsOr(function (){
    var_dump('hello');
});

$result = self::db()->table('users')->where('is_disable', 1)->doesntExist();

$result = self::db()->table('users')->select(['id', 'name'])->where('name', '朱世杰')->doesntExistOr(function (){
    var_dump('world');
});

$users = self::db()->table('users')->paginate();

$users = self::db()->table('users')->simplePaginate();

$users = self::db()->table('users')->orderBy('id')->cursorPaginate(2);

$perPage  = 5;
$columns  = ['*'];
$pageName = 'page';
$page     = null;

$users = self::db()->table('users')->paginate($perPage, $columns, $pageName, $page);

$cursor     = new Cursor(['id' => 0], true);
$perPage    = 5;
$columns    = ['*'];
$cursorName = 'cursor';
$users      = self::db()->table('users')->orderBy('id')->cursorPaginate($perPage, $columns, $cursorName, $cursor);

PaginationState::resolveUsing();

// 游标
$cursor = new Cursor(['id' => 0], true);

/** @var CursorPaginator $cursorPaginate */
$cursorPaginate = self::db()->table('users')->select(['id', 'name'])->orderBy('id')->cursorPaginate(3, ['*'], 'cursor', $cursor);

// 下个游标
$nextCursor = $cursorPaginate->nextCursor();

/** @var CursorPaginator $cursorPaginate */
$cursorPaginate = self::db()->table('users')->select(['id', 'name'])->orderBy('id')->cursorPaginate(3, ['*'], 'cursor', $nextCursor);

// 返回的是生成器，当迭代完一个块时，进入下个块迭代才进行实际查询
$users = self::db()->table('users')->select(['id', 'name'])->orderBy('id')->lazyById(3);

$i = 1;
foreach ($users as $item) {
    print_r($i);
    print_r($item);

    $i++;
    if ($i > 3) {
        break;
    }
}

$users = self::db()->table('users')->select(['id', 'name'])->orderBy('id')->lazy(3);

$i = 1;
foreach ($users as $item) {
    print_r($i);
    print_r($item);

    $i++;
    if ($i > 6) {
        break;
    }
}

$users = self::db()->table('users')->select(['id', 'name'])->orderBy('id')->forPageBeforeId(3, 10)->get();

sh
./test
├── Config
│         ├── DatabaseConfig.php #配置文件
│         ├── PluginFirstDatabase.php #配置文件
│         └── PluginSecondDatabase.php #配置文件
├── ConnectTrait.php
├── Model
│         ├── Orders.php #模型类
│         └── Users.php #模型类
└── Unit
    ├── DatabaseTest.php #单元测试类
    ├── ModelTest.php #单元测试类
    ├── MultiTest.php #单元测试类
    └── TeamoneWpDbOrmBaseTest.php #单元测试类

sh
$ ./vendor/phpunit/phpunit/phpunit --configuration phpunit.xml --test-suffix TeamoneWpDbOrmBaseTest.php ./test/Unit

php
DB::resolverDatabaseConfig(DatabaseConfig::class);

php
$users = new Users();

$users = $users->newQuery()->where('id', '=', 1)->get();

print_r($users);

php
$users = new Users();

// Query 1
$query = $users->newQuery();
$list  = $query->select('id', 'name')->where('id', '=', 1)->get();
print_r($list);

// Query 2
$query = $users->newQuery();
$list  = $query->select('id', 'name')->where('id', '=', 2)->get();
print_r($list);

// Config
print_r($query->getConnection()->getConfig());

php
$orders = new Orders();

$query = $orders->newQuery();
$list  = $query->select(['id', 'order_number'])->where('id', 1)->get();
print_r($list);
print_r($query->getConnection()->getConfig());
print_r($query->toSql());

echo "\r\n\r\n";

// 表前缀切换
$orders->setTablePrefix('th_');
$query = $orders->newQuery();
$list  = $query->select(['id', 'order_number'])->where('id', 1)->get();
print_r($list);
print_r($query->getConnection()->getConfig());
print_r($query->toSql());

php
$orders = new Orders();

DB::connection($orders->getConnection())->enableQueryLog();

// 使用了模型配置的表前缀
$query = $orders->newQuery();
$list  = $query->select(['id', 'order_number'])->where('id', 1)->get();
print_r($list);

// 手动切换表前缀
$orders->setTablePrefix('th_');
$query = $orders->newQuery();
$list  = $query->select(['id', 'order_number'])->where('id', 1)->get();
print_r($list);

$logs = DB::connection($orders->getConnection())->getQueryLog();

print_r($logs);

php
// 连接实例
$db = DB::connection('tencent-rds');
// 开启日志
$db->enableQueryLog();
// 执行查询
$orders = $db->setTablePrefix('th_')->table('orders')
->select('id', 'order_number')->where('id', 1)->get();
print_r($orders);
// 查询日志
$logs = $db->getQueryLog();
print_r($logs);

php
use Teamone\TeamoneWpDbOrm\Capsule\PluginManagerAbstract;

class PluginFirstDatabase extends PluginManagerAbstract
{
    /**
     * @desc 数据库连接配置
     * @return array
     */
    public function getConnectionConfig()
    {
        $config = [
            'default'     => 'ali-rds',
            'connections' => [
                'ali-rds' => [
                    'name'      => 'ali-rds',
                    'read'      => [
                        'host' => [
                            '192.168.10.47',
                            '192.168.10.47',
                        ],
                    ],
                    // 读写
                    'write'     => [
                        'host' => [
                            '192.168.10.47',
                        ],
                    ],
                    'driver'    => 'mysql',
                    'port'      => 3306,
                    'database'  => 'blog',
                    'username'  => 'root',
                    'password'  => '123456',
                    'charset'   => 'utf8',
                    'collation' => 'utf8_unicode_ci',
                    'prefix'    => 'th_',
                ],
            ],
        ];

        return $config;
    }

    /**
     * @desc 获取入口文件
     * @return mixed|string
     */
    public function getEntryFile()
    {
        return __FILE__;
    }
}