lunarphp
Customers Create page return error when using Postgres.
- Lunar version: 1.0.0-alpha
- Laravel Version: 11.9
- PHP Version: 8.3.6
- Database Driver & Version: pgsql (Postgres) & 16.3
Expected Behaviour:
On click to new Customer, it should return a form to create a customer, but it returns error.
Actual Behaviour:
It return this error : SQLSTATE[42883]: Undefined function: 7 ERROR: could not identify an equality operator for type json LINE 1: select distinct "lunar_customer_groups".* from "lunar_custom... ^ (Connection: pgsql, SQL: select distinct "lunar_customer_groups".* from "lunar_customer_groups" left join "lunar_customer_customer_group" on "lunar_customer_groups"."id" = "lunar_customer_customer_group"."customer_group_id" order by "lunar_customer_groups"."name" asc)
it works fine when using MySQL as database, but on Postgres it returns this error.
Steps To Reproduce:
Just change the database to Postgres and try to create a customer.
Getting the same error when trying to attach product to collection. Maybe it's important to note that I use two languages.
SQL:
select distinct "lunar_collections".*
from "lunar_collections"
left join "lunar_collection_product" on "lunar_collections"."id" = "lunar_collection_product"."collection_id"
where "lunar_collections"."id" = 1
limit 1;
Error: [42883] ERROR: could not identify an equality operator for type json.
Postgres version: 16.3 Lunar version: 1.0.0-alpha.27 Laravel version: 11.11.1 PHP version: 8.3.7.
I think this is a known issue with the search side of things, but I'd have to check.
I think this is a known issue with the search side of things, but I'd have to check.
I think so too. I even wasn't able to search for collections with SCOUT_DRIVER=database. But when I've switched to driver collection search results appeared. But not saving.
Found solution here: modify field type from json to jsonb.
I've modified lunar_collections.attribute_data to jsonb and problem has gone.
@glennjacobs Do you think this something we need to consider in the migrations or should we have a notice somewhere in the docs?
You can just use jsonb everywhere. Laravel will change this to json when using mysql database. And the problems with json in postgres in lunar occur in more than one place. I created a migration that changes (I think) everywhere json to jsonb and so far I have no problems. The problem also appeared, for example, when entering the customer view.
Code
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
/**
* Run the migrations.
*/
public function up(): void
{
Schema::table($this->prefix . 'attribute_groups', function (Blueprint $table) {
$table->jsonb('name')->change();
});
Schema::table($this->prefix . 'attributes', function (Blueprint $table) {
$table->jsonb('name')->change();
$table->jsonb('configuration')->change();
$table->jsonb('description')->change();
});
Schema::table($this->prefix . 'products', function (Blueprint $table) {
$table->jsonb('attribute_data')->change();
});
Schema::table($this->prefix . 'customers', function (Blueprint $table) {
$table->jsonb('meta')->nullable()->change();
$table->jsonb('attribute_data')->nullable()->change();
});
Schema::table($this->prefix . 'addresses', function (Blueprint $table) {
$table->jsonb('meta')->nullable()->change();
});
// if (!Schema::hasTable('media')) {
// Schema::table('media', function (Blueprint $table) {
// $table->jsonb('manipulations')->change();
// $table->jsonb('custom_properties')->change();
// $table->jsonb('generated_conversions')->change();
// $table->jsonb('responsive_images')->change();
// });
// }
Schema::table($this->prefix . 'collections', function (Blueprint $table) {
$table->jsonb('attribute_data')->change();
});
// $activityLogTable = config('activitylog.table_name');
// if (!Schema::hasTable($activityLogTable)) {
// Schema::table($activityLogTable, function (Blueprint $table) {
// $table->jsonb('properties')->nullable()->change();
// });
// }
Schema::table($this->prefix . 'product_options', function (Blueprint $table) {
$table->jsonb('name')->change();
$table->jsonb('label')->nullable()->change();
});
Schema::table($this->prefix . 'product_option_values', function (Blueprint $table) {
$table->jsonb('name')->change();
});
Schema::table($this->prefix . 'orders', function (Blueprint $table) {
$table->jsonb('tax_breakdown')->change();
$table->jsonb('meta')->nullable()->change();
$table->jsonb('discount_breakdown')->nullable()->change();
$table->jsonb('shipping_breakdown')->nullable()->change();
});
Schema::table($this->prefix . 'order_lines', function (Blueprint $table) {
$table->jsonb('tax_breakdown')->change();
$table->jsonb('meta')->nullable()->change();
});
Schema::table($this->prefix . 'order_addresses', function (Blueprint $table) {
$table->jsonb('meta')->nullable()->change();
});
Schema::table($this->prefix . 'transactions', function (Blueprint $table) {
$table->jsonb('meta')->nullable()->change();
});
Schema::table($this->prefix . 'carts', function (Blueprint $table) {
$table->jsonb('meta')->nullable()->change();
});
Schema::table($this->prefix . 'cart_addresses', function (Blueprint $table) {
$table->jsonb('meta')->nullable()->change();
});
Schema::table($this->prefix . 'cart_lines', function (Blueprint $table) {
$table->jsonb('meta')->nullable()->change();
});
Schema::table($this->prefix . 'product_variants', function (Blueprint $table) {
$table->jsonb('attribute_data')->nullable()->change();
});
Schema::table($this->prefix . 'discounts', function (Blueprint $table) {
$table->jsonb('data')->nullable()->change();
});
Schema::table($this->prefix . 'brands', function (Blueprint $table) {
$table->jsonb('attribute_data')->nullable()->change();
});
Schema::table($this->prefix . 'customer_groups', function (Blueprint $table) {
$table->jsonb('attribute_data')->nullable()->change();
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::table($this->prefix . 'attribute_groups', function (Blueprint $table) {
$table->json('name')->change();
});
Schema::table($this->prefix . 'attributes', function (Blueprint $table) {
$table->json('name')->change();
$table->json('configuration')->change();
$table->json('description')->change();
});
Schema::table($this->prefix . 'products', function (Blueprint $table) {
$table->json('attribute_data')->change();
});
Schema::table($this->prefix . 'customers', function (Blueprint $table) {
$table->json('meta')->nullable()->change();
$table->json('attribute_data')->nullable()->change();
});
Schema::table($this->prefix . 'addresses', function (Blueprint $table) {
$table->json('meta')->nullable()->change();
});
// if (!Schema::hasTable('media')) {
// Schema::table('media', function (Blueprint $table) {
// $table->json('manipulations')->change();
// $table->json('custom_properties')->change();
// $table->json('generated_conversions')->change();
// $table->json('responsive_images')->change();
// });
// }
Schema::table($this->prefix . 'collections', function (Blueprint $table) {
$table->json('attribute_data')->change();
});
// $activityLogTable = config('activitylog.table_name');
// if (!Schema::hasTable($activityLogTable)) {
// Schema::table($activityLogTable, function (Blueprint $table) {
// $table->json('properties')->nullable()->change();
// });
// }
Schema::table($this->prefix . 'product_options', function (Blueprint $table) {
$table->json('name')->change();
$table->json('label')->nullable()->change();
});
Schema::table($this->prefix . 'product_option_values', function (Blueprint $table) {
$table->json('name')->change();
});
Schema::table($this->prefix . 'orders', function (Blueprint $table) {
$table->json('tax_breakdown')->change();
$table->json('meta')->nullable()->change();
$table->json('discount_breakdown')->nullable()->change();
$table->json('shipping_breakdown')->nullable()->change();
});
Schema::table($this->prefix . 'order_lines', function (Blueprint $table) {
$table->json('tax_breakdown')->change();
$table->json('meta')->nullable()->change();
});
Schema::table($this->prefix . 'order_addresses', function (Blueprint $table) {
$table->json('meta')->nullable()->change();
});
Schema::table($this->prefix . 'transactions', function (Blueprint $table) {
$table->json('meta')->nullable()->change();
});
Schema::table($this->prefix . 'carts', function (Blueprint $table) {
$table->json('meta')->nullable()->change();
});
Schema::table($this->prefix . 'cart_addresses', function (Blueprint $table) {
$table->json('meta')->nullable()->change();
});
Schema::table($this->prefix . 'cart_lines', function (Blueprint $table) {
$table->json('meta')->nullable()->change();
});
Schema::table($this->prefix . 'product_variants', function (Blueprint $table) {
$table->json('attribute_data')->nullable()->change();
});
Schema::table($this->prefix . 'discounts', function (Blueprint $table) {
$table->json('data')->nullable()->change();
});
Schema::table($this->prefix . 'brands', function (Blueprint $table) {
$table->json('attribute_data')->nullable()->change();
});
Schema::table($this->prefix . 'customer_groups', function (Blueprint $table) {
$table->json('attribute_data')->nullable()->change();
});
}
};
PS.
Error on customer view page:
