Get record by ID and all foreigns recursive in Laravel 5

Question

I've two tables, looks that (migrations):

Schema::create('sets', function(Blueprint $table)
{
    $table->increments('id');

    $table->string('key');

    $table->string('name');

    $table->string('set_type');

    $table->integer('belongs_to')->unsigned();

    $table->timestamps();

    $table->foreign('belongs_to')->references('id')->on('sets')->onDelete('cascade');

});

Schema::create('posts', function(Blueprint $table)
{
    $table->bigIncrements('id');

    $table->bigInteger('user_id')->unsigned();

    $table->bigInteger('set_id')->unsigned();

    $table->string('post_type', 25);

    $table->text('post');

    $table->boolean('is_reported')->default(false);

    $table->boolean('is_hidden')->default(false);

    $table->timestamps();

    $table->foreign('user_id')->references('id')->on('users');
    $table->foreign('set_id')->references('id')->on('sets');

});

The 'set' table is for storing data in which the location (country, city...) the post should be view. For example, let's store some countries:

   id | key               | name        | belongs_to
   1  | europe            | Europe      | null
   2  | germany-all       | Germany     | 1
   3  | germany-berlin    | Berlin      | 2
   4  | germany-frankfurt | Frankfurt   | 2
   5  | poland-all        | Poland      | 1
   6  | poland-warsaw     | Warsaw      | 5
   7  | england-all       | England     | 1

And, my post has set_id as 6. Looking logically, when I want get posts from Europe (ID 1), that post should be returned too, because 6 belongs to 5, and 5 belongs to 1. And this is that what I want to do. It's possible to do without using too much PHP?

Answer 1

Okay, I found the best solution. It's the Nested Set pattern. I used baum package and it looks that:

For sets table I added Baum's colums:

Schema::create('sets', function(Blueprint $table) {
   $table->bigIncrements('id');
   $table->integer('parent_id')->nullable()->index();
   $table->integer('lft')->nullable()->index();
   $table->integer('rgt')->nullable()->index();
   $table->integer('depth')->nullable();

   $table->string('key');
   $table->string('name');
   $table->string('set_type');

   $table->timestamps();
});

And done! Just get set_id's and return posts, for example:

$sets = Set::where('key', '=', $myKey)->first()->getDescendantsAndSelf()->lists('id');
$posts = Post::whereIn('set_id', $sets)->with(['user'])->take(6)->get();

I’m leave this for posterity ;)

Answer 2

You need to break down region, country and city into different tables and establish the relationship between these OR you can list all the combinations in one single table i.e. ID | Region | Country | City so you would have separate row for each city containing the country and the region.