时间戳

首页 > 解决方案 > Laravel：返回带有引用列的查询

laravel - Laravel：返回带有引用列的查询

问题描述

如何在查询中获取引用值？如果我有一个示例cities表，其中引用了一个countries表和一个states表的列。

迁移

国家

    public function up()
    {
        Schema::create('cities', function (Blueprint $table) {
            $table->id();
            $table->text('name');
            $table->integer('state_id')->unsigned();
            $table->integer('country_id')->unsigned();

            $table->index('state_id');
            $table->index('country_id');
        });

        Schema::table('cities', function (Blueprint $table)
        {
            $table->foreign('state_id')
                ->references('id')
                ->on('states')
                ->onUpdate('cascade')
                ->onDelete('cascade');
            $table->foreign('country_id')
                ->references('id')
                ->on('countries')
                ->onUpdate('cascade')
                ->onDelete('cascade');
        });
    }

状态

    public function up()
    {
        Schema::create('states', function (Blueprint $table)
        {
            $table->id();
            $table->text('name');
            $table->integer('country_id')->unsigned()->nullable();

            $table->index('country_id');
        });

        Schema::table('states', function (Blueprint $table)
        {
            $table->foreign('country_id')
                ->references('id')
                ->on('countries')
                ->onUpdate('cascade')
                ->onDelete('cascade');
        });
    }

城市

    public function up()
    {
        Schema::create('cities', function (Blueprint $table) {
            $table->id();
            $table->text('name');
            $table->integer('state_id')->unsigned()->nullable();
            $table->integer('country_id')->unsigned()->nullable();

            $table->index('state_id');
            $table->index('country_id');
        });

        Schema::table('cities', function (Blueprint $table)
        {
            $table->foreign('state_id')
                ->references('id')
                ->on('states')
                ->onUpdate('cascade')
                ->onDelete('cascade');
            $table->foreign('country_id')
                ->references('id')
                ->on('countries')
                ->onUpdate('cascade')
                ->onDelete('cascade');
        });
    }

楷模

国家

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Country extends Model
{

    public function state()
    {
        return $this->hasMany(State::class);
    }

    public function city()
    {
        return $this->hasMany(City::class);
    }

    protected $table = 'countries';
}

状态

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class State extends Model
{
    public function country()
    {
        return $this->belongsTo(Country::class);
    }

    public function city()
    {
        return $this->hasMany(City::class);
    }

    protected $table = 'states';
}

城市

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class City extends Model
{
    public function country()
    {
        return $this->belongsTo(Country::class);
    }

    public function state()
    {
        return $this->belongsTo(State::class);
    }

    protected $table = 'cities';
}

输出

如果我查询所有城市City::all()，我可能会得到类似的结果：

all: [
       App\City {#3157
         id: 1,
         name: "New York",
         state_id: 1,
         country_id: 1,
       },
       App\City {#3158
         id: 2,
         name: "Dallas",
         state_id: 2,
         country_id: 1,
       },
       App\City {#3159
         id: 3,
         name: "Miami",
         state_id: 3,
         country_id: 1,
       },
     ],
   }

我将如何返回：

all: [
       App\City {#3157
         id: 1,
         name: "New York",
         state_id: "New York",
         country_id: "USA",
       },
       App\City {#3158
         id: 2,
         name: "Dallas",
         state_id: "Texas",
         country_id: "USA",
       },
       App\City {#3159
         id: 3,
         name: "Miami",
         state_id: "Florida",
         country_id: "USA",
       },
     ],
   }

标签： laravel

解决方案

如果你有正确的关系设置，你应该能够做这样的事情。

City::with('country:id,name', 'state:id,name')->get();

在这里，我假设 和 表都有PKid和name列。countrystate

关系应该是这样的：


<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class City extends Model
{

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

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

推荐阅读