1

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
";

2 Answers 2

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 }
0

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();
1
  • Do you mean something like that: $database = \Drupal::database(); $result = $database->select('something')...? In that case, what would be the syntax to JOIN the 3 'tables' (entities)? I updated my original question with my Drupal6 query I try to adapt to Drupal8.
    – gbmapo
    Jul 1, 2016 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.