Select data from database

Question

For the filter block I need to get the data from database. I have three tables "node", "node__filed_country" and "taxonomy_index". So I need to pull only those taxonomies which are realted to the nodes, which field_country is equal to three particular countries. So I need to provide two joins.

$query = $this->connection->select('node', 'node');
$query->join('node__field_country', $nfc, "node.nid=$nfc.entity_id and ($nfc.field_country_target_id=1 or $nfc.field_country_target_id=2 or $nfc.field_country_target_id=3)");
$query->join('taxonomy_index', $id, "$id.nid = node.nid AND $id.tid = $term->tid AND $id.status = 1 ");
$query->execute()->fetchField();

But something doesnt work in this code. Could you please help me?

Answer 1

You try code

$term_id = 1;
  $query = $this->connection->select('node', 'node');
  $query->join('node__field_country', "nfc", "node.nid=nfc.entity_id and (nfc.field_country_target_id=1 or nfc.field_country_target_id=2 or nfc.field_country_target_id=3)");
  $query->join('taxonomy_index', "tid", "tid.nid = node.nid AND tid.tid =".$term_id." AND tid.status = 1 ");
  $query->execute()->fetchField();

Answer 2

  $query = $this->connection->select('node', 'node');
  $query->join('node__field_country', $nfc, "node.nid=$nfc.entity_id");
  $query->join('taxonomy_index', $id, "$id.nid = node.nid");
  $or_condition = $query->orConditionGroup()
    ->condition("$nfc.field_country_target_id", '1')
    ->condition("$nfc.field_country_target_id", '2')
    ->condition("$nfc.field_country_target_id", '3');
  $and_condition = $query->andConditionGroup()
    ->condition("$id.tid", "$term->tid")
    ->condition("$id.status", '1');
  $query->condition($or_condition);
  $query->condition($and_condition);
  $query->execute()->fetchField();

You have to use or and and conditions separately and append it to query that you are building.