Laravel Postgres Extended

Introduction

An extended PostgreSQL driver for Laravel 9+ with support for some aditional PostgreSQL data types: hstore, uuid, geometric types (point, path, circle, line, polygon...) and support for WITH [RECURSIVE] clause

Installation

Simple run composer require emiliopedrollo/laravel-postgres-extended-schema in your project root directory.

Then you are done.

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, 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.

$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: staudenmeir/laravel-adjacency-list