Clicky

Work with the database in Drupal 8

One of the innovations Drupal 7 was an object-oriented approach in the system of building queries to the database. The system was built on functions such as db_select, db_update, db_insert, etc. Direct query writing based on the db_query and db_query_range functions was also supported. The main advantages of this campaign are the lack of binding to a particular database and the convenience of code support. In Drupal 8, all these functions are marked as deprecated (with the release of Drupal 9 they will be removed from the kernel), and it is recommended that database queries be performed based on the corresponding class system.

General principles of interaction with the database in Drupal 8 are based on obtaining the connection object to the current database and calling the methods of this object to build the final query and obtain the result of its execution. You can get the connection object using the service container (Drupal class) using its static database () method. After receiving the connection object, you need to get the object corresponding to the requested query type. As in the seventh version, Drupal 8 builds the query based on methods such as fields (), join (), condition (), and so on. You can execute the query by calling 'the execute `() method.


Sample

The most commonly used method of "communicating" with a database is a sample. When developing modules, we often have to deal with the need to obtain data from the tables of contra modules or the Drupal kernel. You can get the SELECT-request object using the select () method from the connection object. Let's consider some examples of basic SELECT queries:


  1.The simplest sample with condition

        $query = \ Drupal :: database () -> select `('node_field_data', 'nfd');
       
        $query-> fields ('nfd', ['uid', 'title']);
       
        $query-> condition ('nfd.nid', 1);
       
        $result = $query-> execute'() -> fetchAll ();
        

In this example, the values of the uid and title fields of the node_field_data table are selected, provided that the value in the nid field is one. The result of the query is formed as an array, each value of which will be an object of the stdClass class and will contain the values of the selected records. You can also add fields in the query using the addField() method. By default, the equality() method is used in the condition() method, but you can use other operators, passing the operator as the third parameter.


  2. Selection of one value

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> addField ('nfd', 'title');
        
        $query-> condition ('nfd.nid', 1);
        
        $result = $query-> execute` () -> fetchField ();
        

The $result variable will contain the direct value of the title field. If there are several fields in the result record, the first one will be used. In the case of multiple entries, the first field of the first record will be used.


  3. Sampling of the first record

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> fields ('nfd', ['nid', 'title']);
        
        $query-> condition ('nfd.type', 'article');
        
        $result = $query-> execute`() -> fetchAssoc ();
        

The variable $result will contain the first record of the result of the sample as an associative array. To get an entry in the form of an object, you need to use the fetchObject () method.


  4. Selection of the first column as a simple array

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> addField ('nfd', 'title');
        
        $query-> condition ('nfd.type', 'article');
        
        $result = $query-> execute`() -> fetchCol ();
        

The variable $result will contain a one-dimensional array containing the values of the title field of all selected records.


  5. Combining the tables in the sample

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> addField ('nfd', 'title');
        
        $query-> addField ('ufd', 'name');
        
        $query-> join ('users_field_data', 'ufd', 'ufd.uid = nfd.uid');
        
        $query-> condition ('nfd.type', 'article');
        
        $result = $query-> execute`() -> fetchAll ();
        

You can also join tables in a query using innerJoin () methods (the join () method -is a synonym for this method) and leftJoin ().


  6.Selecting a certain range of records

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> fields ('nfd', ['nid', 'title']);
        
        $query-> condition ('nfd.type', 'article');
        
        $query-> range (0, 10);
        
        $result = $query-> execute`() -> fetchAll ();
        

Using the range() method, you can control the range of selectable records. The method has two parameters: the first is the start position of the range, the second is the number of selectable records from the beginning of the range.


  7. Using the OR conditions in the sample

        $condition_or = new \ Drupal \ Core \ Database \ Query \ Condition ('OR');
        
        $condition_or-> condition ('nfd.nid', 5);
        
        $condition_or-> condition ('nfd.nid', 7);
        
        
        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> fields ('nfd', ['nid', 'title']);
        
        $query-> condition ($ condition_or);
        
        $result = $query-> execute`() -> fetchAll ();
        

In the variable $result there will be two entries - for a node with nid 5 and for a node with nid 7.


  8. Counting the number of records in the sample

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> fields ('nfd', ['nid', 'title']);
        
        $query-> condition ('nfd.type', 'article');
        
        $result = $query-> countQuery () -> execute`() -> fetchField ();
        

  9. Checking values for NULL

        $query = \ Drupal :: database () -> select ('example', 'e');
        
        $query-> fields ('e');
        
        $query-> isNull ('e.field_null');
        
        $query-> isNotNull ('e.field_not_null');
        
        $result = $query-> execute`() -> fetchAll ();
        

  10. Application of complex expressions in the sample

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> fields ('nfd', ['nid', 'title']);
        
        $query-> addExpression ("DATE_FORMAT (FROM_UNIXTIME (nfd.created), '% e% b%
        Y')", 'node_created');
        
        $result = $query-> execute`() -> fetchAll ();
        

  11. Grouping of sampling records

         $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
         
         $query-> addField ('nfd', 'type');
         
         $query-> addExpression ('COUNT (*)', 'count');
         
         $query-> groupBy ('nfd.type');
         
         $result = $query-> execute`() -> fetchAll ();
         

  12. Applying complex conditions in a query

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> fields ('nfd', ['nid', 'title', 'type']);
        
        $query-> where ('DAY (FROM_UNIXTIME (nfd.created)) =: day', [': day' => 7]);
        
        $result = $query-> execute`() -> fetchAll ();
        

I note that the where() method can be used not only in the context of SELECT queries. For example, it can be used in an UPDATE or DELETE request.


  13. Sort selected records

        $query = \ Drupal :: database () -> select ('node_field_data', 'nfd');
        
        $query-> fields ('nfd', ['nid', 'title', 'type']);
        
        $query-> orderBy ('nfd.title');
        
        $result = $query-> execute`() -> fetchAll ();
        

Sorting direction can be set by means of the second parameter of the groupBy() method, which by default is "ASC". You can make a random sort using the orderRandom() method.


Insert and edit

In the case where you need to add or modify data in a specific table, it is better to refer to the Drupal API (if the table belongs to the kernel) or the API of the contra module (if the table belongs to the contra module). However, if you create your own module that operates on its own tables, you will need to describe the addition and modification operations manually. You can retrieve the UPDATE query object using the update() method from the connection object, and the INSERT query object with the insert() method. Similar to the previous section, consider the main examples of queries:


• Update records

        $query = \ Drupal :: database () -> update ('example');
        
        $query-> fields ([
        
        'field_1' => $ value_1,
        
        'field_2' => $ value_2,
        
        'field_3' => $ value_3,
        
        ]);
        
        $query-> condition ('field_4', $ value_4);
        
        $query-> execute `();
        

As a result of this code, fields field_1, field_2 and field_3 will be updated in the example table (they will get the values $value_1, $value_2 and $value_3, respectively) for records in which field_4 is equal to $value_4.


• Using complex expressions when updating

        $query = \ Drupal :: database () -> update ('example');
        
        $query-> expression ('field_1', 'field_1 +: amount', [': amount' => 100]);
        
        $query-> expression ('field_3', 'field_2');
        
        $query-> execute`();
        

If you need to apply a complex expression to update the data, you should use the expression() method. It contains three parameters - an updated field, an expression, arguments for the expression. In this example, for all records, the numeric field field_1 will be incremented by 100, and field_3 will receive values from field_2.


• Adding one record

        $query = \ Drupal :: database () -> insert ('example');
        
        $query-> fields ([
        
        'field_1' => $ value_1,
        
        'field_2' => $ value_2,
        
        'field_3' => $ value_3,
        
        ]);
        
        $query-> execute`();
        

It's worth noting that the fields() method can take one or two parameters. If the first parameter is an associative array, then the array keys must correspond to the fields of the table, and the values to the added values. In this case, the second parameter is omitted. If the first parameter is a normal array, then it corresponds to the fields of the table and you need to pass the second parameter, which must contain the added values in the order corresponding to the order of the fields in the first parameter. If the second parameter is omitted, the values to be added must be passed based on the values() method.


• Adding multiple entries

        $values = [
        
        [$value_1, $value_2, $value_3],
        
        [$value_4, $value_5, $value_6],
        
        [$value_7, $value_8, $value_9],
        
        ];
        
        $query = \ Drupal :: database () -> insert ('example');
        
        $query-> fields (['field_1', 'field_2', 'field_3']);
        
        foreach ($values as $record) {
        
        $query-> values ($record);
        
        }
      
        $query-> execute`();
        

• Add or update depending on the availability of the record

        $query = \ Drupal :: database () -> upsert ('example');
        
        $query-> fields (['field_id', 'field_1']);
        
        $query-> values ([$ id, $value_1]);
        
        $query-> key ('field_id');
        
        $query-> execute`();
        

Often it happens that, depending on the context, you either need to update the record, or add a new one. The implementation of a preliminary SELECT request for checking the presence of a record with a further conditional construction is a bad tone. In Drupal 8 there is a UPSERT request object for this (Drupal 7, by the way, there is no such type of query). You can get this object using the upsert() method. The UPSERT-request object must contain the key field, which will be used to check the existence of the record. The field must be unique within the table. You can add a key field by calling the key() method.

Concerning the update and addition of records, it is worthwhile to say that the execute`() method in the case of the update operation returns the number of updated records, and in the case of the addition operation, the ID of the added record, provided that one record was added. If multiple records were added, the returned identifier is not defined.

Delete

Sometimes you need to delete entries. In the case of module or kernel tables, you should also use only the appropriate API. Well, to delete data from your tables safely use the DELETE-request object, which, as you probably guessed, can be obtained using the delete() method.

        $query = \ Drupal :: database () -> delete ('example');
        
        $query-> condition ('field', $ value);
        
        $query-> execute`();
        

The construction of the conditions for the delete request is identical to the construction for sampling and change requests (you can use NULL checking, complex expressions, etc.), and the execute`() method returns the number of deleted records.

Conclusion

In general, the structure of database query building in Drupal 8 did not change much with Drupal 7. However, some nice and convenient things appeared (for example, UPSERT request). By the way, when you can`t compose the required query based on the object model, you can always execute a direct query to the database using the query() method of the connection object at your own risk (if you are a bad SQL-box). The method as input parameters can take a string with a query to the database.

Quick Free Quote
We respect your privacy. NO SPAM No selling your personal data.
We will respond to your query & collect further details within 24 hours. Guaranteed!

Support

We are friendly people who love to talk. So go ahead and contact us.

Technologies

Awards & Certification