Querying direct and indirect relations in a many-to-many table

migration.php

<?php

Schema::create('media', function (Blueprint $table) {
    $table->id();
    $table->string('native_title')->index();
    // ...
    $table->timestamp('published_at');
    $table->timestamps();
});

Schema::create('media_relations', function (Blueprint $table) {
    $table->id();
    $table->foreignId('parent_id')->constrained('media');
    $table->foreignId('child_id')->constrained('media');
    $table->string('relation_type');
    $table->timestamps();

    $table->unique(['parent_id', 'child_id']);
});

query.sql

WITH RECURSIVE related_media AS (
	SELECT
		child_id AS related_child_id,
		parent_id AS related_parent_id,
		relation_type AS related_relation_type
	FROM
		media_relations
	WHERE
		parent_id = 4 -- Replace with actual ID
		OR child_id = 4 -- Replace with actual ID
	UNION
	SELECT
		child_id,
		parent_id,
		relation_type
	FROM
		media_relations
		JOIN related_media ON parent_id = parent_id
			OR child_id = child_id
)
SELECT DISTINCT ON (media.id)
	related_media.related_relation_type as relation_type,
	media.*
FROM
	related_media
JOIN media ON related_media.related_child_id = media.id
	OR related_media.related_parent_id = media.id
WHERE
	media.id <> 4; -- Replace with actual ID