up vote1down votefavorite
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 ";
share|improve this question
up vote1down voteaccepted

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 }
share|improve this answer
up vote0down vote

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();
share|improve this answer
    
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. – gbmapoJul 1 '16 at 9:35

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