XF 1.5 Select List of Private Forums from XF Database

[Dan Allen]

Is there a way to select a list from the database that shows all the forums that have been checked private in their node permissions? The picture below shows what I mean. I have looked at the xf_node table in mysql, but am unable to identify a field reflecting the state of this checkbox.

 

[Dan Allen]

Did I do something wrong? Is the question unclear? Is the question inappropriate?

What I am trying to figure out a sql query that can run can run against the xenforo database to get a list of forums that have the private box checked for node permissions. In pseudocode, the query would be something like this:

select node.title, node.node_id from xf_node
where the private checkbox is checked in forum_permissions.
Thank you for reading this question.

 

[Sim]

The problem is that these are set on a per-usergroup basis.

So it's not as simple as "which nodes have this checkbox checked?" ... it's actually "which nodes have this checkbox checked for usergroup X?"

It certainly won't be a trivial query - I had a quick look and couldn't work it out immediately, would have to dive into the code to work out how those permissions are stored, which unfortunately I don't have time to do right now.

 

[S Thomas]

So just changed a forum from public to private, this is the permission entry for that.

> -- Dumping data for table `xf_permission_entry_content`
> --
>
> INSERT INTO `xf_permission_entry_content` (`permission_entry_id`, `content_type`, `content_id`, `user_group_id`, `user_id`, `permission_g roup_id`, `permission_id`, `permission_value`, `permission_value_int`) VALUES
> (1, 0x6e6f6465, 2, 0, 0, 0x67656e6572616c, 0x766965774e6f6465, 'reset', 0);

Let's take that fun.
(1, 0x6e6f6465, 2, 0, 0, 0x67656e6572616c, 0x766965774e6f6465, 'reset', 0); =>
(1, 'node', 2, 0, 0, 'general', 'viewNode', 'reset', 0);

That's informative. We just need to search for permission entries which are generally resetting view permissions for nodes for all user groups.
So the query would be

SELECT content_id, title
FROM xf_permission_entry_content xp
LEFT JOIN xf_node xn ON (xp.content_id = xn.node_id)
WHERE content_type = 'node'
AND permission_group_id = 'general'
AND permission_id = 'viewNode'
AND permission_value = 'reset'
AND user_group_id = 0
AND user_id = 0

 

[Dan Allen]

I apologize for not being clear in the question I need answered.

So just changed a forum from public to private, this is the permission entry for that.


Let's take that fun.
(1, 0x6e6f6465, 2, 0, 0, 0x67656e6572616c, 0x766965774e6f6465, 'reset', 0); =>
(1, 'node', 2, 0, 0, 'general', 'viewNode', 'reset', 0);

That's informative. We just need to search for permission entries which are generally resetting view permissions for nodes for all user groups.
So the query would be

SELECT content_id, title
FROM xf_permission_entry_content xp
LEFT JOIN xf_node xn ON (xp.content_id = xn.node_id)
WHERE content_type = 'node'
AND permission_group_id = 'general'
AND permission_id = 'viewNode'
AND permission_value = 'reset'
AND user_group_id = 0
AND user_id = 0

That worked perfectly, thank you.

That would have been hard to work out. With this answer as a starting point, I dug into the source code, quickly finding that this would have been quite the challenge to figure out on my own. Fortunately, the query in this answer provides exactly what I need. I really appreciate it.

 

[Cosmacelf]

So just changed a forum from public to private, this is the permission entry for that.


Let's take that fun.
(1, 0x6e6f6465, 2, 0, 0, 0x67656e6572616c, 0x766965774e6f6465, 'reset', 0); =>
(1, 'node', 2, 0, 0, 'general', 'viewNode', 'reset', 0);

That's informative. We just need to search for permission entries which are generally resetting view permissions for nodes for all user groups.
So the query would be

SELECT content_id, title
FROM xf_permission_entry_content xp
LEFT JOIN xf_node xn ON (xp.content_id = xn.node_id)
WHERE content_type = 'node'
AND permission_group_id = 'general'
AND permission_id = 'viewNode'
AND permission_value = 'reset'
AND user_group_id = 0
AND user_id = 0

FWIW, this seems to work in v2.1 as well.

I was looking for the opposite in that I needed a query that would give me threads that were NOT in a private forum. I ended up with this code:

SELECT xf_post.message, xf_post.username, xf_thread.title AS thread, xf_node.title AS node, xf_post.post_id, xf_post.thread_id FROM xf_post 
    JOIN xf_thread ON xf_post.thread_id = xf_thread.thread_id 
    JOIN xf_node ON xf_thread.node_id = xf_node.node_id 
    WHERE xf_post.message_state = 'visible' AND
        NOT EXISTS (
            SELECT 1
            FROM xf_permission_entry_content
            WHERE
                xf_permission_entry_content.content_id = xf_node.node_id AND
                xf_permission_entry_content.user_id = 0 AND
                xf_permission_entry_content.user_group_id = 0 AND
                xf_permission_entry_content.permission_value = 'reset' AND
                xf_permission_entry_content.permission_id = 'viewNode' AND
                xf_permission_entry_content.permission_group_id = 'general' AND
                xf_permission_entry_content.content_type = 'node' 
        )

    ORDER BY xf_post.post_date DESC 
    LIMIT 5