Let’s learn recursive CTE SQL via paragraphs

We are going through a partial relaunch and it came up twice to find the nodes which have a certain paragraph somewhere. The first one was just a request to return a list of nodes, the second however required writing an update function to do something with the node. I implemented the first using MySQL 8.0 (MariaDB 10.2 works too) installed locally but the second one required Drupal code written for a lower database version. This provides us with the opportunity to share code with you that does the same in PHP and then in a recursive CTE. Without further ado:

<?php
$db = Drupal::database();
$ids = $db->query('SELECT entity_id FROM {paragraph__field_lc_video_ref}')->fetchCol();
$all_ids = $ids;
do {
  $parents = $db->query('SELECT parent_id FROM {paragraphs_item_field_data} WHERE id IN (:ids[]) AND parent_type = :paragraph', [':ids[]' => $ids, ':paragraph' => 'paragraph'])->fetchCol();
  $all_ids = array_merge($all_ids, $parents);
  $ids = $parents;
} while ($parents);
$nids = $db->query('
  SELECT DISTINCT parent_id 
  FROM {paragraphs_item_field_data} 
  INNER JOIN {node_field_data} n ON parent_id = nid AND n.status = 1 
  WHERE id IN (:ids[]) AND parent_type = :node', [':ids[]' => $all_ids, ':node' => 'node'])->fetchCol();

And then the CTE:

CREATE TEMPORARY TABLE x 
SELECT entity_id FROM paragraph__field_article_section_body 
UNION
SELECT entity_id FROM paragraph__field_article_section_title;
WITH RECURSIVE r AS (
  SELECT id, parent_id, parent_type
  FROM paragraphs_item_field_data
  WHERE id IN (SELECT entity_id FROM x)
  UNION ALL
  SELECT r.id, parent.parent_id, parent.parent_type
  FROM r
  INNER JOIN paragraphs_item_field_data parent ON parent.id = r.parent_id
  WHERE r.parent_type = 'paragraph'
)
SELECT DISTINCT nid
FROM r
INNER JOIN node_field_data n on nid = r.parent_id

---

---