How to count entity references from same field in different entity types?

Question

I have three entity types:

• Company
• Team (has field_company which is an entity reference to Company)
• Employee (also has field_company, and field_team which is an
  optional entity reference to Team)

The idea is that all Employees are at least part of a Company, and possibly part of a Team within that Company. Using the Reference Field Option Limit module I have limited the options shown for field_team to only show Teams for the selected Company.

So far so good.

Now I want to create a table view with the following columns:

• Company Name
• Team Count
• Employee Count

I have created two relationships to referencing entities for field_company, and created a type filter for their types (Employee and Team). The problem is that Companies with no Teams, or no Employees are filtered out.

Any help with solving this deceivingly simple problem would be greatly appreciated!

Answer 1

Firstly, create a users view of the format: table with aggregation options with the following fields: - Company ref. - Team ref. - User name. Then go into the table's settings and add a group and compress function to the first field, and count to the others. Optionally you can use the tally members function and you'll get team names with counts displayed between parenthesis.

Secondly,.. well this is simple but depends on how much you are comfortable with php.

1. Create a views_preprocess_view (either that or _views_pre_render) function in your theme or in a custom module.
2. Install and enable devel and use dsm() in your function to print the resulting array of your view.
3. Use entity field query to get a list of your companies.
4. Compare your views results array with the entity field query results to figure out what is missing from the list.
5. Add it to the view with 0 or dashes for counts.

Answer 2

You should use Aggregation in your view and no filter. Use columns nid only and aggregate the fields. Then group results by company first, then team. Tell me if you need further precisions.

Answer 3

There is only one field at play here, Company Reference. And you want the count per node type of items using that field to reference a company.

Very simple if the field for referencing the company were called different things e.g employee_company_reference and team_company_reference but not easy when they are the same as you can't filter for node type per column.

There is however ( as always ) a module for Drupal that counts the incoming references and they can be filtered by node type (with applied patch) then you just create a view of all companies and add the two reference count fields, one for company ref filtered by teams, one for company ref filtered by employee.

So either use separate fields for your references and create a normal aggregations -> count view. OR install and patch this module : https://www.drupal.org/node/2035601