EAV Multiple Joins

gistfile1.sql

SELECT o.object_id, o.object_uri, o.object_type,

#Activity attributes
MAX(IF(p.property_name = 'activity_published', v.value_data, null)) AS activity_published,
MAX(IF(p.property_name = 'activity_content', v.value_data, null)) AS activity_content,
MAX(IF(p.property_name = 'activity_summary', v.value_data, null)) AS activity_summary,
MAX(IF(p.property_name = 'activity_comment_status', v.value_data, null)) AS activity_comment_status,
MAX(IF(p.property_name = 'activity_parent', v.value_data, null)) AS activity_parent,
MAX(IF(p.property_name = 'activity_generator', v.value_data, null)) AS activity_generator,
MAX(IF(p.property_name = 'activity_provider', v.value_data, null)) AS activity_provider,
MAX(IF(p.property_name = 'activity_mentions', v.value_data, null)) AS activity_mentions,
MAX(IF(p.property_name = 'activity_actor', v.value_data, null)) AS activity_actor,
MAX(IF(p.property_name = 'activity_verb', v.value_data, null)) AS activity_verb,
MAX(IF(p.property_name = 'activity_geotags', v.value_data, null)) AS activity_geotags,
MAX(IF(p.property_name = 'activity_object', v.value_data, null)) AS activity_object,
MAX(IF(p.property_name = 'activity_target', v.value_data, null)) AS activity_target,
MAX(IF(p.property_name = 'activity_permissions', v.value_data, null)) AS activity_permissions,

#User attributes
MAX(IF(l.property_name = 'user_name_id', u.value_data, null)) AS activity_actor_user_name_id,
MAX(IF(l.property_name = 'user_first_name', u.value_data, null)) AS activity_actor_user_first_name,
MAX(IF(l.property_name = 'user_last_name', u.value_data, null)) AS activity_actor_user_last_name

#Get the activity table
FROM 5g5if_activity_property_values v
LEFT JOIN 5g5if_properties p ON p.property_id = v.property_id
LEFT JOIN 5g5if_objects o ON o.object_id=v.object_id

#Join the user table
LEFT JOIN 5g5if_objects q ON q.object_id=v.value_data AND p.property_name ='activity_actor'
LEFT JOIN 5g5if_user_property_values u ON u.object_id=q.object_id
LEFT JOIN 5g5if_properties l ON l.property_id = u.property_id


WHERE	o.object_type='activity'
GROUP BY o.object_id
ORDER BY o.object_updated_on DESC