Entity query for distinct field

Question

I have an entity line_item with the following fields:

• id
• uid (entity reference)
• product (entity reference)
• ...

I would like to get all distinct products (product ids) for a specific user.

I tried with entity query:

$query = $this->entity_query->get('line_item');
$query->condition('uid.entity.name', $username);
$entity_ids = $query->execute();

Unfortunately this results in all line_items from the given user, with multiple times the same product. But what I am interested in is just getting all different products for the given user.

Answer 1

I don't think it's possible to write a single entity query to accomplish this. You could take your $entity_ids result, use loadMultiple to load all the line_item entities, and then iterate through them to get all the products. Something like this:

$line_items = $this->entity_type_manager->getStorage('line_item')->loadMultiple($entity_ids);

$products = [];
foreach ($line_items as $line_item) {
  $product = $line_item->getProduct();
  if ($product && !isset($products[$product->id()])) {
    $products[$product->id() = $product;
  }
}

Another possibility would be to use the database service to construct a select query with actual database table names and fields (\Drupal\Core\Database\Connection).

See: https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Connection.php/function/Connection%3A%3Aselect/8.2.x

and for the list of methods you can use to construct the select: https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21SelectInterface.php/interface/SelectInterface/8.2.x