How to filter single field in a view

Question

I am running Drupal 7 with the latest version of the Views module.

I have a content type called "School," which has an entity reference field for "classroom." The "classroom" content type has a radio button field for whether it has an assigned teacher.

I'd like my view to output a table which will list the total number of classrooms in each school (so school name in the first column, # of classrooms in the second column). After some messing around, I was able to get this working.

However, now I'd like to add a third column where I display the # of classrooms which have an assigned teacher. So if the classroom doesn't have a teacher assigned, it doesn't get included in the count. Anyone know how to accomplish this? It doesn't look like I can filter on individual fields...

Answer 1

If this were an SQL query your new column could be a subquery. I mean this new value of # of classrooms with assigned teachers has its own business logic, seperate from the list of data your already returning.

You can accomplish this using Views Field View. This lets you embed a view result -- per row of your existing view. From the project page:

This module allows you to embed a view as a field in a view. A new field handler is made available, so this can also be used in area (header/footer/empty) handlers as well as rows.

This view handler can accept arguments from fields of the parent view using tokens and pass them into the child view for each row. Raw or rendered token values can be used, as well as static values.

It's highly recommended to use this module in conjunction with views caching. Views Content cache and Cache Actions are good ways of caching views.

So:

1. Make a new view; called for instance with the human name of "sum rooms assigned teachers" (the machine name will be sum_rooms_assigned_teachers).
2. For this View simply use display mode of Show Fields; and include like the NID as a field; Under advanced settings enable Aggregation for the View. For the NID value use the Count aggregation.
3. Add a contextual filter to this view based on the school name.
4. Save your view.

Then in your parent view:

1. After installing Views Field View. Add a new Field your view of Global: View.
2. select the view named sum_rooms_assigned_teachers. Provide the contextual filter to the view of your school name or ID; it will look like [!field_school_entityreference_id] or something similiar.
3. Give this field whatever label you want.

Your main view should now show a room count. In the helper view you may want to deny access to anyone but the Administrator or other role -- as this is simply a helper view for this other view; and rather useless by itself.