[Bug?]: Syntax error with Postgres

[PerryRylance]

What happened?

Hello again folks

Outstanding package, thank you very much!

We are facing an issue here which I very nearly reported on the Laravel framework repo, however on closer inspection, I think this belongs here. I worked around this by making a custom grammar to correct this specific query, but then realised that because options is text, this is probably the expected behaviour from Laravel - so I made a migration for our project to change it to jsonb which also solves the issue for us / Postgres.

How to reproduce the bug

Steps to reproduce

• Fresh install of Laravel, Sail with Postgres, Filament and Bolt
• Log in and go to Connections in the sidebar
• Make a data set with some items in it,
• Navigate to the edit page eg /admin/collections/1/edit

Expected no errors,

Observed SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: text -> unknown LINE 1: ...) as aggregate from "bolt_fields" where ("options"->'dataSou... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. (Connection: pgsql, Host: pgsql, Port: 5432, Database: gaffa_rewards, SQL: select count(*) as aggregate from "bolt_fields" where ("options"->'dataSource')::jsonb @> "1" and "bolt_fields"."deleted_at" is null)

The issue is that options is a string, Postgres is saying we might need to add a cast here.

We fixed this for our project by adding a migration to make options into a JSONB however I appreciate that might not work for folks using databases without JSONB support.

Package Version

4.0.10

PHP Version

8.5.1

Laravel Version

12.11.1

Which operating systems does with happen with?

Linux

Notes

Postgres 18.1

[atmonshi]

I honestly never used Postgres, so can you debug this? and what need to change?

thank you :)

[PerryRylance]

Yes absolutely! Leave this with me 💃

[PerryRylance]

MRE here with containers for everyone's favourite relational databases.

The fix for Postgres is to make this field into JSON which is I suspect what Laravel expects you to do before using whereJsonContains.

I'll try adding a migration to do that and check that everything passes for all DBs.

[PerryRylance]

Forget about the MRE - see #421 this has properly set up containers for popular database engines.

[atmonshi]

so, no changes needed in bolt for whereJsonContains?

@Abdulmajeed-Jamaan what you think about the testing containers?
can we do it with a gh action?

[PerryRylance]

I still need to address that issue @atmonshi - needed to get a container set up to reproduce it and so I can test that the fix I'm proposing won't break other databases - we are using Postgres.

[Abdulmajeed-Jamaan]

I still need to address that issue @atmonshi - needed to get a container set up to reproduce it and so I can test that the fix I'm proposing won't break other databases - we are using Postgres.

You can use https://github.com/nektos/act to run the tests locally and add the tests you need for postgres .