A Fistul of Snippets (I): Five tips (database-related) for Drupal 8

Hello, It's me. I have compiled some of the snippets created during this month of September and I have grouped them in this article, through the common element: they are proposals to work with the database in the context of Drupal 8. 

About the Author:

----------------

• David Rodríguez, @davidjguru.
• Contact at: [email protected]
• Website: The Russian Lullaby, https://therussianlullaby.com
• Drupal.org profile: https://www.drupal.org/u/davidjguru
• Linkedin/davidjguru: https://www.dhirubhai.net/in/davidjguru
• Blog, english: https://davidjguru.github.io
• Medium, spanish: https://medium.com/@davidjguru

---------------------------------------------------------------------------------------------------

1 - Changing the active database connection at runtime.

Just a small example of changing database connections within our code, using the parameters registered in settings.php.

First: You have declared the database connections in your settings.php file. The $databases array is a two levels array with form ['key']['target'], where 'key' reference a database and 'target' set options added to 'default' for master/slave configurations. 

// Default Database with key = default
$databases['default']['default'] = array (
'database' => 'database_name',
'username' => 'database_user',
'password' => 'database_password',
'prefix' => '',
'host' => 'localhost',
'port' => '3306',
'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
'driver' => 'mysql',
);

// Secondary database with key = secondarydb
$databases['secondary']['default'] = array (
'database' => 'database2_name',
'username' => 'database2_user',
'password' => 'database2_password',
'prefix' => '',
'host' => 'localhost',
'port' => '3306',
'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
'driver' => 'mysql',
);

Second: We'll save the $key value from the current connected database.

$default= Database::getConnection()->getKey();

Third: We'll set the new connection registered in settings.php.

Database::setActiveConnection('secondary');
// Doing your stuff over the secondary database. 
// ...

Fourth: Recovers the connection to the main database. 

Database::setActiveConnection($default);

Here you have available the snippet in case you want to download the file: https://gitlab.com/snippets/1893772

2- Building dynamic queries with injected services from a Controller.

In this example we are going to make a controller that will respond to a path of the file your_custom_module.routing.yml with its comment() method. We'll inject two services to get the current user id and a database connection object.

your_custom_module.comment:
  path: '/your_custom_module/database/comment'
  defaults:
    _controller: '\Drupal\your_custom_module\Controller\YourCustomModuleController::comment'
    _title: 'Your Custom Module Comments Query Page Results'
  requirements:
    _access: 'TRUE'

The goal is to get a list of comments rendered as a table, filtered by the id of the current user with several parameters through a join. We'll use two tables: 'comments' and 'comment_field_data'.

<?php

/**
 * @file
 * Contains \Drupal\your_custom_module\Controller\YourCustomModuleController.
 */

namespace Drupal\your_custom_module\Controller;

use Drupal\Core\Controller\ControllerBase;
use Drupal\Core\Database\Driver\mysql\Connection;
use Drupal\Core\Session\AccountInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;

class YourCustomModuleController extends ControllerBase {
 protected $current_user;
 protected $connection;
 
  public function __construct( AccountInterface $current_user,
                               Connection $connection) {
    $this->current_user = $current_user;
    $this->connection = $connection;
  }

// Injecting Services. 
  public static function create(ContainerInterface $container) {
    return new static(
      $container->get('current_user'),
      $container->get('database')
    );
  }
  
  public function comment() {
    $output = [];

    // Build a dynamic select query.
    $query = $this->connection->select('comment_field_data', 'a');

    // Add methods, conditions and rules to the query object.
    $query->join('comment', 'b', 'a.cid = b.cid');
    $query->fields('a', ['cid', 'entity_id', 'subject', 'uid']);
    $query->fields('b', ['uuid', 'extra']);
    $query->condition('a.uid', $this->currentUser->id());
    $query->orderBy('cid', 'DESC');

    // Show the query object as SQL statement in screen, as dpm - kint style.
    dpq($query);

    // Executes the select query.
    $result = $query->execute();

    // Go Through the results.
    $rows = [];
    foreach($result as $record) {
      $rows[] = [
        $record->cid,
        $record->entity_id,
        $record->subject,
        $record->uid,
        $record->uuid,
        $record->extra,
      ];
    }

    // Build the final table ready to render.
    $output['comment_table'] = [
      '#type' => 'table',
      '#rows' => $rows,
      '#header' => [
          $this->t('Comment ID'),
          $this->t('Entity ID'),
          $this->t('Subject'),
          $this->t('User ID'),
          $this->t('Universal Unique ID'),
          $this->t('Extra Field'),
        ],

];

    return $output;
  }

In my case, using the admin user, the SQL query showed by dpq($query) in screen is just like this:

SELECT a.cid AS cid, a.entity_id AS entity_id, a.subject AS subject, 
a.uid AS uid, b.uuid AS uuid, b.extra AS extra
FROM
{comment_field_data} a
INNER JOIN {comment} b ON a.cid = b.cid
WHERE a.uid = '1'
ORDER BY cid DESC

Here you have available the snippet in case you want to download the file: https://gitlab.com/snippets/1891159

3- Checking if a node exists, creating and showing it.

Let's go to check if a node already exists taking by its title...if not, then we'll create one new node and we'll launch it to the render system, ready to show it in screen.

Context: We are in a Controller, within a method. We're catching a request from a route defined in your_custom_module.routing.yml. Our mission is to return a node ready to be rendered on screen. If the node doesn't exist, we will create it.

// Set initial basic properties.
  $message = '';
  $output = [];
  $entity_type = 'node';
  $node_title = 'Title of the Node - Chiquito Ipsum';
  $node_data  = [
    'type' => 'article',
    'title' => 'Title of the Node - Chiquito Ipsum',
    'body' => 'Lorem fistrum se calle ustée no puedor no te digo trigo por no llamarte Rodrigor. 
               Benemeritaar mamaar por la gloria de mi madre diodenoo te va a hasé pupitaa 
               sexuarl diodenoo no puedor a peich. Apetecan ese pedazo de hasta luego Lucas 
               diodeno pupita. Al ataquerl está la cosa muy malar caballo blanco caballo negrorl.',
    ];

// Check if the node item already exists getting an array of Node IDs - nids.
  $query = \Drupal::entityTypeManager()->getStorage($entity_type)->getQuery();
  $query->condition('title', $node_title);
  $nids = $query->execute();

if (!(empty($nids))) {

    // Set text for message.
    $message = 'An Entity with title: @title was located in database.';

    // If there are some nodes with same title we only want the first case.
    $first_key = array_key_first($nids);
    $node = \Drupal::entityManager()->getStorage('node')->load($nids[$first_key]);

    // Send to the render system the first node ready to print in screen.
    $view_builder = \Drupal::entityTypeManager()->getViewBuilder('node');
      
    // Assign the render structure to the output.
    $output = $view_builder->view($node);
  
  } else {

     // Set text for message.
     $message = 'An Entity named: @name not exists in database.';

     // Build and save the node item.
     $node_item = \Drupal::entityManager() 
        ->getStorage('node')
        ->create($node_data);
     $node_item->save();

      // Ask for a render structure of the node item.
      $view_builder = \Drupal::entityTypeManager()->getViewBuilder('node');

      // Assign the render structure to the output.
      $output = $view_builder->view($node_item);

    }

  // Set a new message in Log - /admin/reports/dblog.
  \Drupal::logger('your_custom_module')->notice($message, ['@title' => $node_title]);
    
// Launch the output to the render system.
  return $output;

Well, in my usual happiness, I took the function array_key_first() without thinking that it is available from PHP 7.3.0...well if your version is older, it won't be available...but you can easily build your own version of the function and insert it into the previous code block.

Remember: We were extracting from the database the id value of a node through the its title but for the possibility that there were several equal, we have an array of Node IDs ($nids). The first one already serves us, so we only need the first nid of the array. As it is an associative array with random values in the indexes, we use a foreach to go through it. We take the first one and exit the loop.

if(!function_exists('array_key_first')) {
      function array_key_first(array $arr) {
        foreach($arr as $key => $unnecessary_value) {
          return $key;
        }
        return NULL;
      }
    }

Here you have available the snippet in case you want to download the file: https://gitlab.com/snippets/1890764

4- Drupal 8: Adding new fields in the database (install / uninstall).

We're playing with some methods of the Scheme / Database API of Drupal 8 in particular with functions that allow us to locate tables and fields, creating or deleting them.

Context: In a custom module, within the .install file, Doing tasks on the installation. Looking for a field and create if not exist. We'll delete this field in the uninstallation process. We're using a pair of basic Drupal hooks: install and uninstall. 

/**
 * Implements hook_install().
 */
function your_custom_module_install() {

  // Get the connection and the database schema.
  $connection = \Drupal::service('database');
  $schema = $connection->schema();

  // Test if the table 'Comment' exist or not.
  if ($schema->tableExists('comment')){
  
    // Test if the field 'extra' exist or not.
    if(!($schema->fieldExists('comment','extra'))) {

      // Build the field definition.
      $field_definition = [
        'description' => 'Newly created Extra Field.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 1,
      ];

      // Adding the new field if the field not exist.
      $schema->addField('comment', 'extra', $field_definition);

      // Set a new message.
      \Drupal::logger('your_custom_module')->notice('Created field Extra');
    }
  }
}

And the hook for unistall the module:

/**
 * Implements hook_uninstall().
 */
function your_custom_module_uninstall() {

  // Get the connection and the database schema.
  $connection = \Drupal::service('database');
  $schema = $connection->schema();

  // Test if the table 'Comment' exist or not.
  if ($schema->tableExists('comment')) {
  
    // Test if the field 'extra' exist or not.
    if ($schema->fieldExists('comment', 'extra')) {
    
    // If exist then delete the field. 
      $schema->dropField('comment', 'extra');
    // Set a new message.
    \Drupal::logger('your_custom_module')->notice('Field Extra deleted.');
    }
  }
}

Here you have available the snippet in case you want to download the file: https://gitlab.com/snippets/1890485

5- Database interactions via Connection Object.

Some diverse ways to connect to a Database, using diverse styles.

// In a Procedural way via the Service Container or directly from the class.
// It's not very refined and the Drupal Inquisition will fall on you. 
$connection1 = \Drupal::database();
$connection2 = \Drupal::service('database');
$connection3 = \Drupal::getConnection();

// Creating a connection to a secondary database(sites with multiple databases). 
$connection4 = \Drupal\Core\Database\Database::getConnection('default', 'second_database');
// As in your settings.php
$databases['second_database']['default']

// In a OOP way using Dependency Injection from a class like a Controller.
// First - In your class, you will have a property. 
protected $my_database;

// Second - In your class your construct will receive a Connection param.
public function __construct(Connection $database) {
  $this->my_database = $database;
}

// Third - Using a "create" function with Container in order to get the service. 
public static function create(ContainerInterface $container) {
  return new static(
    $container->get('database')
  );
}

// Fourth - Now you can use the service in your methods.
$this->my_database->insert('message')
[...]

Here you have available the snippet in case you want to download the file: https://gitlab.com/snippets/1890008

Did you get to the end of the article? Did you find it interesting?...Here I put a couple of links where I also write about Drupal: 

• https://davidjguru.github.io (in English)
• https://medium.com/@davidjguru (in Spanish)

:wq!