Count how many children have a reference to the parent

Question

Explanations:

1) I have a node "parent"

2) I have 2 nodes "child"

3) Each child has a reference field (filled with a reference to the node "parent")

Now, I'm trying to create a list with the Views module.

First problem: This list is a list of "parent" nodes AND should include a column with the following result: COUNT(child with a reference to the parent).

Second problem: The count result should include only published children.

Example 1 with 2 children published (2 children have a reference to the parent):

Title of parent node | 2

---

Example 2 with 1 child published (2 children have a reference to the parent):

Title of parent node | 1

---

Sorry if it's not totally clear, it's hard to explain

Above all of this, if the result could be sortable it will be a good news but I don't think it will be possible...

Answer 1

Second problem: the views results will only include published entities if you add the filter: Published (= True).

It's a lot more difficult if you want unpublished node to show in view results, but not be counted.

First problem:
The easiest way to solve this is by using the Summary option of a Contextual filter.

List the Child nodes in your Views, then add the Contextual filter for the Entity reference field to the Parent, and set it to display the Summary (adjust Validation and other options to your needs).

The Summary will list the Parents links, with the Children count next to them. When you click through the Parent links you will see the original Views Children list (but only for that clicked-on parent).

Don't forget the base path if the Views Display you're working on isn't the first Page Display in your Views.

The Summary also gives you the option to sort according to the Count :)

Your Contextual filter options should looks something like this:

When the filter value is NOT in the URL
Display a summary
Sort order: Descending
Sort by: Number of records
Format: List
Display record count with link

̣̣̣̣̣̣̣_______________

Alternativelly you could count with the Agreggation option (under Advanced settings). You would count the children title field for example, their Entity IDs, while grouping the Parent reference field.

Answer 2

After a long time I finally found a solution to do that and it's a little bit complicated but it works.

1. I added a new custom field (which returns the count of children published with a parent reference)

  public function render(ResultRow $values)
  {
    /** @var Node $educational_institution */
    $educational_institution = $values->_entity;
    $educational_institution_id = $educational_institution->id();

    $connection = Database::getConnection();
    $query = $connection->select('node__field_educational_institution', 'n_educational_institution');
    $query->join('node_field_data ', 'n_data', 'n_educational_institution.entity_id = n_data.nid');
    $query->condition('n_educational_institution.field_educational_institution_target_id', $educational_institution_id);
    $query->condition('n_data.status', TRUE);
    $query->fields('n_educational_institution', ['entity_id']);

    return $query->countQuery()->execute()->fetchField();
  }

2. I added the possibility to sort by clicking the column header (in the same file)

  function clickSort($order) {
    /* Count educational_institution_requests whose have "published" status
     * - node_field_data.nid -> used by the "view" for listing educational institution entities
     * - node_field_data as n_data -> define an alias to avoid using the table from the main query
    */
    $subquery = '(
      SELECT COUNT(*)
      FROM node__field_educational_institution, node_field_data as n_data
      WHERE node__field_educational_institution.field_educational_institution_target_id = node_field_data.nid
      AND n_data.nid = node__field_educational_institution.entity_id
      AND n_data.status = 1
    )';
    $this->query->addOrderBy(NULL, $subquery, $order, "educational_institution_request_count");
  }

3. I added a custom filter

class EducationalRequestCount extends NumericFilter {

  /**
   * Helper function that builds the query.
   */
  public function query() {
    $this->ensureMyTable();
    /* Count educational_institution_requests whose have "published" status
     * - node_field_data.nid -> used by the "view" for listing educational institution entities
     * - node_field_data as n_data -> define an alias to avoid using the table from the main query
    */
    $subquery = '(
      SELECT COUNT(*)
      FROM node__field_educational_institution, node_field_data as n_data
      WHERE node__field_educational_institution.field_educational_institution_target_id = node_field_data.nid
      AND n_data.nid = node__field_educational_institution.entity_id
      AND n_data.status = 1
    )';
    $this->query->addField(NULL, $subquery, 'educational_institution_request_count');
    if(in_array($this->operator, ['between', 'not between'])) {
      $this->query->addHavingExpression(0, 'educational_institution_request_count ' . $this->operator . ' ' . $this->value['min'] . ' AND ' . $this->value['max'], []);
    } else {
      $this->query->addHavingExpression(0, 'educational_institution_request_count ' . $this->operator . ' ' . $this->value['value'], []);
    }
  }

}

4. Don't forget to declare the custom filter in the hook_views_data

    'filter' => array(
      'title' => t('Title'),
      'help' => t('Help'),
      'id' => 'educational_institution_request_count'
    )

5. My custom filter extends NumericFilter, we need an extra step

5.1. Create config/schema/my_module.views.schema.yml file

5.2. Add this

views.filter.educational_institution_request_count:
  type: views_filter
  label: 'educational_institution_request_count numeric'
views.filter_value.educational_institution_request_count:
  type: views.filter_value.numeric
  label: 'educational_institution_request_count numeric'

Reference: https://drupal.stackexchange.com/a/238894/84848