Kohana ORM relationship has_many and subcategories displaying correctly?

Question

ORM

Good afternoon. There are 2 tables Categories and Apps. They are linked by the Categories_Apps table. The Categories table has a parent_id field for creating subcategories.
How is it possible, knowing the Categories ID, to first get the IDs of all nested children and then all the records from the Apps table corresponding to these IDs of the category and subcategories?
Categories:
|id|parent_id|
|1|0|
|2|1|
|3|2|
|4|0|
Categories_Apps:
|category_id|app_id|
|1|1|
|3|2|
Apps:
|id|name|
|1|test|
|2|test2|
That is, knowing knowing ID 1, you need to get all subcategories from the Categories table (ID: 1,2,3) and then get all the records from the Apps table (ID: 1,2). I hope I was able to correctly explain this trivial but perplexing question.

Answer 1

You can do this, the idea is clear, I think, if I understood the question correctly :)
You can add the parent id, if so.
Well, the methods are in the models, and not like here :)

class Model_Category extends ORM
{
  protected $_table_name = 'categories';

  protected $_has_many = array
  (
    'subs' => array(
      'model' => 'Category',
      'foreign_key' => 'parent_id',
    ),
    'apps' => array(
      'model' => 'App',
      'through' => 'categories_apps',
      'foreign_key' => 'category_id',
      'far_key' => 'app_id',
    ),
  );
}

class Model_App extends ORM
{
  protected $_table_name = 'apps';

  protected $_has_many = array
  (
    'categories' => array(
      'model' => 'Category',
      'through' => 'categories_apps',
      'foreign_key' => 'app_id',
      'far_key' => 'category_id',
    ),
  );
}

class Controller_Welcome extends Controller
{
  public function action_index()
  {
    $id = 1;

    // Variant 1
    $subs_ids = $this->get_subs_ids_v1($id);
    $this->show_subs_ids($subs_ids);
    $this->show_apps($subs_ids);

    // Variant 2
    $subs_ids = $this->get_subs_ids_v2($id);
    $this->show_subs_ids($subs_ids);
    $this->show_apps($subs_ids);
  }

  private function get_subs_ids_v1($id)
  {
    // --------
    // Category
    $category = ORM::factory('Category', $id);

    if ( ! $category->loaded())
      throw new HTTP_Exception_404;

    echo 'Category: ' . $category->title . '<br/>';

    // ------------------
    // Sub-Categories ids
    return $category
      ->subs
      ->find_all()
      ->as_array(NULL, 'id');
  }

  private function get_subs_ids_v2($id)
  {
    // ------------------
    // Sub-Categories ids
    return ORM::factory('Category')
      ->where('parent_id', '=', $id)
      ->find_all()
      ->as_array(NULL, 'id');
  }

  private function show_subs_ids($subs_ids)
  {
    echo '<pre>' . print_r($subs_ids, TRUE) . '</pre>';
  }

  private function show_apps($subs_ids)
  {
    // $subs_ids может быть пустым, нужна дополнительная проверка,
    // чтобы where IN не выдало шибку
    $apps = ORM::factory('App')
      ->join('categories_apps')
      ->on('categories_apps.app_id', '=', 'app.id')
      ->where('categories_apps.category_id', 'IN', $subs_ids)
      ->find_all();

    foreach ($apps as $app)
      echo 'App: ' . $app->title . '<br/>';
  }
}

Category: cat-1

Array
(
    [0] => 2
    [1] => 3
    [2] => 4
)

App: app-1
App: app-2
App: app-3
App: app-4
App: app-5
App: app-6

Array
(
    [0] => 2
    [1] => 3
    [2] => 4
)

App: app-1
App: app-2
App: app-3
App: app-4
App: app-5
App: app-6