How to code a views filter to exclude parents that have children

Question

I am using the Project Modules.

Some projects have no tasks others can have one or more tasks. In SQL terminology this is a one-to-many relationship, I believe.

I think the generated SQL joins Tasks node type and Projects node type.

I am using grouping so that tasks belonging to the same project are grouped together and projects without tasks are also grouped together. The problem is that projects that have tasks appear twice--in the "header" above the tasks associated with that particular project AND in the list of projects without tasks. I want the project title to appear only in the header above the tasks associated with that particular project.

There is an Entity Reference that links tasks to projects utilizing the field_data_pmtask_pmparent table. I am not using this in my view.

How can I code a filter to omit projects that have tasks from the list of projects that have no tasks?

My View

Answer 1

The following is designed for Projects and Tasks, but would probably work for any Entity Relationship with optional children of a parent.

To create a view that lists all parent nodes (in this case Project content type) whether they have children (in this case Tasks) or not use the following methodology.

The output will be all Projects with Tasks listed below them if they have Tasks.

Create a View for Projects. The Format chosen was Table, but other options would probably work.

Table Settings should include Grouping field Nr. 1 set to Content: Title. Check all the boxes Hide Empty Column for each field.

In the Fields section of the View do not list fields that are in the Project data type. Use only those in the Task data type. The Project title will automatically be retrieved because of the relationship in the Task data type that points to the owning Project. Filter Criteria should be set to Content Type = Project.

Click Advanced on the View page then click Add (next to RELATIONSHIPS). Click the drop down box and select Entity Reference. Select "A bridge to the Content entity that is referencing Content via pmtask_parent". Type Tasks into the Identifier blank. This establishes a BACKWARD reference to Projects. Require this relationship should NOT be checked because there are Projects without Tasks. You must read the Entity References very carefully. Some are very similar to each other.

There appears to be no way to find a list of all Entity References in Drupal except to add an existing field to a content type (Manage Fields) and to click the drop-down arrow next to it to select a field. Entity References are preceded by the term Entity Reference.

From the database perspective, the pmtask_parent Entity Reference is an index (or pointer) to the parent database record. In this case, the Project. Traditionally relational databases would create separate tables for Tasks and Project, but Drupal does things differently with both stored in the node table. The field_data_pmtask_parent table serves as the linkage between the two node types.

Above from https://www.drupal.org/node/2533748.