Help with database query

Question

How can I convert this sql query into a Drupal 8 query?

I need all nodes of type 'page' that has not been referenced by nodes of type 'article'. Article content type contains an entity reference field, from where nodes of type 'page' can be referenced.

SELECT *
FROM node_field_data
WHERE type = 'page' AND
node_field_data.nid NOT IN (
   SELECT 1
   FROM node_field_data
   LEFT JOIN node__field_ref
   ON node_field_data.nid = node__field_ref.entity_id
   WHERE type = 'article'
);

I tried with this approach, but it doesn't work:

$sub_result = $this->database->query("
   SELECT 1 FROM {node_field_data} n
   LEFT JOIN {node__field_ref} r
   ON n.nid = r.entity_id
   WHERE n.type = :type",
   ['type' => 'article']
);

$result = $this->database->query("
   SELECT * FROM {node_field_data} n
   WHERE n.type = :type AND n.nid NOT IN :query", [
      'type' => 'page',
      'query' => $sub_result
   ]
);

I get this error:

The website encountered an unexpected error. Please try again later.
Recoverable fatal error: Object of class Drupal\Core\Database\Statement could not be converted to string in Drupal\Core\Database\Statement->execute() (line 59 of core/lib/Drupal/Core/Database/Statement.php).
Drupal\Core\Database\Statement->execute(Array, Array) (Line: 625)
Drupal\Core\Database\Connection->query('
      SELECT * FROM {node_field_data} n
      WHERE n.type = :type AND n.nid NOT IN :sub_result', Array, Array) (Line: 87)
Drupal\Core\Database\Driver\mysql\Connection->query('
      SELECT * FROM {node_field_data} n
      WHERE n.type = :type AND n.nid NOT IN :sub_result', Array) (Line: 141)
...
...
...

Answer 1

Try this out. get_all_nodes_not_referenced_by_article('page');

use Drupal\Core\Database\Database;
/**
 * @param $type
 *
 * @return mixed
 */
function get_all_nodes_not_referenced_by_article($type){

  $connection = Database::getConnection();

  //- Step one get all referenced nids
  $query = $connection->select('node_field_data', 'nfd')
                      ->condition('nfd.type', 'article')
                      ->fields('nfd', ['nid']);
  $query->leftJoin('node__field_ref', 'nfr', 'nfd.nid = nfr.entity_id');
  $result = $query->execute()->fetchAll(PDO::FETCH_ASSOC);

  $referenced_nids = [];
  foreach ($result as $key => $value) {
    $referenced_nids[] = $value['nid'];
  }

  /**
   * Check if $referenced_nids to avoid
   * Drupal\Core\Database\InvalidQueryException: Query condition 'nfd.nid NOT IN ()' cannot be empty.
   * */
  if (!empty($referenced_nids)) {
    return $connection->select('node_field_data', 'nfd')
                      ->condition('nfd.type', $type)
                      ->condition('nfd.nid', $referenced_nids, 'NOT IN')
                      ->fields('nfd', ['nid', 'title'])
                      ->execute()
                      ->fetchAll(PDO::FETCH_ASSOC);
  }
  else {
    return $connection->select('node_field_data', 'nfd')
                      ->condition('nfd.type', $type)
                      ->fields('nfd', ['nid', 'title'])
                      ->execute()
                      ->fetchAll(PDO::FETCH_ASSOC);
  }
}
?>

Answer 2

Thank you so much for your help, berramou.

I just needed to modify it a little bit to make it work:

$query = $this->database->select('node_field_data', 'nfd')
  ->condition('nfd.type', 'article')
  ->fields('nfd', ['nid']);
$query->leftJoin('node__field_ref', 'nfr', 'nfd.nid = nfr.entity_id');
$query->fields('nfr', ['field_ref_target_id'])
  ->isNotNull('nfr.entity_id')
  ->condition('nfr.bundle', 'article');
$result = $query->execute()->fetchAll(PDO::FETCH_ASSOC);

$referenced_nids = [];
foreach ($result as $key => $value) {
  $referenced_nids[] = $value['field_ref_target_id'];
}

if (!empty($referenced_nids)) {
  return $this->database->select('node_field_data', 'nfd')
    ->condition('nfd.type', 'page')
    ->condition('nfd.nid', $referenced_nids, 'NOT IN')
    ->fields('nfd', ['nid', 'title'])
    ->execute()
    ->fetchAll(PDO::FETCH_ASSOC);
}
else {
  return $this->database->select('node_field_data', 'nfd')
    ->condition('nfd.type', 'page')
    ->fields('nfd', ['nid', 'title'])
    ->execute()
    ->fetchAll(PDO::FETCH_ASSOC);
}

I needed to check against field field_ref_target_id (the page nid) and also field entity_id shouldn't be null - otherwise I got all nodes of type page, instead of only nodes that havn't been referenced.

I appreciate you help.