Queries

Grouping query conditions + Using SQL LIKE operator

// We use orConditionGroup() to groupe multiple Conditions
// We use escapeLike() to use the LIKE operator

Database::setActiveConnection('external');
$db = Database::getConnection();

$query = $db->select('TABLEPREFIX.TABLE_NAME', 'ALIAS');
$query->fields('ALIAS', ['FIELDS']);
 $group = $query->orConditionGroup()
            ->condition('FIELD_NAME', "%" . $db->escapeLike($palabra_clave) . "%", 'LIKE')
            ->condition('FIELD_NAME', "%" . $db->escapeLike($palabra_clave) . "%", 'LIKE');
$query->condition($group);
$results = $pager->execute()->fetchAll();
Database::setActiveConnection();

Query Pagination

Using PagerSelectExtender

// We use PagerSelectExtender to limit the what we get on our page
// $pager should be passed later to the twig file as a variable

use Drupal\Core\Database\Query\PagerSelectExtender;

Database::setActiveConnection('external');
$db = Database::getConnection();

$query = $db->select('TABLEPREFIX.TABLE_NAME', 'ALIAS');
$query->fields('ALIAS', ['FIELDS']);
 $group = $query->orConditionGroup()
            ->condition('FIELD_NAME', "%" . $db->escapeLike($palabra_clave) . "%", 'LIKE')
            ->condition('FIELD_NAME', "%" . $db->escapeLike($palabra_clave) . "%", 'LIKE');
$query->condition($group);
$pager = $query->extend(PagerSelectExtender::class)->limit(5);
$results = $pager->execute()->fetchAll();
Database::setActiveConnection();

Stored Procedures

Execute a Stored Procedure

// Executing the query --> RETURNS A BOOLEAN
$results = Database::getConnection()->query("EXEC SCHEMA.SP_SP_NAME", $options)->execute();

Execute a Stored Procedure and get data as a return

Controller or Class File

  // We don't use execute(), we directly run fetchAssoc() to get an associative array or fetchObject() to get a an object
  $sp = Database::getConnection()->query("EXECUTE DRU.SP_NAME", $options)->fetchAssoc();

  // OR
  // $sp = Database::getConnection()->query("EXECUTE DRU.SP_NAME", $options)->fetchObject();

  return [
    '#sp' => $sp,
  ]

Twig File

// The data is passed from the controller to the twig file via the render array
// Since it's an associative array, we need the key and value
{% for key,value in sp %}
    Key :
    {{ key }}
    Value :
    {{ value }}
{% endfor %}

MODULE.module

function module_theme(){
    return [
    'variables' => [
      '#sp' => $sp,
    ],
  ];
}