This PR add the ability to call *Returning methods on Eloquent Builder and retrieve the results as models.

The tests are pretty minimal but should be a good starting point.

The implementation is based on Builder::get (https://github.com/laravel/framework/blob/9.x/src/Illuminate/Database/Eloquent/Builder.php#L669-L700)

I don't know if adding eager loading ability should be done but it could be added here or on another PR if needed.

This PR adds support to create functions. We can of course change the signature but this is just a first proposal because I think it would be a great addition.

Excellent package, I've been using it for a long time.

Recently I needed to use with and with recursive and I wanted to use it in the model and not DB.

Would it be possible to add?

I even made it locally for myself, but the code I made I don't think it's feasible to put on the air, but I've extended the service provider and made changes so that Grammer has this method:

  /**
     * Compile the select query into SQL.
     *
     * @param \Illuminate\Database\Query\Builder $query
     * @return string
     */
    public function compileSelect(Builder $query)
    {
        $sql = parent::compileSelect($query);

        $withQuery = $query->withQuery;

        if ($withQuery) {
            $withQuery .= ' ';
        }

        return "{$withQuery}{$sql}";
    }

Query\Builder I added:

   public $withQuery = '';
    public function withQuery($query = null)
    {
        $this->withQuery = $query;

        return $this;
    }

It works for me, but it could be better by adding the bindings and making the code better.

In laravel9.6.0 app using tpetry/laravel-postgresql-enhanced 0.20.0 I create function with table data returned :

return new class extends Migration
{
    public function up()
    {
        Schema::createFunctionOrReplace('ts_categories_get', ['sort_field' => 'character varying'],
            'TABLE(id smallint, name character varying, active boolean, slug character varying, description text,
             created_at timestamp, updated_at timestamp)', 'plpgsql', '

  BEGIN
    SELECT ts_categories.id, ts_categories.name, ts_categories.active, ts_categories.slug, ts_categories.description,
    ts_categories.created_at, ts_categories.updated_at
        from ts_categories;
  END;

');
    }

But running the function : SELECT ts_categories_get('name') I got error : ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function ts_categories_get(character varying) line 4 at SQL statement In statement: SELECT ts_categories_get('name') I see in phppgadmin this function defined as : https://prnt.sc/TuNTxr6-TcxM

What is wrong ?

Thanks!

Eloquents lazy() has the problem of making new queries for each chunk via OFFSET+LIMIT, which doesn't give a stable result if something changed in parallel. Meanwhile lazyById prevents applying own ordering, plus aren't completely safe from changes in parallel requests either. And chunk() ends up with all the results queried out into memory at PDO level, which is unsuitable for dealing with a lot of rows.

So I had to come up with a version that overcomes all that by making use of PostgreSQL cursors to handle the chunking. Here's a quick (and perhaps a bit dirty in regards to toSql() usage) macro that does this:

    public function boot()
    {
        Builder::macro('lazyFetch', function(int $chunkSize = 1000) {
            if ($chunkSize < 1) {
                throw new InvalidArgumentException('The chunk size should be at least 1');
            }
            if (DB::transactionLevel() <= 0) {
                throw new RuntimeException('lazyFetch can only be used within a DB transaction');
            }

            return LazyCollection::make(function () use ($chunkSize) {
                $cursorName = 'lazy_fetch_' . spl_object_id($this);
                DB::statement("DECLARE {$cursorName} NO SCROLL CURSOR FOR " . $this->toSql(), $this->getBindings());

                while (true) {
                    $results = DB::select("FETCH FORWARD {$chunkSize} FROM {$cursorName}");

                    foreach ($results as $result) {
                        yield $result;
                    }

                    if (count($results) < $chunkSize) {
                        DB::statement("CLOSE {$cursorName}");
                        return;
                    }
                }
            });
        });
    }

and then it can be used by just using lazyFetch() instead of lazy() to get a stable LazyCollection. It might even be a good idea to use that for lazy() automatically if within a transaction?

Anyhow, I think it might be a good fit into laravel-postgresql-enhanced in some form. If so, how would it be integrated into a package like laravel-postgresql-enhanced is for easy usage by programs? Might take a stab at making a PR for this if I knew which way to integrate it into the package.

I have a model Location with relationships to organization and region models. Each of those is part of a hierarchical structure, stored in the database as an adjacency list.

For example:

• Location 1 has region_id = 2 and organization_id = 3
• Region 2 has parent_id = 20
• Organization 3 has parent_id = 33

When users load a list of locations, there are two sets of where clauses that need to be applied:

1. Filter based on the user's permissions (at the organization, region, or location level; via an assignments table with a morphTo relationship)
2. Filter based on the user's request (also at the organization, region, or location level)

My Location model has this scope for the first filter:

public function scopeUserCanAccess($rootQuery, User $user)
{
    $rootQuery->where(function ($query) use ($user, $rootQuery) {
        $addedOrgCTE = false;
        $addedRegionCTE = false;

        foreach ($user->assignments as $assignment) {
            switch ($assignment->assignable_type) {
                case 'organization':
                    if (!$addedOrgCTE) {
                        $orgQuery = Organization::query()
                            ->select('id', 'parent_id')
                            ->where('id', $assignment->assignable_id)
                            ->unionAll(
                                Organization::query()
                                    ->select('organizations.id', 'organizations.parent_id')
                                    ->join('org_tree', 'org_tree.id', 'organizations.parent_id')
                            );
                        $rootQuery->withExpression('org_tree', $orgQuery, ['recursive' => true]);
                        $addedOrgCTE = true;
                    }
                    $query->orWhereIn('organization_id', function ($query) {
                        $query->select('id')->from('org_tree');
                    });
                    break;
                case 'region':
                    if (!$addedRegionCTE) {
                        $regionQuery = Region::query()
                            ->select('id', 'parent_id')
                            ->where('id', $assignment->assignable_id)
                            ->unionAll(
                                Region::query()
                                    ->select('regions.id', 'regions.parent_id')
                                    ->join('region_tree', 'region_tree.id', 'regions.parent_id')
                            );
                        $rootQuery->withExpression('region_tree', $regionQuery, ['recursive' => true]);
                        $addedRegionCTE = true;
                    }
                    $query->orWhereIn('region_id', function ($query) {
                        $query->select('id')->from('region_tree');
                    });
                    break;
                case 'location':
                    $query->orWhere('id', $assignment->assignable_id);
                    break;
            }
        }
    });
}

For the second filter, I have this function that modifies the query based on the request:

public function filterQuery($request, $rootQuery)
{
    $rootQuery->where(function ($query) use ($rootQuery) {
        $addedOrgCTE = true;
        $addedRegionCTE = false;

        if ($request->has('organization')) {
            $rawArg = $request->organization;
            $arg = str_contains($rawArg, ',') ? explode(',', $rawArg) : [$rawArg];
            if (!$addedOrgCTE) {
                $orgQuery = Organization::query()
                    ->select('id', 'parent_id')
                    ->whereIn('id', $arg)
                    ->unionAll(
                        Organization::query()
                            ->select('organizations.id', 'organizations.parent_id')
                            ->join('org_tree', 'org_tree.id', 'organizations.parent_id')
                    );
                $rootQuery->withExpression('org_tree', $orgQuery, ['recursive' => true]);
                $addedOrgCTE = true;
            }
            $query->orWhereIn('organization_id', function ($query) {
                $query->select('id')->from('org_tree');
            });
        }

        if ($request->has('region')) {
            $rawArg = $request->region;
            $arg = str_contains($rawArg, ',') ? explode(',', $rawArg) : [$rawArg];
            if (!$addedRegionCTE) {
                $regionQuery = Region::query()
                    ->select('id', 'parent_id')
                    ->whereIn('id', $arg)
                    ->unionAll(
                        Region::query()
                            ->select('regions.id', 'regions.parent_id')
                            ->join('region_tree', 'region_tree.id', 'regions.parent_id')
                    );
                $rootQuery->withExpression('region_tree', $regionQuery, ['recursive' => true]);
                $addedRegionCTE = true;
            }
            $query->orWhereIn('region_id', function ($query) {
                $query->select('id')->from('region_tree');
            });
        }

        if ($request->has('location')) {
            $rawArg = $request->location;
            $arg = str_contains($rawArg, ',') ? explode(',', $rawArg) : [$rawArg];
            $query->orWhereIn('id', $arg);
        }
    });
}

The problem I have is that this generates an invalid query because it adds the same CTE (org_tree or region_tree) twice.

Is there any way I can check if a CTE is already added before I add it again, or we can change the withExpression method to not add an expression with the same name as one that is already present?

Hello, First off I want to say thank you for all the hard work you've put into this amazing package.

I ran into an issue where Larastan is giving me errors when I access model properties that were defined in migrations using the package's Blueprint class. For example, given the following user migration:

<?php

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->string('name');
            $table->caseInsensitiveText('email')->unique()->index();
        });
    }
}

Calling properties on the model with $user->name or $user->email will give the following error:

------ ---------------------------------------------------------- 
  Line   Data/AuthData.php                                         
 ------ ---------------------------------------------------------- 
  49     Access to an undefined property App\Models\User::$name.   
  51     Access to an undefined property App\Models\User::$email.  
 ------ ---------------------------------------------------------- 

It's easy enough to work around by annotating the model.

/**
 * @property string $name
 * @property string $email
 */
class User extends Authenticatable

But since I'm using the package's phpstan extension I figured I'd ask if this is expected functionality or if I'm missing something obvious.

Thanks again!

Environment Details

composer.json

"php": "^8.1",
"laravel/framework": "^9.43",
"nunomaduro/larastan": "^2.2.9",
"tpetry/laravel-postgresql-enhanced": "^0.23.0"

phpstan.neon

includes:
    - ./vendor/nunomaduro/larastan/extension.neon
    - ./vendor/tpetry/laravel-postgresql-enhanced/phpstan-extension.neon

parameters:
    level: 8

It seems that the LIKE query is not working with citext columns. I assume that it's related to this piece of code: https://github.com/laravel/framework/blob/aad4521c6a5c91a8fc0b9acbc2362c281013daea/src/Illuminate/Database/Query/Grammars/PostgresGrammar.php#L42-L47

The column will be casted into text and thus any case insensitive search isn't working anymore. Is there any smart way to solve it?

Hi, I'm getting a TypeError trying to use this on Laravel 9. What am I doing wrong?

TypeError 

Illuminate\Database\Migrations\Migration@anonymous(): Argument #1 ($table) must be of type Tpetry\PostgresqlEnhanced\Schema\Blueprint, Illuminate\Database\Schema\Blueprint given, called in
/var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php on line 256 

Migration:

<?php

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    use ZeroDowntimeMigration;

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('somethings', function (Blueprint $table) {
        // ...

Occasionally I see this error when I type my arguments:

Argument #1 ($builder) must be of type Illuminate\Contracts\Database\Eloquent\Builder, Tpetry\PostgresqlEnhanced\Query\Builder given

I think this change should not have any side effects.

This PR just provides the general idea of supporting an ON TRUE condition. If you want to return a NULL-extended row in the case where the lateral join returns no rows, then you would use LEFT JOIN LATERAL (...) ON TRUE. You can already achieve it with the current implementation by providing the values manually but extending this library would even make it easier. What do you think?

Hello! I use Postgres heavily and am very excited to see that you started this project.

Right now I have umbrellio/laravel-pg-extensions implemented and have used its "Custom Extensions" method for adding other new functionality I needed. On top of that library I have umbrellio/laravel-ltree, belamov/postgres-range, staudenmeir/laravel-adjacency-list, and with it staudenmeir/laravel-cte.

Most of the custom functionality I've added is already handled by your library, so I would just be able to remove that on my end. LTree I can just use the Eloquent helpers and your library can do the schema stuff. You also support ranges so that's easy. I do use Laravel Adjacency List heavily so I'm wondering how it would work with both libraries implementing CTE (maybe no issue if there's no naming conflict?), and then compatibility with other Postgres libraries in general.

I think the main issue I run into when I'm trying to get these libraries to work together is when there's conflicts with the PostgresConnection. And then some way for multiple libraries to extend Blueprint and Schema Grammar which is what the Custom Extension method allows for.

So far I've used the umbrellio/laravel-pg-extensions implementation of PostgresConnection and extended that. Then when there's conflicts, which I ran into with belamov/postgres-range and staudenmeir/laravel-cte, I just implement them manually using the Custom Extension method so that they do not load their versions of the PostgresConnection, and prevent them from being automatically discovered by Laravel using composer.json:

"extra": {
    "laravel": {
        "dont-discover": [
          "belamov/postgres-range",
          "staudenmeir/laravel-cte"
        ]
    }
}

Switching to your library I won't have that Custom Extension method anymore, which means I don't have a clear way to deal with conflicts, so I know that will be the main challenge in transitioning here.

Of course this is out of scope of this repository but I thought I'd ask just in case you have some insight here. Either way, I do plan to try this transition in just the next few days, so if you're not sure I'll go ahead and just update this issue with anything that I find, maybe help others in the future.

I am so excited about this library, thank you so much for starting this!

When running composer update which regenerates ide-helper stubs, I go this as it was analyzing the models.

Exception: Unknown database type citext requested, Doctrine\DBAL\Platforms\PostgreSQL100Platform may not support it.
Could not analyze class App\Models\Tag.

The fix was easy enough, in AppServiceProvider I put:

<?php
// ...
use Tpetry\PostgresqlEnhanced\Types\CitextType;
class AppServiceProvider extends ServiceProvider
{
...
    public function boot(){
        \app('db')->registerDoctrineType(CitextType::class, 'citext', 'string');
        // ...
    }

As I was looking at the code in the vendor folder, I noticed the $typeInstance->getName() is deprecated in doctrine, not sure if that is related,and I'm not sure that's the best way to register the doctrine type, but it made the error go away..

The IS DISTINCT FROM and IS NOT DISTINCT FROM terms simplify a query a lot for nullable columns:

col IS DISTINCT FROM 'value' simplifies a query with nullable columns as it is a replacement for (col IS NULL OR col != 'value')

I think it would be a great addition if we could order by nulls first/last, i.e. see https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY. Maybe as a new Builder trait? What do you think?

If possible, we should also try to support CursorPaginator since it does not work with raw values at the moment.