Create View fail when the query has a boolean column condition

[glamax13]

Hello,
I had an issue today when i tried to create a view.
I think i found the root of the problem.

As mentioned in this previous issue, toSql() is not a perfect representation of what is actually send to postgres as it will replace a boolean condition with an integer (which works fine with MySql but not Postgres).
The unfortunate consequence is that when calling Query::toSql($query), as we try to create the view BuilderView->createView(), the definition will have the integer instead of the boolean.

This behaviour applied to this exemple

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Migrations\Migration;

return new class extends Migration
{
	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::createView(
			'disabled',
			DB::table('disable_products')->where('soft_disabled', true)->select('disable_id')
		);
	}
}

Produces the following error:

   2025_08_08_100000_add_disabled_view .................................................................................................. 4.24ms FAIL

   Illuminate\Database\QueryException 

  SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: boolean = integer at character 93
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. (Connection: pgsql, SQL: create view "disabled" as select "disable_id" from "disable_products" where "soft_disabled" = 1)

Calling whereBoolean() did not fix the issue as it come from the value substitution of toSql() and not the query in itself.

Thank you for your time and your work.

[glamax13]

On a related note, the condition rewritten as followed works: where('soft_disabled', 't')

[tpetry]

Yeah, as you've read, Laravel's handling of booleans is really MySQL-focused and I can't change it. If I would cast the value to boolean, some applications relying on the integer value will break. But the whereBoolean function has been created exactly for this purpose - and works.

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('disable_products', function (Blueprint $table) {
            $table->id('disable_id');
            $table->boolean('soft_disabled');
        });

        Schema::createView(
            name: 'disabled',
            query: DB::table('disable_products')->whereBoolean('soft_disabled', true)->select('disable_id')
        );
    }
};

This works perfectly - as intended. Can you provide a minimal migration with the bug you're experiencing?

[glamax13]

My apologies, whereBoolean() does solve the issue.
I guess i forgot to change all mentions of where(true) in my original migration.

It does indeed work perfectly :)

[glamax13]

As a precision: i retried with the full view and the issue with using whereBoolean() is that it is not recognised in a ->when() chained function (converted to where('boolean', true)).

But this isn't the scope of this issue so i'll keep using where('soft_disabled', 't') for now.

My weird migration:

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Query\Builder;

return new class extends Migration
{
	public function up()
	{
		foreach (['disabled' => null, 'disabled_soft' => true, 'disabled_hard' => false] as $name => $mode) {
			Schema::createView(
				$name,
				DB::table('disable_products')->select('table.id')
					->join(
						'table',
						fn($q) => $q
							->on('disable_id', 'table.id')
							->where('disable_type', 'class')
							->when(!is_null($mode), fn($q) => $q->whereBoolean('soft_disabled', $mode))
					)
			);
		}
	}
}

[tpetry]

Can you share a very simple migration with the when() problem I could run? This shouldn't be a problem in theory. But don't know which weird magic the Laravel framework is doing there internally. Sounds like the where{Columnname}($value) magic of Laravel is triggered.

[glamax13]

Here is an example of my troubles

<?php

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

return new class extends Migration
{
    // public $withinTransaction = false;
	public function up()
	{
        Schema::create('my_disabled', function (Blueprint $table) {
            $table->identity()->primary();
            $table->morphs('disable');
			$table->boolean('soft_disabled');
            $table->timestamps();
        });

		Schema::create('my_products', function(Blueprint $table){
            $table->identity()->primary();
		});

		DB::table('my_disabled')->insert([
			'disable_id' => 1,
			'disable_type' => 'product',
			'soft_disabled' => true,
		]);

		DB::table('my_products')->insert(['id' => 1]);

		foreach (['disabled' => null, 'disabled_soft' => true, 'disabled_hard' => false] as $name => $mode) {
			Schema::createView(
				$name,
				DB::table('my_disabled')->select('my_products.id')
					->join(
						'my_products',
						fn($q) => $q
							->on('disable_id', 'my_products.id')
							->where('disable_type', 'product')
							->when(!is_null($mode), fn($q) => $q->whereBoolean('soft_disabled', $mode))
					)
			);
		}
		dd();
	}
};

The error:

SQLSTATE[42703]: Undefined column: 7 ERROR: column "boolean" does not exist at character 174 (Connection: pgsql, SQL: create view "disabled_soft" as select "my_products"."id" from "my_disabled" inner join "my_products" on "disable_id" = "my_products"."id" and "disable_type" = 'product' and "boolean" = 'soft_disabled')

It would appear to be cause by the JoinClause() and not the when() helper.

[tpetry]

I'll look into later.