I have the module installed and have set up activity publisher templates to record site activity for node creation, node updates, user creation and user profile updates. These all work fine and are recording activity for nodes and users. I've enabled the relationships for Activity:Node and Activity:User in order to filter out activity by blocked users and unpublished content - therein lies the problem.
When I set my filters to Node:Published (Yes) and User:Active (Yes) my view now filters out all user related content as the nid on the activity table is NULL for things like "UserX joined the site", "UserY updated their profile" etc.
The reason is that views is doing a LEFT JOIN on the node table and then checking node status <> 0. The full query being generated is:
SELECT activity.aid AS aid,
COALESCE(activity_personal_messages.message, activity_messages.message) AS activity_messages_message,
activity.created AS activity_created
FROM activity activity
LEFT JOIN node node_activity ON activity.nid = node_activity.nid
LEFT JOIN users users_activity ON activity.uid = users_activity.uid
INNER JOIN activity_targets activity_targets ON activity.aid = activity_targets.aid AND (activity_targets.uid = 0 AND activity_targets.language = '***ACTIVITY_LANGUAGE***')
INNER JOIN activity_messages activity_messages ON activity_targets.amid = activity_messages.amid
LEFT JOIN activity_targets activity_personal_targets ON activity.aid = activity_personal_targets.aid AND (activity_personal_targets.uid = ***CURRENT_USER*** AND activity_personal_targets.language = '***ACTIVITY_LANGUAGE***')
LEFT JOIN activity_messages activity_personal_messages ON activity_personal_targets.amid = activity_personal_messages.amid
WHERE (users_activity.status <> 0) AND (node_activity.status <> 0)
ORDER BY activity_created DESC
The important part being the WHERE clause, I need it to instead say:
WHERE (users_activity.status <> 0) AND (node_activity.status <> 0 OR node_activity.status IS NULL)
From looking at the current node filters in views I think I basically need to create a new filter like so:
/**
* Filter by published status
*/
class views_handler_filter_activity_node_status extends views_handler_filter {
function admin_summary() { }
function operator_form() { }
function query() {
$table = $this->ensure_my_table();
$this->query->add_where($this->options['group'], "$table.status <> 0 OR $table.status IS NULL");
}
}
Just wondering where the best place to put this is? Custom module? Patch activity? Patch views?
Any thoughts? For now I'll maybe try patching activity and see how I get on.
Comments
Comment #1
Scott Reynolds commentedYour right adding filters to will make the relationship 'required'. So what needs to be worked on is a Views patch that makes $query::add_where "unrequired relationship" aware.
Because this affects EVERY filter using an unrequired relationship.
Comment #2
sirkitree commentedPlease reopen if one of you plans to work on a patch for this. But I think Scott is saying that this is something that should be patched in Views. I can't find anything related myself so closing this for now.