How to choose between classic SQL query and Drupal 8 technic?

Question

I have a custom content entity (amap_distributions_inscriptions) linked to 2 entity_reference 'datedistributionid' (which is basically the date of distribution plus other fields) and 'amapienid' (which is mainly the people who can attend to the distribution).

I want to create a form allowing people to enroll to the distribution, i.e. choose a date among all possible and their role.

In the past (a module I wrote for Drupal 6), I had a db_query joining the three tables, sorting by date, name of the people, etc. I processed the result of the query with a while ($record = db_fetch_object($result)) to build a tabular form with all dates and already enrolled people and so on.

What would be the "Drupal 8" way to do that?

I started with a

$storage = \Drupal::entityManager()->getStorage('amap_distributions_inscriptions');  
$ids = \Drupal::entityQuery('amap_distributions_inscriptions')  
  ->execute();  
$inscriptions = $storage->loadMultiple($ids);  
foreach ($inscriptions as $id=>$inscription) {my code here}  

but I'm struggling with retrieving 'date distribution' and 'amapien', then sorting the results before starting to build the form rows...

Any guidance?

Trying to use @ssibal's advice, I assume I should write something like

$database = \Drupal::database();
$result = $database
  ->select('amap_distributions_dates', ???)
  ->fields('ddate', 'iid', 'pid', 'irole', 'nom', 'prenom')
  ->condition('dnumber', 0, '>')
  ->execute()

My D6 query ('amap_distributions_dates', 'amap_distributions_inscriptions' and 'association_personnes' being the 3 tables):

$query = "
SELECT 
    amap_distributions_dates.ddate, 
    iid, 
    amap_distributions_inscriptions.pid, 
    irole,
    nom, 
    prenom
FROM 
    amap_distributions_dates 
    LEFT JOIN amap_distributions_inscriptions ON amap_distributions_inscriptions.ddate = amap_distributions_dates.ddate
    LEFT JOIN association_personnes ON association_personnes.pid = amap_distributions_inscriptions.pid 
WHERE
    dnumber > 0
ORDER BY 
    amap_distributions_dates.ddate ASC, 
    iid ASC,
    irole ASC, 
    nom ASC, 
    prenom ASC
";

Answer 1

Finally, this worked:

$database = \Drupal::database();
$query = $database->select('amap_distributions_dates', 'amdd');
$query->leftJoin('amap_distributions_inscriptions', 'amdi', 'amdi.datedistributionid = amdd.id');
$query->leftJoin('association_personne', 'ap', 'ap.id = amdi.amapienid');
$query->fields('amdd', ['id', 'datedistribution', 'nombreproduits'])
    ->fields('amdi', ['id', 'datedistributionid', 'amapienid', 'role'])
    ->fields('ap', ['id', 'nom', 'prenom'])
    ->condition('nombreproduits', 0, '>')
    ->orderBy('datedistribution', 'ASC')
    ->orderBy('role', 'ASC')
    ->orderBy('nom', 'ASC')
    ->orderBy('prenom', 'ASC');
$results = $query->execute()->fetchAllAssoc('id');
foreach ($results as $row) { some code here }

Answer 2

If you want more sophisticated query solution go for "database" service and the "select" method. You can have a much faster query and exactly what you need. It's the D8 equivalent of D7 db_select().

Here is an example, how to add conditions, joins, and select fields. You should all the time check your database structure via phpmyadmin for example:

    $query = $this->database()->select('taxonomy_term_data', 'term');
    $query->innerJoin('taxonomy_term_field_data', 'term_data', 'term_data.tid = term.tid');
    $query->innerJoin('taxonomy_term__field_dst', 'name', 'name.entity_id = term.tid');
    $query->condition('term.vid', 'my_vocab', '=');
    $query->condition('term_data.name', $variable, '=');
    $query->condition('name.langcode', $this->getParameter('language'));
    $query->addField('name', 'field_destination_name_value', 'name');
    $result = $query->execute();