SqliteGrammar / Sqlite support about eloquent-json-relations HOT 11 CLOSED

SQLite is extremely fast, so I don't think performance should be an issue.

from eloquent-json-relations.

Hi @antoniopaisfernandes,
What version of SQLite are you using?

from eloquent-json-relations.

Hi,
I'm using 3.39.2

from eloquent-json-relations.

How are you using the package in your app? What relationships did you define and test?

from eloquent-json-relations.

I have a Workshop model like this:

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Staudenmeir\EloquentJsonRelations\HasJsonRelationships;
use Staudenmeir\EloquentJsonRelations\Relations\BelongsToJson;

class Workshop extends Model
{
    use HasFactory,
        HasJsonRelationships;

    public $incrementing = false;

    protected $guarded= [];

    protected $casts = [
        'team_leader_ids' => 'json',
    ];

    public function teamLeaders(): BelongsToJson
    {
        return $this->belongsToJson(User::class, 'team_leader_ids');
    }
//...
}

And a related model with a scope:

class WorkshopWarranty extends Model
{
    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope(new WorkshopWarrantyScope());
    }

    public function workshop(): BelongsTo
    {
        return $this->belongsTo(Workshop::class);
    }
//...
}


class WorkshopWarrantyScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $builder->whereHas('workshop', function (Builder $query) {
            $query->whereHas('teamLeaders', fn (Builder $query) => $query->where('id', auth()->id()));
        });
    }
}

Running in MariaDB gets the following SQL:

SELECT
    *
FROM
    `workshop_warranties`
WHERE
    EXISTS (
        SELECT
            *
        FROM
            `workshops`
        WHERE
            `workshop_warranties`.`workshop_id` = `workshops`.`id`
            AND EXISTS (
                SELECT
                    *
                FROM
                    `users`
                WHERE
                    json_contains(
                        `workshops`.`team_leader_ids`,
                        json_array(`users`.`id`)
                    )
                    AND `id` = 835
                    AND `users`.`deleted_at` IS NULL
            )
    )

Running in SQLite

SELECT
    *
FROM
    "workshop_warranties"
WHERE
    EXISTS (
        SELECT
            *
        FROM
            "workshops"
        WHERE
            "workshop_warranties"."workshop_id" = "workshops"."id"
            AND EXISTS (
                SELECT
                    *
                FROM
                    "users"
                WHERE
                    EXISTS (
                        SELECT
                            1
                        FROM
                            json_each("workshops"."team_leader_ids")
                        WHERE
                            "json_each"."value" IS json_array(`users`.`id`)
                    )
                    AND "id" = 835
                    AND "users"."deleted_at" IS NULL
            )
    )

Running that SQL in HeidiSQL:

Best

from eloquent-json-relations.

Thanks, I'll look into it. SQLite didn't support these relationships when I created the package, but that was five years ago.

from eloquent-json-relations.

I've released a new version with SQLite support for relationships like yours.

This update has only been possible since Laravel added support for whereJsonContains() on SQLite two weeks ago.
Out of curiosity: Were you waiting for that or was it a coincidence that you startet developing right after the Laravel update?

from eloquent-json-relations.

Wow!! That was fast!

It was a serendipity :)
Developed last week

Your package added 3 months ago:

Thank you!

from eloquent-json-relations.

@staudenmeir is there any query performance trick for SQLite as you already have documented for MySQL / PostgreSQL?
https://github.com/staudenmeir/eloquent-json-relations?tab=readme-ov-file#query-performance

from eloquent-json-relations.

@ren0v Not that I know of. Are you having performance issues?

from eloquent-json-relations.

from eloquent-json-relations.