Find visible Channels by Groupid?

[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.