After a long time I finally found a solution to do that and it's a little bit complicated but it works.
- 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();
}
- 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");
}
- 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'], []);
}
}
}
- 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'
)
- 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