paxha/laravel-cte

Laravel queries with common table expressions

v2.0 2019-11-16 16:12 UTC

This package is auto-updated.

Last update: 2024-10-17 03:41:01 UTC


README

Build Status Code Coverage Scrutinizer Code Quality Latest Stable Version Total Downloads License

Introduction

This Laravel extension adds support for common table expressions (CTE) to the query builder and Eloquent.

Supports Laravel 5.5+.

Compatibility

  • MySQL 8.0+
  • MariaDB 10.2+
  • PostgreSQL 9.4+
  • SQLite 3.8.3+
  • SQL Server 2008+

Installation

composer require paxha/laravel-cte:"^1.0"

Usage

SELECT Queries

Use withExpression() and provide a query builder instance, an SQL string or a closure:

$posts = DB::table('p')
    ->select('p.*', 'u.name')
    ->withExpression('p', DB::table('posts'))
    ->withExpression('u', function ($query) {
        $query->from('users');
    })
    ->join('u', 'u.id', '=', 'p.user_id')
    ->get();

Use withRecursiveExpression() for recursive expressions:

$query = DB::table('users')
    ->whereNull('parent_id')
    ->unionAll(
        DB::table('users')
            ->select('users.*')
            ->join('tree', 'tree.id', '=', 'users.parent_id')
    );

$tree = DB::table('tree')
    ->withRecursiveExpression('tree', $query)
    ->get();

You can provide the expression's columns as the third argument:

$query = 'select 1 union all select number + 1 from numbers where number < 10';

$numbers = DB::table('numbers')
    ->withRecursiveExpression('numbers', $query, ['number'])
    ->get();

INSERT/UPDATE/DELETE Queries

You can use common table expressions in INSERT(Laravel 5.7.17+), UPDATE and DELETE queries:

DB::table('profiles')
    ->withExpression('u', DB::table('users')->select('id', 'name'))
    ->insertUsing(['user_id', 'name'], DB::table('u'));
DB::table('profiles')
    ->withExpression('u', DB::table('users'))
    ->join('u', 'u.id', '=', 'profiles.user_id')
    ->update(['profiles.name' => DB::raw('u.name')]);
DB::table('profiles')
    ->withExpression('u', DB::table('users')->where('active', false))
    ->whereIn('user_id', DB::table('u')->select('id'))
    ->delete();

Eloquent

You can use common table expressions in Eloquent queries.

In Laravel 5.5–5.7, this requires the QueriesExpressions trait:

class User extends Model
{
    use \Paxha\LaravelCte\Eloquent\QueriesExpressions;
}

$query = User::whereNull('parent_id')
    ->unionAll(
        User::select('users.*')
            ->join('tree', 'tree.id', '=', 'users.parent_id')
    );

$tree = User::from('tree')
    ->withRecursiveExpression('tree', $query)
    ->get();

Recursive Relationships

If you want to implement recursive relationships, you can use this package: paxha/laravel-adjacency-list

Lumen

If you are using Lumen, you have to instantiate the query builder manually:

$builder = new \Paxha\LaravelCte\Query\Builder(app('db')->connection());

$result = $builder->from(...)->withExpression(...)->get();

In Eloquent, the QueriesExpressions trait is required for all versions of Lumen.

Contributing

Please see CONTRIBUTING and CODE OF CONDUCT for details.