MySQL query for all discussions except Categories where user has no permission.

sampaul

Hello, this is a very challenging problem for me. I've done integration before using vBulletin.
But in Vanilla, I couldn't figure out how to display on the main site, all Vanilla discussions except those in Categories where the current user's role has no permission.

To elaborate this scenario. I've created a Category called "Staff Section" where only the Admins and Moderators have access.
When the user is a moderator or admin, posts from the Staff Section will be listed in the homepage along with the rest of the discussions.
But if user belongs to the other user role e.g. Members or Guest. All discussions will be displayed except the contents from the "Staff Section".

What's the MySQL query that I should use to accomplish this?

Here's my query so far:

  
$DB->query("SELECT t.Type, t.DiscussionID, t.CountViews, t.CountComments, t.Body, t.Name,t.DateInserted, t.Sink,t.CategoryID,
            c.CategoryID,c.Name as category_name,c.UrlCode, x.PermissionID,
            m.member_id, m.username
            FROM discussion t
            INNER JOIN category c ON t.CategoryID=c.CategoryID 
            INNER JOIN usertable m ON t.InsertUserID=m.member_id
            LEFT OUTER JOIN permission x ON t.CategoryID=x.JunctionID AND x.RoleID=(SELECT RoleID from userrole WHERE userID='".$SESSION->$userid."') 
            WHERE t.Sink='0' AND PermissionID IS NULL ORDER BY t.DateInserted DESC");

x00

Sorry I'm missing the point, if they are not in the category permission, it will not be displayed. What is the point of the extra query.

Are you trying to display them outside of vanilla?

sampaul

yes outside of vanilla. i have a main site, and i'm using vanilla as my forum. when a user logged in at the main site, he will be logged in at vanilla too. user management is primarily handled by the main site. when user's group, pics, username, password, etc is change in the main site. the vanilla user's database is also updated.

the only problem left, i want to display all forum discussions in the main site particularly in the homepage. but it it must show only the discussions from the category where the current user has permission to view its content.

x00

When you mean all the discussion are you attempting to replicate the functionality of the forum, or are simply summarising a discussion list, so they can click on the links to take them to the forum?

The very simplest way tot do that is to use the basic api. Which take into consideration permissions on the same domain if called from the browser with a little ajax e.g.

• http://vanillaforums.org/categories.json/
• http://vanillaforums.org/discusions.json/

Just remember you still have to sanitise.

if you want to still do it like above you need to get to understand the difference between permissions, roles an users.

see dashboard/models/class.permissionmodel.php CachePermissions

then check the dashboard/models/class.usermodel.php DefinePermissions and GetSession

then look at library/core/class.session.php CheckPermission

it is used in vanilla/models/class.categorymodel.php JoinUserData

see the bit about PermsDiscussionsView etc.

with the JunctionTable and JunctionID.

You are probably best with two or three queries, get the permission for that user, an determine which categories, then fetch the discussions in those categories.

x00

if you are pulling raw data out of the database sanitize it for output.

hgtonight

Welcome to the community!

@sampaul said:
the only problem left, i want to display all forum discussions in the main site particularly in the homepage. but it it must show only the discussions from the category where the current user has permission to view its content.

Do you want to display this as a sidebar item?

What version of Vanilla are you running?

You can get the contents of any module using http://www.example.com/application/module/modulename^[1]. There is an addon I wrote called Latest Post List that wraps the discussion list into a module. Combined, this may do what you are looking for.

Linc

Use the "Debugger" addon. It shows all MySQL queries executed to build a page. If your permissions are already setup as described, you should be able to load the /discussions page and see the query it uses.

sampaul

Sorry for late reply guys. Yes, I want it to display on the sidebar of my main site. All posts from all categories that the current logged in user has permission to view and read. :-)
Only the posts. Which the user can click and will take him to the thread. Thanks

@lincoln, thanks for the response. I tried using the debugger plugin but couldn't see any queries executed. :-(

R_J

@sampaul: with the debugger plugin enabled, the admin sees all queries at the bottom of a screen. So if you surf to /discussions, you'll see all queries that has been made.

You'll see that there is no single query. It is done just as @x00 has advised: there are several queries that are used to built the discussion list. But you should really consider using the built in api: /discussions?DeliveryType=VIEW (see also this short explanation

sampaul

@R_J i must have forgotten to put something on the template file because the debugger plugin is already enabled yet I cannot see any queries at the bottom of the screen.

anyway yes that is what i'm looking for. can you please give me some examples on how to use it if i use JSON as deliverytype? Thank you very much in advance.

I didn't know that vanilla has this core functions. Wow this is awesome!

R_J

I was impressed, too and nope, I'm a JSON noob and cannot say much to that. But I'd recommend looking at the Vanilla sources for examples. I'm convinced you'll find some answers https://github.com/vanillaforums/Garden/search?l=php&q=json&ref=cmdform

The following SQL should give you all the categories that are allowed for the logged in user, I just wanted to answer your first question and was curious, too ;-)

SELECT
   c.CategoryID
   , c.Name
FROM
   V20188_Category c
WHERE
   (c.PermissionCategoryID = -1
   OR c.PermissionCategoryID IN (
      SELECT
         p.JunctionID
      FROM
         V20188_User u
         LEFT JOIN V20188_UserRole ur ON u.UserID = ur.UserID
         LEFT JOIN V20188_Role r ON ur.RoleID = r.RoleID
         LEFT JOIN V20188_Permission p ON r.RoleID = p.RoleID AND p.JunctionTable = 'Category'

      WHERE
         p.JunctionID IS NOT NULL
         AND p.JunctionID <> -1
         AND u.UserID = '5'
   ))   

sampaul

okay thank you for this information. and thanks also to those who replied. :-)

R_J

@sampaul, that's what I've found out: the JSON includes a data section with is a base64 packed version of the view you'll get when you use /discussions?DeliveryType=VIEW alone

You'll either have to parse or style that for your homepage. There seems to be no DeliveryType=RAW which would be nice for what you try to achieve

x00

JSON is not hard it is just an object notation or way of serialising data. It directly translates in to javacript object which it based around. jQuery make it a doodle.

You do need to sanitise data as ever.

sampaul

is it not possible to use file_get_content?

x00

if you wish to use the VIEW and client side only solution there is a jquery library for base64

https://github.com/carlo/jquery-base64

R_J

Is there a benefit of using JSON for such a scenario? The file size is bigger and the information is the same.

sampaul

Hello @x00 thanks I will try your suggestions.

Here's what I'm trying to achieve.
I plan to create a forum exclusive for forum staff (mod and admins).
I want the latest forum posts to be displayed on the sidebar of my main site (not vanilla).
If mod and admins posted on "Forum Staff Only".
Other usergroup shouldn't be able to see posts from that forum because they don't have permission.

I saw this on the documentation. It returns JSON data which I can parse using PHP http://vanillaforums.org/discussions.json
My question, is the role permission of viewing the forums will also apply on displaying latest posts?

x00

@R_J said:
Is there a benefit of using JSON for such a scenario? The file size is bigger and the information is the same.

Well it depend if you wan to do some formatting with the data and use a different template or just insert it naively.

x00

@sampaul said:
Hello x00 thanks I will try your suggestions.

I saw this on the documentation. It returns JSON data which I can parse using PHP http://vanillaforums.org/discussions.json
My question, is the role permission of viewing the forums will also apply on displaying latest posts?

if you are fetching it with curl (via the server) it won't have the same user session as the client. if you fetch using jquery/ajax via the client it will, and will follow the permission as they would normally access the forum.

This is presuming you have some SSO.

x00

You might check the wordpress pluign widgets, it is basically the same thing.

One advantage of using @R_J VIEW idea is the data is already sanitised. The other method it it is not, but you can simply strip tags.