0

I would like to know how to correct a view query, when Drupal goes nuts ?

I'm using a view that should list media by categories (taxonomy term). This view is called by a node, that held a reference to the same categories (same taxonomy term) that the medias I wish to display.

I need to order thos categories according to the delta field on my node object.

So in my views I did a join from the media taxonomy field to the taxonomy terms. Then, I have a join on my any node that hold the taxonomy field. I filter on the nid through contextual filters.

Until then all is good.

However, if I add the sort Critera on the delta value of categorie, the views module add another (unnecessaray) join on the table holding data for my taxonomy field on my node. This confuses my database and then I get a scalar product as results (every media is sent back, for each existing delta line).

Exemple of the query (I get two joins on node__field_categorie) :

    SELECT DISTINCT field_categorie_taxonomy_term_field_data__node__field_categorie.delta AS field_categorie_taxonomy_term_field_data__node__field_catego, media_field_data.mid AS mid, taxonomy_term_field_data_media__field_standard_termref_1.tid AS taxonomy_term_field_data_media__field_standard_termref_1_tid, field_categorie_taxonomy_term_field_data.nid AS field_categorie_taxonomy_term_field_data_nid
FROM
{media_field_data} media_field_data
LEFT JOIN {media__field_standard_termref_1} media__field_standard_termref_1 ON media_field_data.mid = media__field_standard_termref_1.entity_id AND media__field_standard_termref_1.deleted = '0'
INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data_media__field_standard_termref_1 ON media__field_standard_termref_1.field_standard_termref_1_target_id = taxonomy_term_field_data_media__field_standard_termref_1.tid
INNER JOIN {**node__field_categorie**} taxonomy_term_field_data_media__field_standard_termref_1__node__field_categorie ON taxonomy_term_field_data_media__field_standard_termref_1.tid = taxonomy_term_field_data_media__field_standard_termref_1__node__field_categorie.field_categorie_target_id AND taxonomy_term_field_data_media__field_standard_termref_1__node__field_categorie.deleted = '0'
INNER JOIN {node_field_data} field_categorie_taxonomy_term_field_data ON taxonomy_term_field_data_media__field_standard_termref_1__node__field_categorie.entity_id = field_categorie_taxonomy_term_field_data.nid
LEFT JOIN {media__field_domain} media__field_domain ON media_field_data.mid = media__field_domain.entity_id AND media__field_domain.deleted = '0' AND (media__field_domain.langcode = media_field_data.langcode OR media__field_domain.bundle = 'useful_document')
LEFT JOIN {**node__field_categorie**} field_categorie_taxonomy_term_field_data__node__field_categorie ON field_categorie_taxonomy_term_field_data.nid = field_categorie_taxonomy_term_field_data__node__field_categorie.entity_id AND field_categorie_taxonomy_term_field_data__node__field_categorie.deleted = '0'
WHERE ((media_field_data.status = '1') AND (media_field_data.bundle IN ('useful_document'))) AND (media__field_domain.field_domain_target_id LIKE 'intranet_adapei69' ESCAPE '\\')
ORDER BY field_categorie_taxonomy_term_field_data__node__field_catego ASC

Exemple of my view, so you can better understand.

enter image description here

How can I correct such a query ? I'm using Drupal 8.7.12 and can't upgrade for the moment. I guess the easier way around this would be to be able to add an order by clause using the column made available by my first join on : node__field_categorie

I looked into query_alter and views_query_alter hooks, but it seems it is no longer possible to add an order by clause there (or I tried the wrong way). Any ideas ?

1

Finally, I found how to do it. I don't understand why I got lots of errors previously.

 function sully_ad_import_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
if ($view->id() == 'document_list' && in_array($view->current_display,array('block_1','block_3'))) {

  $query->addField(
    'taxonomy_term_field_data_media__field_standard_termref_1__node__field_categorie',
    'delta',
    'delta'
  );
  $query->addOrderBy(
    NULL,
    NULL,
    'asc',
    'delta'
  );

}

}

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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