通过ID获取记录，并在Laravel 5中递归所有foreging

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 ;)