So, i have a working solution:
Bare in mind, while I am not too much of a PHP novice, I am still learning OOP. I have also only been using vBulletin for about 3 weeks.
PHP Code:
public function getVisibileRelation($userID){
$userInfo = $this->vb_auth->is_logged_in_as($userID);
// returns primary user group, and other member groups
if($userInfo['membergroupids'] != ''):
$memberGroupsStr = $userInfo['usergroupid'].','.$userInfo['membergroupids'];
else:
$memberGroupsStr = $userInfo['usergroupid'];
endif;
$allAttach = array();
$visNodeArrToStr = array();
$visNodeArr = array();
//Based on membergroups + usergroups, find category channels, where user has visible / editable permissions -- I will obviously need to revisit my forumpermissions column once I solidify my Channel / Category structure.
$visibleNodes = $this->db->query("select permissionid, forumpermissions, TABLEPREFIX_permission.nodeid, TABLEPREFIX_node.title, TABLEPREFIX_channel.category from TABLEPREFIX_permission
left join TABLEPREFIX_channel on (TABLEPREFIX_permission.nodeid = TABLEPREFIX_channel.nodeid)
left join TABLEPREFIX_node on (TABLEPREFIX_node.nodeid = TABLEPREFIX_channel.nodeid)
where (TABLEPREFIX_permission.groupid in ($memberGroupsStr)) and (TABLEPREFIX_node.title is not null) and (TABLEPREFIX_channel.category = 0) and (
TABLEPREFIX_permission.forumpermissions = 569347 or
TABLEPREFIX_permission.forumpermissions like '5%'
)")->result_array();
//)
$visibleNodesQuery = $this->db->last_query();
$allAttachments = $this->db->query("select TABLEPREFIX_node.nodeid, TABLEPREFIX_node.userid, TABLEPREFIX_filedata.filedataid
from TABLEPREFIX_node inner join TABLEPREFIX_attach on (TABLEPREFIX_node.nodeid = TABLEPREFIX_attach.nodeid )
left join TABLEPREFIX_filedata on (TABLEPREFIX_attach.filedataid = TABLEPREFIX_filedata.filedataid)")->result_array();
foreach ($allAttachments as $row):
$allAttach[] = $row['nodeid'];
endforeach;
$allAttachStr = implode(',',$allAttach);
foreach ($visibleNodes as $row):
$visNodeArrToStr[] = $row['nodeid'];
$visNodeArr[] = array(
'nodeid' => $row['nodeid'],
'title' => $row['title']
);
endforeach;
$visNodeStr = implode(',',$visNodeArrToStr);
//TABLEPREFIX_attach_keyword is a self made table where I assign keywords to uploaded documents
$visibleAttachments = $this->db->query ("
select t1.nodeid as t1nodeid, t2.nodeid as t2nodeid, t3.nodeid as channelid,
t4.nodeid as categoryid, t5.nodeid AS postnodeid, t6.nodeid AS attachNodeId,
t5.userid, TABLEPREFIX_filedata.filedataid, visible,
username, counter, filename,
dateline, keywords
from TABLEPREFIX_node AS t1
left join TABLEPREFIX_node AS t2 ON t2.parentid = t1.nodeid
left join TABLEPREFIX_node AS t3 ON t3.parentid = t2.nodeid
left join TABLEPREFIX_node AS t4 ON t4.parentid = t3.nodeid
left join TABLEPREFIX_node AS t5 ON t5.parentid = t4.nodeid
left join TABLEPREFIX_node AS t6 ON t6.parentid = t5.nodeid
left join TABLEPREFIX_attach on (t6.nodeid = TABLEPREFIX_attach.nodeid )
left join TABLEPREFIX_filedata on (TABLEPREFIX_attach.filedataid = TABLEPREFIX_filedata.filedataid)
left join TABLEPREFIX_user on (TABLEPREFIX_user.userid = t6.userid )
left join TABLEPREFIX_attach_keyword on (TABLEPREFIX_attach.filedataid = TABLEPREFIX_attach_keyword.filedataid)
where (
t1.nodeid in ({$visNodeStr}) or
t2.nodeid in ({$visNodeStr}) or
t3.nodeid in ({$visNodeStr}) or
t4.nodeid in ({$visNodeStr}) or
t5.nodeid in ({$visNodeStr})
)
and
t6.nodeid in ({$allAttachStr})")->result_array();
$attachQuery = $this->db->last_query();
foreach($visibleAttachments as $row) {
$attachment[] = array(
'nodeid' => $row['attachNodeId'],
'userid' => $row['userid'],
'username' => $row['username'],
'filedataid' => $row['filedataid'],
'visible' => $row['visible'],
'counter' => $row['counter'],
'dateline' => date('l, F jS', $row['dateline']),
'filename' => $row['filename'],
'keywords' => $row['keywords']
);
}
return $attachment;
}
It is definitly not stable, nor is it clean. I have thrown in some documentation, because this will not work out of the box for most people. I have a custom table I am referencing (_attach_keyword). Also, I am using the Code Igniter framework to assist with Queries (as well as other MVC aspects). This is the model I use to fetch visible files.
If anyone else is facing a similar situation, feel free to try and figure out my code. If you take out the join for _attach_keywords, it should work rather easily.
More Information