Strange behavior with multiple schemaless attributes in one model

[salvobee]

Hi there

Something strange is happening here...

I have a model with multiple schemaless attributes, querying the model using the scoped queries works only for one of the attributes:

✔️ Query builder using customer attribute

>>> Profile::withCustomer('display_name', 'will.com')->first();
=> App\Models\Profile {#4325
     id: "cd1e1855-733a-454f-8313-e6dc48457a34",
     user_id: "d79b2c15-c2c0-4b6e-a62d-826e6c5ebe90",
     type: "business",
     customer: "{"legal_name":"DarkOliveGreen","legal_status":null,"display_name":"will.com","vat":"East Leif","business_category":"IX","suggested_category":"84598","tags":["quibusdam","sed","voluptates"],"referer_name":"Mann Mount","referer_mail":"[email protected]","referer_phone":"[email protected]"}",
     contact_data: "{"email":"[email protected]","alternative_email":"Nikita Villages","phone":"504-915-1919","alternative_phone":null}",
     address: "{"country":"Bosnia and Herzegovina","city":"Dareside","district":"59607-3271","zip_code":"19337","address":"239 Shaniya Flats Apt. 388\nPort Stanberg, OK 67916-9263","street_number":"Hintz Summit","address_reference":"4857 Hand Spur\nEast Alanaview, NE 74844-0967","latitude":2277.8077011,"longitude":3428}",
     loyalty_profile: "{"card_number":"Liechtenstein","card_balance":1}",
     privacy: "{"privacy_policy_accepted":false,"commercial_offers_authorized":false}",
     created_at: "2021-07-21 12:45:59",
     updated_at: "2021-07-21 12:45:59",
   }
>>>

❌ Query builder using privacy attribute

>>> Profile::withPrivacy('privacy_policy_accepted',false)->get();
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 1 malformed JSON (SQL: select * from "profiles" where json_extract("privacy", '$."privacy_policy_accepted"') = false)'
>>>

I've triple checked the syntax in model and everything, but no guess on why I'm getting this error.

Here are my how my model looks like:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Jamesh\Uuid\HasUuid;
use Spatie\SchemalessAttributes\SchemalessAttributesTrait;

class Profile extends Model
{
    use HasUuid, HasFactory, SchemalessAttributesTrait;


    protected $fillable = [
        'type',
        'customer',
        'contact_data',
        'address',
        'loyalty_profile',
        'privacy',
    ];

    protected $schemalessAttributes = [
        'customer',
        'contact_data',
        'address',
        'loyalty_profile',
        'privacy',
    ];


    // region SCOPES

    /**
     * Scopes query to lookup inside customer schemaless attribute.
     *
     * @return Builder
     */
    public function scopeWithCustomer(): Builder
    {
        return $this->customer->modelScope();
    }

    /**
     * Scopes query to lookup inside contact data schemaless attribute.
     *
     * @return Builder
     */
    public function scopeWithContactData(): Builder
    {
        return $this->contact_data->modelScope();
    }

    /**
     * Scopes query to lookup inside address schemaless attribute.
     *
     * @return Builder
     */
    public function scopeWithAddress(): Builder
    {
        return $this->address->modelScope();
    }

    /**
     * Scopes query to lookup inside loyalty profile schemaless attribute.
     *
     * @return Builder
     */
    public function scopeWithLoyaltyProfile(): Builder
    {
        return $this->loyalty_profile->modelScope();
    }

    /**
     * Scopes query to lookup inside privacy schemaless attribute.
     *
     * @return Builder
     */
    public function scopeWithPrivacy(): Builder
    {
        return $this->privacy->modelScope();
    }

    // endregion

    // region RELATIONSHIPS

    /**
     * Returns user who belongs to current profile.
     *
     * @return BelongsTo
     */
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    // endregion
}

And my migration

<?php

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

class CreateProfilesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('profiles', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->foreignUuid('user_id');
            $table->string('type');
            $table->schemalessAttributes('customer');
            $table->schemalessAttributes('contact_data');
            $table->schemalessAttributes('address');
            $table->schemalessAttributes('loyalty_profile');
            $table->schemalessAttributes('privacy');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('profiles');
    }
}

Query scope works only for customer attribute and not for the others....

I'm using SQLite and data comes from a DtoFactory

[salvobee]

Updates: looks like the issue can be reproduced only using SQLite...

I've just tried to test it on mysql (version included with sail) and everything works flawlessy!