sql

<?php

declare(strict_types=1);

namespace Drupal\my_module\Plugin\views\field;

use Drupal\views\Attribute\ViewsField;
use Drupal\views\Plugin\views\field\FieldPluginBase;
use Drupal\views\ResultRow;

/**
 * Sortable yes/no column backed by an EXISTS subquery from views data.
 *
 * '@base' in the 'exists subquery' definition is replaced with the alias of
 * the base table.
 */
#[ViewsField('exists_subquery')]
class ExistsSubquery extends FieldPluginBase {

  public function query() {
    $this->ensureMyTable();
    $exists = 'EXISTS (' . str_replace('@base', $this->tableAlias, $this->definition['exists subquery']) . ')';
    $this->field_alias = $this->query->addField(NULL, "CASE WHEN $exists THEN 1 ELSE 0 END", $this->realField);
  }

  public function clickSort($order) {
    $this->query->addOrderBy(NULL, NULL, $order, $this->field_alias);
  }

  public function render(ResultRow $values) {
    return $this->getValue($values) ? $this->t('yes') : $this->t('no');
  }

}

<?php

declare(strict_types=1);

namespace Drupal\my_module\Plugin\views\filter;

use Drupal\views\Attribute\ViewsFilter;
use Drupal\views\Plugin\views\filter\BooleanOperator;

/**
 * Yes/no filter backed by an EXISTS subquery from views data.
 */
#[ViewsFilter('exists_subquery')]
class ExistsSubquery extends BooleanOperator {

  public function defaultExposeOptions() {
    parent::defaultExposeOptions();
    // BooleanOperator exposes as required without a stored value, which fails
    // form validation. Default to "- Any -" instead.
    $this->options['expose']['required'] = FALSE;
    $this->options['value'] = 'All';
  }

  public function query() {
    if ($this->value === 'All') {
      return;
    }
    $this->ensureMyTable();
    $exists = 'EXISTS (' . str_replace('@base', $this->tableAlias, $this->definition['exists subquery']) . ')';
    $this->query->addWhereExpression($this->options['group'], empty($this->value) ? "NOT $exists" : $exists);
  }

}

<?php

declare(strict_types=1);

namespace Drupal\my_module\Hook;

use Drupal\Core\Hook\Attribute\Hook;
use Drupal\Core\StringTranslation\StringTranslationTrait;

final class MyModuleViewsHooks {

  use StringTranslationTrait;

  #[Hook('views_data_alter')]
  public function viewsDataAlter(array &$data): void {
    $subquery = 'SELECT 1 FROM {node__field_related_content} ref WHERE ref.field_related_content_target_id = @base.nid';
    $data['node_field_data']['is_referenced'] = [
      'title' => $this->t('Is referenced'),
      'field' => ['id' => 'exists_subquery', 'click sortable' => TRUE, 'exists subquery' => $subquery],
      'filter' => ['id' => 'exists_subquery', 'label' => $this->t('Is referenced'), 'exists subquery' => $subquery],
    ];
  }

}

<?php

namespace Drupal\my_module_migrate\Plugin\migrate\source\d8;

use Drupal\migrate\Row;

/**
 * Base class for D8 source plugins to collect field values from Field API.
 *
 *  Available configuration keys:
 *   - id: (optional) The id of the content which should get migrated. This can
 *     be useful to only migrate a selected set of nodes. Excepts multiple ids.
 *
 * @MigrateSource(
 *   id = "my_module_d8_gallery",
 *   source_provider = "my_module_migrate"
 * )
 */
class MyModuleGallery extends ContentEntity {

  /**
   * {@inheritdoc}
   */
  public function query() {
    $query = parent::query();

    if (isset($this->configuration['id'])) {
      $entityDefinition = $this->entityTypeManager->getDefinition($this->configuration['entity_type']);
      $idKey = $entityDefinition->getKey('id');
      $query->condition("d.{$idKey}", $this->configuration['id'], 'IN');
    }

    // Left join to get the nid of the old image node.
    $query->leftJoin('paragraph__field_referenzfeld_galerie', 'pfrg', 'pfrg.entity_id = b.id AND pfrg.revision_id = b.revision_id');
    $query->leftJoin('node__field_gallery_bild', 'nfgb', 'nfgb.entity_id = pfrg.field_referenzfeld_galerie_target_id');

    $query->leftJoin('node__field_base_public_title', 'nfbpt', 'nfbpt.entity_id = pfrg.field_referenzfeld_galerie_target_id');

    $query->addExpression('GROUP_CONCAT(nfgb.field_gallery_bild_target_id)', 'gallery_image_ids');
    // Add public title field.
    $query->addField('nfbpt', 'field_base_public_title_value', 'field_base_public_title_value');

    $query->groupBy('b.id');

    return $query;
  }

  /**
   * {@inheritdoc}
   */
  public function prepareRow(Row $row) {
    $return = parent::prepareRow($row);

    $nids = [];
    $ids_string = $row->getSourceProperty('gallery_image_ids');
    if (!empty($ids_string)) {
      $ids = explode(',', $ids_string);

      foreach ($ids as $id) {
        $nids[] = ['nid' => (int) $id];
      }
      // Add new source property to skip old gallery node within migration.
      $row->setSourceProperty('field_referenzfeld_galerie', $nids);
    }
    $row->setSourceProperty('field_base_public_title', $row->getSourceProperty('field_base_public_title_value'));

    return $return;
  }

}

SELECT 
  GROUP_CONCAT(nfgb.field_gallery_bild_target_id), 
  nfbpt.field_base_public_title_value
FROM paragraph__field_referenzfeld_galerie AS pfrg
LEFT JOIN node__field_gallery_bild AS nfgb
  ON nfgb.entity_id = pfrg.field_referenzfeld_galerie_target_id
LEFT JOIN node__field_base_public_title AS nfbpt
  ON nfbpt.entity_id = pfrg.field_referenzfeld_galerie_target_id
WHERE pfrg.entity_id = 100810 AND pfrg.revision_id = 1919384

SET @SearchString = 'my_search_value';
SET @DatabaseName = 'my_database';

SET @SQL = CONCAT('
SELECT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME, ".", COLUMN_NAME) AS Location,
       (SELECT COUNT(*) 
        FROM `', @DatabaseName, '`.`', TABLE_NAME, '` 
        WHERE `', COLUMN_NAME, '` LIKE CONCAT("%", "', @SearchString, '", "%")
       ) AS Occurrences
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = "', @DatabaseName, '"
  AND DATA_TYPE IN ("char", "varchar", "text", "tinytext", "mediumtext", "longtext")
HAVING Occurrences > 0
ORDER BY Occurrences DESC
');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DELETE FROM `key_value` WHERE `collection` = 'update_fetch_task';

SELECT table_name AS `table`, round(((data_length + index_length) / 1024 / 1024), 2) `size in MB`
FROM information_schema.TABLES
WHERE table_schema = "DATABASE_NAME"
ORDER BY `size in MB` DESC
LIMIT 10;