Discussion viewable only by certain group members

Yougeen

Hi guys,

I downloaded the groups and membership plugins and would like to extend their functionalty by creating a new plugin. The requirement is that when a moderator opens a new discussion, he should have a option (e.g. a drop down list) where he can select a certain group (which already have been created by the groups plugin). And after that this discussion should only be visible for the selected group. I know that this could also been solved by creating a new categorys and open a new discussion in that category where only the group (role) members have access. But this is not the purpose.

Because I'm not very experienced with vanilla plugin development, could someone help me with some input how I can solve this?

Thanks a lot in advance.

R_J

Could you please try that:

@R_J said:

$Sender->SQL->Join(
    'UserGroup ug', 'ug.GroupID = d.DiscussionGroupID AND ug.UserID = '.Gdn::Session->UserID.' OR d.DiscussionGroupID = 0'
    , 'right outer'
);

I think it must be a right outer join and you should include all DiscussionGroupID = 0 discussions (if they should not adhere to any restrictions)

R_J

Prima, freut mich

R_J

Take a look at some plugins and the tutorial to get started. In order to create such a group discussion, you'll have to have the permission to do that. Simply include something like 'RegisterPermissions' => array('Plugins.YourPluginName.Add'), to your plugin. Later on, when showing the "New Discussion" form, you can test for that permission like that:

If (!$Session->CheckPermission('Plugins.YourPluginName.Add')) {
    return;
}

Below that should be the code to show the group dropdown (maybe you can take that as an example: https://github.com/R-J/AddWPInfo/blob/master/class.addwpinfo.plugin.php#L48)
In order to find out "where" to hook into Vanilla, I'd recommend you install the plugin "Eventi"

The information for which groups a discussion is visible must be stored somewhere. I think the easiest would be to add a field to the Discussion table. That is normally done in the Setup function of a plugin (either by directly creating such a column or by calling a function called "Structure".

Group only discussions must be excluded on most views, so you would have to hook into some functions like that: https://github.com/R-J/ExcludeCategories/blob/master/class.excludecategories.plugin.php#L24-31
Add a check if discussions group permission is the same as the users group.

You should be aware that there will be inconsistencies, eg. for the number of discussions per category. That information is stored per category. In worst case there will be no visible discussion for a user in one category, but he can see the discussion count showing there are 1,2 or even twenty discussions in that category. That will look strange...

Yougeen

Hi R_J,

thanks for your reply which helps me to get started. I would try something out and maybe I'll get back to you later again.

R_J

Just post what you've got or where you get stuck and let's see how that problems could be solved.

Yougeen

Hi R_J,

now I could need some help from you

Things I've done so far:

• A dropdown list with all available groups is shown when a discussion will be created / edited.
• The ID of the group will be saved in the discussion table as "DiscussionGroup"

Right now I stuck at the "DiscussionModel_BeforeGet_Handler" function.

In this function I want to check if the user is permitted to see the discussion, if not I want to hide the discussion from the list.

My idea was to grab the current user id by (Gdn::Session()->UserID) and check if the User is inside the group by looking into the Usergroup table which contains two columns (UserID and GroupID) and compare it with the DiscussionGroupID from the Discussion table.

The first point where I stuck is when I try to catch the DiscussionGroupID from the Discussion table by $Sender->EventArguments['Discussion'], the return value is empty. But when I look into the database I see that a DiscussionGroupID is saved for certain discussions, so there have to be an ID. Do you know why its empty?

The second point is when I try to create a sql-statement like this one
$UserGroupID = Gdn::SQL()->Select('GroupID')->From('Usergroup')->Where('UserID', 1)->get();
it also fails and I receive the message that Column 'UserID' in where clause is ambiguous.

I would be happy if you could give me some ideas how I can solve this

Thank you!

R_J

You are catching the BeforeGet event and before you get anything, there couldn't be an EventArgument "Discussion"

Look at the function Get inside class.discussionmodel.php where that event is fired. You'll see that you have an event argument "Wheres". You can change that, but in order to achieve what you want, you'll need to use the WhereIn clause and you'll have to add it like that (untested, just to get you started):

public function DiscussionModel_BeforeGet_Handler($Sender) {
    // Get the current logged in user
    $User = Gdn::Session()->User;

    // I don't know the structure of the groups plugin, but the idea is to get the users group IDs into an array
    $UserGroupIDs = unserialize($User->GroupIDs);

    // add a restriction to the sql of the discussionmodel, so that only the allowed group discussions are fetched
    $Sender->SQL->WhereIn('d.DiscussionGroupID', $UserGroupIDs);
}

That will only give you discussions with any DiscussionGroupID, but not the discussions without a DiscussionGroupID. Take a look at /library/database/class.sqldriver.php if you want to try to build the sql by yourself, shout for help if not

R_J

@Yougeen said:
Hi R_J,

The second point is when I try to create a sql-statement like this one
$UserGroupID = Gdn::SQL()->Select('GroupID')->From('Usergroup')->Where('UserID', 1)->get();
it also fails and I receive the message that Column 'UserID' in where clause is ambiguous.

Don't know if you still need an answer for that one, but just for the sake of completeness: I don't know the answer, but I guess you can avoid that by writing $UserGroupID = Gdn::SQL()->Select('ug.GroupID')->From('Usergroup ug')->Where('ug.UserID', 1)->Get();

My guess would be that if you do a decho(Gdn::SQL()->GetSelect()); die; before that line, you would see a SQL statement that already contains a table which has the column UserID

Yougeen

Hi R_J,

the assignment between the UserID and the GroupID is defined in an external table named "usergroup" thats the reason why I need a SQL-request against the usergroup table.

I found something really strange. When I try out to execute the following code:

public function DiscussionModel_BeforeGet_Handler($Sender) {    
  $GroupID = $Sender->SQL->Select('ug.GroupID')->From('usergroup ug')->get();
}

I receive an error message Unknown column 'd.DiscussionID' in 'on clause'

And the following SQL code output:

SELECT d.*,
       d.InsertUserID AS `FirstUserID`,
       d.DateInserted AS `FirstDate`,
       d.DateLastComment AS `LastDate`,
       d.LastCommentUserID AS `LastUserID`,
       w.UserID AS `WatchUserID`,
       w.DateLastViewed AS `DateLastViewed`,
       w.Dismissed AS `Dismissed`,
       w.Bookmarked AS `Bookmarked`,
       w.CountComments AS `CountCommentWatch`,
       ug.GroupID AS `GroupID`
FROM GDN_Discussion d,
     GDN_usergroup ug
LEFT JOIN GDN_UserDiscussion w ON d.DiscussionID = w.DiscussionID
AND w.UserID = 3 LIMIT 5

The problem with this statement is that SQL tries to join my GDN_usergroup table with the other tables and thats why the statement fails. If I manually change the statement into (compare lines 12+13):

SELECT d.*,
       d.InsertUserID AS `FirstUserID`,
       d.DateInserted AS `FirstDate`,
       d.DateLastComment AS `LastDate`,
       d.LastCommentUserID AS `LastUserID`,
       w.UserID AS `WatchUserID`,
       w.DateLastViewed AS `DateLastViewed`,
       w.Dismissed AS `Dismissed`,
       w.Bookmarked AS `Bookmarked`,
       w.CountComments AS `CountCommentWatch`,
       ug.GroupID AS `GroupID`
FROM GDN_usergroup ug,
     GDN_Discussion d
LEFT JOIN GDN_UserDiscussion w ON d.DiscussionID = w.DiscussionID
AND w.UserID = 3 LIMIT 5

Then it works properly. How can I solve this problem within my function?

Thank you for your help!

R_J

If you join the table usergroup by the group id and add a where user = current user to it, you should get all discussion for the current users group id

$Sender->SQL->Join('UserGroup ug', 'ug.GroupID = d.DiscussionGroupID', 'left outer');
$Sender->SQL->Where('ug.UserID', Gdn::Session->UserID);

Or even better:

$Sender->SQL->Join(
    'UserGroup ug', 'ug.GroupID = d.DiscussionGroupID AND ug.UserID = '.Gdn::Session->UserID
    , 'left outer'
);

Yougeen

Hi R_J,

thanks again for your input. I still stuck on the SQL-Query because I cannot hide the discussions for the user.

First of all, your SQL Code from above returns the following Query Statement:

SELECT d.*,
       d.InsertUserID AS `FirstUserID`,
       d.DateInserted AS `FirstDate`,
       d.DateLastComment AS `LastDate`,
       d.LastCommentUserID AS `LastUserID`,
       w.UserID AS `WatchUserID`,
       w.DateLastViewed AS `DateLastViewed`,
       w.Dismissed AS `Dismissed`,
       w.Bookmarked AS `Bookmarked`,
       w.CountComments AS `CountCommentWatch`
FROM GDN_Discussion d
LEFT JOIN GDN_UserDiscussion w ON d.DiscussionID = w.DiscussionID
AND w.UserID = 3
LEFT OUTER JOIN GDN_UserGroup ug ON ug.GroupID = d.DiscussionGroup
AND ug.UserID = 3

The result on the database looks like this:

Here you can see that the user should not see the discussionID 14.

And my Usergroup Table:

At this point I tried to write some different queries but I was not able to hide explicit the discussion where the user have not permission. As I'm not a SQL expert, i hope that you can help me here. Thanks alot !

R_J

Could you please try that:

@R_J said:

$Sender->SQL->Join(
    'UserGroup ug', 'ug.GroupID = d.DiscussionGroupID AND ug.UserID = '.Gdn::Session->UserID.' OR d.DiscussionGroupID = 0'
    , 'right outer'
);

I think it must be a right outer join and you should include all DiscussionGroupID = 0 discussions (if they should not adhere to any restrictions)

Yougeen

@R_J said:
Could you please try that:

I think it must be a right outer join and you should include all DiscussionGroupID = 0 discussions (if they should not adhere to any restrictions)

Thanks a lot R_J, that was the answer. I had to add a "group by discussion id" and then I received my results

R_J

Prima, freut mich