时间戳

首页 > 解决方案 > Laravel 查询具有 3 个关系的数据透视表

laravel - Laravel 查询具有 3 个关系的数据透视表

问题描述

我会提前感谢您对我无法解决的下一个问题的帮助。

我有 3 张桌子

Contracts
Currencies
Amounts

和数据透视表

contract_currency_amount

在这里，我向您展示迁移

合同

<?php

use Carbon\Carbon;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateContractsTable extends Migration
{
    /**
     * Contracts Table
     *
     * @var string
     */
    private $table = 'contracts';

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create($this->table, function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('number')->unique();
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists($this->table);
    }
}

货币

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCurrenciesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('currencies', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('abbreviation');
            $table->string('description');
            $table->boolean('active')->default(true);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('currencies');
    }
}

金额

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateAmountsTable extends Migration
{
    /**
     * Amounts Table
     *
     * @var string
     */
    private $table = 'amounts';

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create($this->table, function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->float('amount');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists($this->table);
    }
}

Contract_Currency_Amount 数据透视表

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateContractCurrencyAmountTable extends Migration
{
    /**
     * Pivot Table
     *
     * @var string
     */
    private $table = 'contract_currency_amount';

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create($this->table, function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedInteger('contract_id');
            $table->unsignedInteger('currency_id');
            $table->unsignedInteger('amount_id');
            $table->timestamps();

            $table->foreign('contract_id')
                ->references('id')->on('contracts')
                ->onDelete('cascade');

            $table->foreign('currency_id')
                ->references('id')->on('currencies')
                ->onDelete('cascade');

            $table->foreign('amount_id')
                ->references('id')->on('amounts')
                ->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists($this->table);
    }
}

我已经为这些表中的每一个创建了模型，包括数据透视表的模型，但我不知道如何对数据透视表进行查询，以便它返回特定记录的所有数据，关联 3 个表. 例如，我希望在 Blade 的视图中通过一个变量来显示特定合同的所有金额，并能够通过以下方式通过该变量：

@foreach($amounts as $amount)
    {{ $amount->currency->abbreviation }}
@endforeach

我的意思是，我希望查询返回3个表的相关值。我没有使用数据透视表的经验，我将不胜感激..

编辑 1

金额模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Amount extends Model
{
    /**
     * Attributes that should be mass-assignable.
     *
     * @var array
     */
    protected $fillable = ['amount'];
}

货币模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

/**
 * Class Currency
 *
 * @property $id
 * @property $abbreviation
 * @property $description
 * @property $active
 * @property $created_at
 * @property $updated_at
 *
 * @package App
 * @mixin \Illuminate\Database\Eloquent\Builder
 */
class Currency extends Model
{
    /**
     * Attributes that should be mass-assignable.
     *
     * @var array
     */
    protected $fillable = ['abbreviation','description','active'];
}

合同模式

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Models\Entity;
use App\Models\CInitiative;

class Contract extends Model
{
    // TODO: This!
}

合约货币金额模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\Pivot;

class ContractCurrencyAmount extends Pivot
{
    protected $table = 'contract_currency_amount';
}

标签： laraveleloquentorm

解决方案

为了实现您正在寻找的内容，您必须正确设置模型。您必须将 ContractCurrencyAmount 模型设置为普通的 eloquent 模型，而不是扩展枢轴。

合约货币金额

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class ContractCurrencyAmount extends Model
{
    protected $table = 'contract_currency_amount';

    public function contract()
    {
        return $this->belongsTo('App\Contract');
    }

    public function currency()
    {
        return $this->belongsTo('App\Currency');
    }

    public function amount()
    {
        return $this->belongsTo('App\Amount');
    }
}

合同

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Contract extends Model
{
    public function relations()
    {
        return $this->hasMany('App\ContractCurrencyAmount', 'contract_id');
    }
}

货币

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Currency extends Model
{
    public function relations()
    {
        return $this->hasMany('App\ContractCurrencyAmount', 'currency_id');
    }
}

数量

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Currency extends Model
{
    public function relations()
    {
        return $this->hasMany('App\ContractCurrencyAmount', 'amount_id');
    }
}

然后你可以这样做：

foreach($amounts as $amount) {
{
    foreach($amount->relations as $relation) {
        $relation->currency->abbreviation;
    }
}

您还可以使用预先加载来优化您的数据库查询：

$amounts = Amount::with('relations.currency')->get();

这将在上面的 for 循环中预加载您需要的所有数据，而不是在每次迭代时查询数据库。

推荐阅读