i'm trying to develop a custom widget for fengoffice 2.3.2.1, i debuged the application for finding an appropriate way to create a query based on workspaces, tags and people (based on members entity).
Feng uses this pattern for filtering dashboards, tasks, documents and etc.
im looking for the best way for loading objects based on workspace, groups, user authorities(permissions and other people's task). i found below queries and i think this way for loading members for each tasks and objects is not the best way.
SELECT SQL_CALC_FOUND_ROWS * FROM fo_objects o
INNER JOIN fo_project_milestones e ON e.object_id = o.id
WHERE
o.id IN (
SELECT object_id FROM fo_sharing_table
WHERE group_id IN (
SELECT permission_group_id FROM fo_contact_permission_groups WHERE contact_id = 2
)
)
AND o.id IN (2,3,5,6,14,16,18,20,22,27,30,33,37,40,45,49,55,58,60,61,63,68,93,94,116,119,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,144,157,173,174,175,176,177,181,226,254,255,256,257)
AND o.object_type_id = 10
AND `o`.`trashed_on` = '0000-00-00 00:00:00' AND `o`.`archived_on` = '0000-00-00 00:00:00' AND `is_template` = false AND `completed_on` = '0000-00-00 00:00:00' AND (`due_date` >= '2013-11-02 00:00:00' AND `due_date` < '2013-11-16 23:59:59') AND `archived_on` = 0
And
SELECT SQL_CALC_FOUND_ROWS * FROM fo_objects o
INNER JOIN fo_project_tasks e ON e.object_id = o.id
WHERE
o.id IN (
SELECT object_id FROM fo_sharing_table
WHERE group_id IN (
SELECT permission_group_id FROM fo_contact_permission_groups WHERE contact_id = 2
)
)
AND o.id IN (2,3,5,6,14,16,18,20,22,27,30,33,37,40,45,49,55,58,60,61,63,68,93,94,116,119,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,144,157,173,174,175,176,177,181,226,254,255,256,257)
AND o.object_type_id = 5
AND `o`.`trashed_on` = '0000-00-00 00:00:00' AND `o`.`archived_on` = '0000-00-00 00:00:00' AND is_template = 0 AND `e`.`completed_by_id` = 0 AND `e`.`due_date` = '0000-00-00 00:00:00'
ORDER BY due_date ASC, priority ASC
LIMIT 0 , 6
As you can see in all of these queries accessible objects for members loads via IN Clause.
Could someone please inform me how can i load workspaces hierarchy or members access hierarchy.
Thanks in advance.