Find visible Channels by Groupid?

[ericbdev]

Hello,
I am working on a custom back end that pulls a list of documents from vBulletin's database.
One intricate aspect of the vBulletin set up is that 90% of the channels are restricted to one User Groups's visiblity. Because of this, it would not work for my client to show a user documents which he can't see through the forum.

If that was unclear at all, here is an example:
UserA is in Channel1
UserB is in Channel1, Channel2.

UserB uploads Document9 in Channel2. UserA should not be able to see Document9.
UserA uploads Document8 in Channel1. UserA, UserB should be able to see Document 8.

Ideally, I would like a function that is able to match a User's Group to visible Channels, but I would settle for a list of nodeids that a user is able to access.

Does anyone have any ideas of how I might achieve this?

That or, is somone able to explain to me how I can extrapolate this information from the permission table? I see it has listed nodeid, and groupid, but I am unable to accurately determine the correlations.

PHP Code:

select TABLEPREFIX_node.nodeid,TABLEPREFIX_node.routeid,TABLEPREFIX_node.userid, TABLEPREFIX_node.title
from TABLEPREFIX_node left join TABLEPREFIX_permission on (TABLEPREFIX_node.nodeid = bfn_permission.nodeid )
where TABLEPREFIX_permission.groupid = 6
//groupid 6 should be admin, and therefore see everything 


(The above querie is what I had tried, but does not return to me enough information to get what I need)

I would appreciate any snippets, or advice anyone would have to offer!
Thanks

[ericbdev]

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.