Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

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

sampaulsampaul New
edited July 2013 in Vanilla 2.0 - 2.8

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");

«1

Comments

  • 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?

    grep is your friend.

    UnderDog
  • 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.

    UnderDog
  • x00x00 MVP
    edited July 2013

    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.

    grep is your friend.

    hgtonightUnderDog
  • x00x00 MVP
    edited July 2013

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

    grep is your friend.

    UnderDog
  • hgtonighthgtonight ∞ · New Moderator
    edited July 2013

    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.

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

    UnderDog
  • LincLinc Detroit Admin

    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.

    hgtonightUnderDog
  • 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_JR_J Ex-Fanboy Munich Admin

    @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
  • sampaulsampaul New
    edited August 2013

    @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_JR_J Ex-Fanboy Munich Admin

    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'
       ))   
    
    UnderDog
  • okay thank you for this information. and thanks also to those who replied. :-)

  • R_JR_J Ex-Fanboy Munich Admin

    @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

    UnderDog
  • 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.

    grep is your friend.

    sampaulUnderDog
  • is it not possible to use file_get_content?

  • 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

    grep is your friend.

  • R_JR_J Ex-Fanboy Munich Admin

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

  • 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?

  • @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.

    grep is your friend.

  • x00x00 MVP
    edited August 2013

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

    grep is your friend.

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

    grep is your friend.

    sampaulR_J
Sign In or Register to comment.