CODEWITHSUNDEEP
databaselaravelpostgresqleloquent
user12645341
user12645341

Reputation:

Problem with Eloquent : Undefined function: 7 ERROR: operator does not exist

I use postgresql and i have three table

1: Habilitation

code              - string(225)
code_menu  - string(225) 
code_sub_menu  - string(225) 
name             - string(225)

2: menu

code              - string(225)
name             - string(225)

3: submenu

code              - string(225)
name             - string(225)

I want to show habilitations with their menu and sub-menu using Eloquent , when i try :

Habilitation::with(['menu','submenu'])->get();

i get this error : 

Illuminate\Database\QueryException
SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying = integer LINE 1: select * from "menu" where "menu"."code" in (0, 0, 0, 0) ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL: select * from "menu" where "menu"."code" in (0, 0, 0, 0))

My models :

class Menu extends Model
{
    protected $table = "menu";
    public $primaryKey = "code";
    public $timestamps = false;

    protected $casts = [
        'code' => 'string',
    ];

    public function submenu()
    {
        return $this->hasMany('App\Models\SubMenu','code');
    }

    public function habilitation()
    {
        return $this->hasMany('App\Models\Habilitation','code');
    }
}

class SubMenu extends Model
{
    protected $table = "submenu";
    public $primaryKey = "code";
    public $timestamps = false;

    protected $casts = [
        'code' => 'string',
    ];

    public function menu()
    {
        return $this->belongsTo('App\Models\SubMenu','code');
    }

    public function habilitation()
    {
        return $this->hasMany('App\Models\Habilitation','code');
    }

}

class Habilitation extends Model
{
    protected $table = "habilitation";
    public $primaryKey = "code";
    public $timestamps = false;

    protected $fillable = [
        'code', 'code_menu','code_sub_menu'
    ];

    protected $casts = [
        'code' => 'string',
        'code_menu' => 'string',
        'code_sub_menu' => 'string'
    ];

    public function menu()
    {
        return $this->belongsTo('App\Models\Menu','code_menu','code');
    }

    public function submenu()
    {
        return $this->belongsTo('App\Models\SubMenu');
    }

}

i haven't problem using query builder of laravel but i want to use Eloquent for it. someone could help me ? or give me some hints ?
thank you in advance

Upvotes: 2

Views: 13774

Answers (4)

umutyerebakmaz
umutyerebakmaz

Reputation: 1037

Attention developers who use uuid in PostgreSQL. If you are using Trait in Laravel, there is a high probability that you will encounter the same problem.

class ShoppingSession extends Model
{
    use HasFactory, Uuid;

    protected $casts = [
        'id' => 'string',
    ];

    protected $primaryKey = 'id';
    protected $fillable = [
        'user_id',
        'total',
    ];

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function shoppingCart(): HasMany
    {
        return $this->hasMany(ShoppingCart::class);
    }
}

Upvotes: 0

mowses
mowses

Reputation: 29

I had the same issue using Postgres and a MorphMany relationship. I solved it by setting keyType to string in the model that returned the relationship, I did not set it directly to the model because I wanted keyType as integer.

public function merchantOrderable(): MorphMany
{
    $morph = $this->morphMany(OrderModel::class, 'merchant_orderable');
    $morph->getParent()->setKeyType('string');

    return $morph;
}

Upvotes: -1

Hammad Rasheed
Hammad Rasheed

Reputation: 251

This error is related to PostgreSQL. You need to check for proper type casting or column data types as PostgreSQL is strict, and you can see you have type cast your columns to string but in the query it's actually integer. This normally happens when you are referencing INT to VARCHAR or vice versa.

This

select * from "menu" where "menu"."code" in (0, 0, 0, 0)

Should actually be

select * from "menu" where "menu"."code" in ('0', '0', '0', '0')

So I suggest changing your string data type to int for primary & foreign keys.

Upvotes: 0

user12645341
user12645341

Reputation:

i added public $keyType = ‘string’; to resolve this problem . hints : https://www.tekmx.com/blog/using-non-standard-primary-key-with-eloquent-relations-laravel-5

Upvotes: 4

Related Questions