Fetching node field values programmatically

Question

I have figured out how to query the node table programmatically without using the deprecated db_query() function. However, I soon discovered that the actual content fields are not held in the node table, but in a separate revisions table.

I can't figure out how to select fields from the content nodes.

So for example, I can get a count of the number of nodes of type "entity", by using the following code.

use Drupal\Core\Database\Database;
$connection = Database::getConnection();

$sth = $connection->select('node', 'x')
->fields('x', array('nid', 'type'));

$executed = $sth->execute();
$results = $executed->fetchAll(\PDO::FETCH_OBJ);

$type_entities = array();

foreach ($results as $row) {

    // COUNT ONLY ENTITIES
    if ( $row->type == "entity" ) {
        $type_entities[] = $row->nid;
    }
}
echo "Total: ".count( $type_entities );

This code works fine, because I am not referencing any data from the node, just counting the node id's.

But say I also have a field entitled "color", and I want to return that field too, so I can count the number or red, green, blue, etc? The following code won't work, because the "color" field is not in the node table, so can someone please help me to create a working version of the following:

use Drupal\Core\Database\Database;
$connection = Database::getConnection();

$sth = $connection->select('node', 'x')
->fields('x', array('nid', ['type', 'color'] ));  // <-- ADDED color FIELD

$executed = $sth->execute();
$results = $executed->fetchAll(\PDO::FETCH_OBJ);

$type_entities = array();
$is_red = array();
$is_greed = array();
$is_blue = array();

foreach ($results as $row) {

    // COUNT ONLY ENTITIES
    if ( $row->type == "entity" ) {
        $type_entities[] = $row->nid;

        // COUNT COLOURS
        if ( $row->color == "red" ) {
            $is_red[] = $row->nid;
        } else if ( $row->color == "green" ) {
            $is_green[] = $row->nid;
        } else if ( $row->color == "blue" ) {
            $is_blue[] = $row->nid;
        } 
    }
}
echo "Total: ".count( $type_entities );
echo "Red: ".count( $is_red );
echo "Green: ".count( $is_green );
echo "Blue: ".count( $is_blue );

So how do I do that, only fetching the "color" field properly using ->fetchField() or ->fetchCol() maybe?

Thanks for looking!

Answer 1

I think you should leave the database structure and focus on the entities concept.

This brings you to a simpler approach using entityTypeManager to get nodes and filter them by whatever you want.

$query = $this->entityTypeManager()->getStorage('node')->getQuery();
$nids = $query->loadByProperties(
    [
        'type' => 'content_type',
        'status' => 1,
        'field_name' => 'value'
    ])
    ->execute();