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?