Missing assets in taxonomy term lists

Hmm ok... I see what's happening. It's a result of this recent feature: #2477409: Inactive assets

During that development, I added a filter to the Crops list to only include "active" assets in the total counts of plantings in each crop. Otherwise, you might see something like "Cherry tomatoes (200)" instead of "Cherry tomatoes (5)" (assuming you have 5 cherry tomato plantings that are active, and another 195 from previous years). Unfortunately, a side effect of that query filter is that it only shows crops with active plantings.

Adding this to the roadmap for beta6... I'll try to come up with a better solution.

Renaming this issue and moving it to the Farm Asset module because it affects more than just the Crops view. It also affects Animal Groups, Animal Types, and any other taxonomy term Views that join in farm assets that reference them.

I've almost got a solution worked out for this... but I'll post that in a separate comment.

Ok, so I figured out a pretty good way to fix this without having to change the end result of the Views that are affected.

Basically, the problem was that I was using a filter to remove any plantings that were not active. But that meant that if a crop didn't have any active plantings it wouldn't be displayed.

I examined the query being generated by the View (annotated below):

SELECT

# term id
taxonomy_term_data.tid AS tid, 

# parent term id
taxonomy_term_data_taxonomy_term_hierarchy.tid AS taxonomy_term_data_taxonomy_term_hierarchy_tid, 

# crop name
taxonomy_term_data.name AS taxonomy_term_data_name, 

# vocabulary id
taxonomy_term_data.vid AS taxonomy_term_data_vid, 

# vocabulary machine name
taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name, 

# crop family term id
field_data_field_farm_crop_family.field_farm_crop_family_tid AS field_data_field_farm_crop_family_field_farm_crop_family_tid, 

# count of plantings
COUNT(field_farm_crop_taxonomy_term_data.id) AS field_farm_crop_taxonomy_term_data_id

# base table = term data
FROM taxonomy_term_data taxonomy_term_data

# join in the term hierarchy table on term id
LEFT JOIN taxonomy_term_hierarchy taxonomy_term_hierarchy ON taxonomy_term_data.tid = taxonomy_term_hierarchy.tid

# join in the parent term via the hierarchy
LEFT JOIN taxonomy_term_data taxonomy_term_data_taxonomy_term_hierarchy ON taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid

# join in "crop" fields that reference the crop
LEFT JOIN field_data_field_farm_crop field_data_field_farm_crop ON taxonomy_term_data.tid = field_data_field_farm_crop.field_farm_crop_tid AND (field_data_field_farm_crop.entity_type = 'farm_asset' AND field_data_field_farm_crop.deleted = '0')

# join in the asset (planting) that has the field
LEFT JOIN farm_asset field_farm_crop_taxonomy_term_data ON field_data_field_farm_crop.entity_id = field_farm_crop_taxonomy_term_data.id

# join in the vocabulary
LEFT JOIN taxonomy_vocabulary taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid

# join in "crop family" fields that reference the crop
LEFT JOIN field_data_field_farm_crop_family field_data_field_farm_crop_family ON taxonomy_term_data.tid = field_data_field_farm_crop_family.entity_id AND (field_data_field_farm_crop_family.entity_type = 'taxonomy_term' AND field_data_field_farm_crop_family.deleted = '0')

WHERE 
(( 
  (taxonomy_vocabulary.machine_name IN  ('farm_crops')) 
  AND (field_farm_crop_taxonomy_term_data.active <> '0')
))

GROUP BY tid, taxonomy_term_data_taxonomy_term_hierarchy_tid, taxonomy_term_data_name, taxonomy_term_data_vid, taxonomy_vocabulary_machine_name, field_data_field_farm_crop_family_field_farm_crop_family_tid

ORDER BY taxonomy_term_data_name ASC

LIMIT 50 OFFSET 0

The part that was causing the issue was in the WHERE clause:

AND (field_farm_crop_taxonomy_term_data.active <> '0')

The BETTER way to achieve the same goal, without losing crops from the list is to add an additional condition to the 4th JOIN (where the farm_asset) table is joined in. The extra condition could be used to check for the "active" property on the assets, and only include active assets in the join. Like this:

# join in the asset (planting) that has the field
LEFT JOIN farm_asset field_farm_crop_taxonomy_term_data ON field_data_field_farm_crop.entity_id = field_farm_crop_taxonomy_term_data.id AND field_farm_crop_taxonomy_term_data.active = 1

Getting that extra "AND field_farm_crop_taxonomy_term_data.active = 1" in there is harder than it seems, however... I ended up having to create a new Views relationship handler that extends views_handler_relationship_entity_reverse. Inside that handler, I was able to use Views' "#join_extra" property to add the necessary condition.

All taxonomy term / farm asset reverse reference relationships are overridden with the new handler. The handler includes a checkbox option that let's you turn it on/off - in case you want to build a View that includes counts of inactive assets for example.

This will be included in beta6 which I'm rolling out shortly: #2548397: [META] FarmOS 7.x-1.0-beta6

I am in the process of updating all the various Views (in other modules). But the bug itself is fixed.

Moving old issues to the canonical farmOS project for easier searching. Apologies for email spam if anyone is following these. :-)