How to properly link multiple tables?

Question

Laravel

I have 6 tables: buildings , streets , settlements , subregions , regions and countries .
There is a connection between them by means of id: buildingshas street_id, streetshas settlement_idand so on up to countries.

How to link them correctly to get the address in one line? For example, the Russian Federation, Altai Territory, Altai District, Barnaul, st. Pushkin 43.

I do all this in Laravel 7.x.

Answer 1

<?php
$model = \App\Models\Building::where('id', 1)->with('street.settlement.subregion.region.country')->first();
echo $model->address;

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Building extends Model
{
    public function street()
    {
        return $this->belongsTo('App\Models\Street');
    }

    public function getAddressAttribute()
    {
        $parts = [
            $this->street->settlement->subregion->region->country->title,
            $this->street->settlement->subregion->region->title,
            $this->street->settlement->subregion->title,
            $this->street->settlement->title,
            $this->street->title,
            $this->title,
        ];
        return join(', ', $parts);
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Street extends Model
{
    public function settlement()
    {
        return $this->belongsTo('App\Models\Settlement');
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Settlement extends Model
{
    public function subregion()
    {
        return $this->belongsTo('App\Models\Subregion');
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Subregion extends Model
{
    public function region()
    {
        return $this->belongsTo('App\Models\Region');
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Region extends Model
{
    public function country()
    {
        return $this->belongsTo('App\Models\Country');
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Country extends Model
{

}

DROP TABLE IF EXISTS `buildings`;
CREATE TABLE `buildings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `street_id` int DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

DROP TABLE IF EXISTS `countries`;
CREATE TABLE `countries` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

DROP TABLE IF EXISTS `regions`;
CREATE TABLE `regions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

DROP TABLE IF EXISTS `settlements`;
CREATE TABLE `settlements` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `subregion_id` int DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

DROP TABLE IF EXISTS `streets`;
CREATE TABLE `streets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `settlement_id` int DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

DROP TABLE IF EXISTS `subregions`;
CREATE TABLE `subregions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `region_id` int DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

INSERT INTO `buildings` (`id`, `street_id`, `title`) VALUES
('1', '1', '43');

INSERT INTO `countries` (`id`, `title`) VALUES
('1', 'Российская Федерация');

INSERT INTO `regions` (`id`, `country_id`, `title`) VALUES
('1', '1', 'Алтайский край');

INSERT INTO `settlements` (`id`, `subregion_id`, `title`) VALUES
('1', '1', 'г. Барнаул');

INSERT INTO `streets` (`id`, `settlement_id`, `title`) VALUES
('1', '1', 'ул. Пушкина');

INSERT INTO `subregions` (`id`, `region_id`, `title`) VALUES
('1', '1', 'Алтайский район');

Answer 2

Евгений, сорри за наглость.
Можно это прописать так, чтоб находясь в другой модели получил другие адреса например с Subregion получаю Subregion, Region и Country или с Region получаю Region и Country.
Имею ввиду не хардкод, а черзе trait