laravel-tags: SQLSTATE[42000]: Syntax error or access violation: 1064

Hi all, I am trying to do an example of tagging. I have appended my model with the HasTags trait and when I do

        $id = Questions::create([
            'body' => request('title'),
            'skillset_id' => request('skillsetId'),
            'tags' => ['red', 'blue']
        ])->id;

I get:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."en"' = ? and `type` is null limit 1' at line 1 (SQL: select * from `tags` where `name`->'$."en"' = red and `type` is null limit 1)

I am on

Windows 10
MariaDB 10.3.2
MySQL 5.7.19

I have successfully installed and added the migration tables, but the creating of tags is where I fail.

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 22 (2 by maintainers)

Commits related to this issue

Apply fix for MariaDB From https://github.com/spatie/laravel-tags/issues/72 — committed to Inggo/laravel-tags by Inggo 6 years ago
Temp fix for MariaDB not supporting JSON natively Change by https://github.com/spatie/laravel-tags/issues/72 — committed to flexchar/laravel-tags by deleted user 6 years ago

Most upvoted comments

I actually had some problems with the above fix… Was creating duplicate rows… I fixed it with

->where(\DB::raw( "json_extract(name, '$." . $locale . "')" ), '=', $name)

+16

netr on Mar 8, 2018

If someone wants just to get rid of this issue. I solved it by updating one line of code. Update /vendor/spatie/laravel-tags/src/Tag.php line No.60 comment or replace line with below code:

//->whereRaw("name->{$locale}", $name) ->where(\DB::raw( "lcase(json_unquote(json_extract(name, '$._" . $name . "')))" ), '=', strtolower( $locale ))

UPDATED: @netr is right, above mentioned code generate duplicate rows: below is working fine: ->where(\DB::raw( "json_extract(name, '$." . $locale . "')" ), '=', $name)

Thanks @netr

naeemz on Mar 19, 2018

The error message points out that your are using MariaDB. Our package leverages JSON columns which are only supported by MySQL.

freekmurze on Nov 21, 2017

For those, who do not want to edit the source code in the vendor folder, there is obviously an option:

Create new class which will extend the original Tag.php class

<?php namespace App\Classes\Vendor\Spatie\LaravelTags;

/**
 * Class Tag
 * @package App\Classes\Vendor\Spatie\LaravelTags
 */
class Tag extends \Spatie\Tags\Tag {

    public static function findFromString(string $name, string $type = null, string $locale = null)
    {
        $locale = $locale ?? app()->getLocale();

        return static::query()
            ->where(\DB::raw( "json_extract(name, '$." . $locale . "')" ), '=', $name)
            ->where('type', $type)
            ->first();
    }

}

Create new trait, so the new Tag class can be used instead of the old one

<?php namespace App\Classes\Vendor\Spatie\LaravelTags;

trait HasCustomTags {

    public static function getTagClassName(): string {
        return Tag::class;
    }

}

And finally, on the model just add the following code:

use HasTranslations, HasTags, HasCustomTags {
     HasCustomTags::getTagClassName insteadof HasTags;
}

This approach will allow you to successfully deploy code to the server from your local environment without loosing the functionality.

darki73 on Dec 14, 2018

Can I ask, if a fix has been found (as @netr details) why can that not be added to the package, as opposed to the response of “use mysql instead”?

fredbradley on May 25, 2018