Relationship on the same table

1

Hello,
I have been blocking for a few days on a problem.
I can not display the results of a hasMany relationship on the same table. Maybe I do not hurt myself. Could someone give me his opinion? Thank you in advance.

I have a table videos, when I display the detail of a video, I will like to display related videos. So I created a related_videos table.

CREATE TABLE videos (
   id bigint UNSIGNED NOT NULL AUTO_INCREMENT,
   title varchar(255) NULL,
   slug varchar(255) NULL,
   thumb varchar(255) NULL,
   duration int UNSIGNED NOT NULL DEFAULT 0,
   publish_date datetime NULL,
   active bool NULL,
   CONSTRAINT videos_pk PRIMARY KEY (id)
);

CREATE TABLE related_videos (
   id bigint UNSIGNED NOT NULL AUTO_INCREMENT,
   parent_id bigint UNSIGNED NOT NULL,
   video_id bigint UNSIGNED NOT NULL,
   CONSTRAINT related_videos_pk PRIMARY KEY (id)
);

I wish to have this kind of result, a video can be related to several other videos, that’s why I did not include it in the table videos

Video ID Video Title Related Video ID Related Video Tilte
1 Video A 2 Video B
1 Video A 3 Video C
1 Video A 4 Video D
3 Video C 1 Video A
3 Video C 2 Video B
3 Video C 4 Video D 
class VideosTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);
        ...

        $this->hasMany('RelatedVideos', [
            'className' => 'Videos',
            'foreignKey' => 'parent_id',
        ]);
    }
}

class RelatedVideosTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);
        ...

        $this->belongsTo('Videos', [
            'foreignKey' => 'parent_id',
            'joinType' => 'INNER',
        ]);
    }
}

When I select a video, I have the following result:

array:26 [▼
  "id" => 1
  "thumb" => "eI7jFbspBXeL22iX4.jpg"
  "title" => "Video A"
  "related_videos" => array:2 [▼
        0 => array:3 [▼
              "id" => 1
              "parent_id" => 1
              "video_id" => 2
        ]
        1 => array:3 [▶]
      ]
]

How to get this result

array:26 [▼
  "id" => 1
  "thumb" => "eI7jFbspBXeL22iX4.jpg"
  "title" => "Video A"
  "related_videos" => array:2 [▼
        0 => array:3 [▼
              "id" => 2
              "thumb" => "1TktC1AW1WUDtO9F12.jpg"
              "title" => "Video B"
        ]
        1 => array:3 [▶]
      ]
]

Thanks for your help.

2

You can write hasAndBelongsToMany relation ship to get the result. Please check how it works

3

Thank you, it’s a very good idea. I did not think about it. I created a Behavior to do the job but it is no longer necessary, I will changed my code to use belongsToMany instead.
Thanks again.

4

hasMany and belongsTo aren’t the right relations here. You definitely want a belongsToMany.

5

That is brilliant Thank you very much