Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mDelshad's avatar

Column not found: 1054 Unknown column 'coin_user.symbol' in 'field list'

We have three models (user - currencies - balances) What should be the relationship of these tables, for example, I want to receive the amount of bitcoin currency of user To do this, I have written the code as follows

this is user table :

Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->enum('type', [User::TYPE_ADMIN, User::TYPE_USER])->default(User::TYPE_USER);
        $table->string('name');
        $table->string('email')->unique()->nullable();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password')->nullable();
        $table->timestamp('password_changed_at')->nullable();
        $table->rememberToken();
        $table->timestamps();
        $table->softDeletes();
    });

coin table:

 Schema::create('coins', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('symbol')->unique();
        $table->decimal('buy_percent',6,4)->nullable();
        $table->enum('buy_status' , ['active' , 'inactive'])->default('inactive');
        $table->text('buy_description')->nullable();
        $table->decimal('sell_percent',6,4)->nullable();
        $table->enum('sell_status' , ['active' , 'inactive'])->default('inactive');
        $table->text('sell_description')->nullable();
        $table->timestamps();
    });

and balances table. i include pivot table in this migration:

        Schema::create('balances', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('coin_id');
            $table->string('symbol');
            $table->decimal('balance');
            $table->timestamps();
        });
        Schema::create('coin_user', function(Blueprint $table)
        {
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->unsignedBigInteger('coin_id');
            $table->foreign('coin_id')->references('id')->on('coins')->onDelete('cascade');
        });

And relationships are written that way:

in User model :

    public function coins() {
        return $this->belongsToMany(Coin::class)->withPivot(['symbol', 'balance']);
    }

in Coin model :

    public function users() {
        return $this->belongsToMany(User::class)->withPivot(['symbol', 'balances']);
    }

and Balance model :

    public function coins()
    {
        return $this->belongsToMany(Coin::class , 'balances');
    }

    public function user()
    {
        $this->belongsToMany(User::class , 'balances');
    }

when i run this code:

    $coin = Coin::with('users')->get();
    foreach ($coin as $coin) {
        // $coin here is `BTC`, `ETH`, etc.
        foreach ($coin->users as $user) {
            // $user here is Bob, Mike, etc.
            $user->pivot->balance; // 0.16, etc.
            // Do whatever with `$coin` and `$user`
        }
    }

this error returned for me:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'coin_user.symbol' in 'field list' (SQL: select users.*, coin_user.coin_id as pivot_coin_id, coin_user.user_id as pivot_user_id, coin_user.symbol as pivot_symbol, coin_user.balances as pivot_balances from users inner join coin_user on users.id = coin_user.user_id where coin_user.coin_id in (1, 2, 3, 4) and users.deleted_at is null)

What is the problem? How can I access users' balances?

0 likes
8 replies
MichalOravec's avatar

Remove this from relationships, you don't have them in a pivot table.

->withPivot(['symbol', 'balance']);

The pivot table is coin_user and not balances.

Or as pivot use balances for that read a documentation one more time.

https://laravel.com/docs/8.x/eloquent-relationships#many-to-many-model-structure

In addition to customizing the name of the intermediate table, you may also customize the column names of the keys on the table by passing additional arguments to the belongsToMany method. The third argument is the foreign key name of the model on which you are defining the relationship, while the fourth argument is the foreign key name of the model that you are joining to.

https://laravel.com/docs/8.x/eloquent-relationships#defining-custom-intermediate-table-models

https://laravel.com/docs/8.x/eloquent-relationships#custom-pivot-models-and-incrementing-ids

1 like
mDelshad's avatar

Is this structure correct for something I want to do?

SilenceBringer's avatar

@mdelshad I think your balances table actually is the pivot you need

in User model :

    public function coins() {
        return $this->belongsToMany(Coin::class, 'balances')->withPivot(['symbol', 'balance']);
    }

in Coin model :

    public function users() {
        return $this->belongsToMany(User::class,  'balances')->withPivot(['symbol', 'balances']);
    }

and you can remove coin_user table at all

SilenceBringer's avatar
Level 55

@mdelshad as @michaloravec just a typo. in your original code

    public function users() {
        return $this->belongsToMany(User::class,  'balances')->withPivot(['symbol', 'balance']);
    }
mDelshad's avatar

Sorry I clicked wrong I wanted to edit but could not

MichalOravec's avatar

Think about the code, not just copy it. He has a typo there...

mDelshad's avatar

I'm so sorry , I can not understand English well😅 because of this I did

Please or to participate in this conversation.