Converting a Views SQL query with INNER JOIN to an EntityQuery

Question

I'm trying to create a custom JSON:API resource that returns the entities that have been flagged by a user. (The Flag module does not offer a way to get this out of the box.)

I used Views to build a query to show all terms flagged by the user:

SELECT taxonomy_term_field_data.tid AS tid, flagging_taxonomy_term_field_data.id AS flagging_taxonomy_term_field_data_id
FROM
{taxonomy_term_field_data} taxonomy_term_field_data
INNER JOIN {flagging} flagging_taxonomy_term_field_data ON taxonomy_term_field_data.tid = flagging_taxonomy_term_field_data.entity_id AND (flagging_taxonomy_term_field_data.flag_id = 'MY_FLAG' AND flagging_taxonomy_term_field_data.uid = 'MY_UID')

Then, I installed the JSON:API Resources module and configured a custom resource.

However, JSON:API uses EntityQuery, not regular SQL queries.

So based on my very limited knowledge of SQL, I wrote a basic query to get all the terms:

    $query = $this->getEntityQuery('taxonomy_term')
      ->condition($entity_type->getKey('bundle'), 'MY_VOCABULARY');

Now I want to restrict this to the terms that have been flagged by the user. How do I implement the INNER JOIN in the Views query in EntityQuery?

Answer 1

You can try hook_query_TAG_alter

Sample code

$query = $this->getEntityQuery('taxonomy_term')
  ->condition($entity_type->getKey('bundle'), 'MY_VOCABULARY')->addTag('join_flaging');

/**
 * Implements hook_query_TAG_alter().
 */
function MYMODULE_query_join_flagging_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
  $query->addJoin("INNER","flagging","flg","taxonomy_term_field_data.tid = flg.entity_id AND (flg.flag_id='MY_FLAG' AND flg.uid='MY_UID')");
}

Answer 2

As the flagging_taxonomy_term is an entity, see https://git.drupalcode.org/project/flag/-/blob/8.x-4.x/src/Entity/Flagging.php, according to this change-record, JOINS are supported, for you like:

<?php
$query = Drupal::entityQuery('taxonomy_term')
  // $UPDATE: ->condition($entity_type->getKey('bundle'), 'MY_VOCABULARY') use vid as bundle:
  ->condition('vid', 'MY_VOCABULARY')
  ->condition('flagging.entity.flag_id', 'YOUR_FLAG')
  ->condition('flagging.entity.uid', 1);