CODEWITHSUNDEEP
phpmysqljoingreatest-n-per-group
ShoeLace1291
ShoeLace1291

Reputation: 4698

How do I use MySQL Joins to select forum boards that users are allowed to read?

I have three primary tables in this scenario: categories, boards, and permissions. The categories and boards tables are pretty self-explanatory. The permissions table contains rows that will determine whether or not the user is allowed to either read threads within a board, create threads within a board, reply to threads within a board, or moderate threads and posts within a board. In the table, the user's ability for each action are indicated by a 0(off, or false) or 1(on, or true). These are based on the member group that the user falls in.

What I would like to do is create a query that selects categories. It should only select categories that have boards that the user can read. Basically, if a category doesn't have any boards that the user can read, it won't be selected. If the category has even only one board that the user can read, it will be selected.

The next query is to select the boards within a certain category that a user can read. Based on the same principals as stated above. What's the point of displaying a board that a user cannot read only for them to find that out after they have already clicked the link?

I've never really created a complicated query like this on my own, so I wouldn't even know where to start. I would really appreciate it if you could help me out.

Update

So, I decided to give it a go, and this is what I came up with:

$query = "
            SELECT
                b.category_id, b.board_id, b.position,
                p.group_id, p.board_id, p.read
            FROM forum_boards AS b
                INNER JOIN forum_permissions AS p ON (
                        p.board_id = b.board_id,
                        p.group_id = 1,
                        p.read = 1
                )
            WHERE b.category_id = ".$category_id."
            ORDER BY b.position";

Note that this is the query to get boards within a certain category that the user can read. It is currently returning the error below, and I can't figure out why.

Operand should contain 1 column(s)

Categories Table Boards Table Permissions Table

Upvotes: 4

Views: 359

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

As Niko has already provide the example query using joins so i am providing the subquery version so that make some difference with the already provided answer

The fisrt subquery is what you ask for What I would like to do is create a query that selects categories. It should only select categories that have boards that the user can read. Basically, if a category doesn't have any boards that the user can read, it won't be selected. If the category has even only one board that the user can read

I have added the condition status=1 that your forum_boards are active and i just put the group_id=1 so you have to provide the right user group id 

SELECT * FROM forum_categories WHERE category_id IN (
SELECT category_id FROM forum_boards WHERE `status`=1 AND board_id IN (
SELECT board_id FROM forum_permissions WHERE group_id=1 AND `read`=1) )

Here is your second query you ask for the same requirement but this time to select boards but within a category , again i added the condition status=1 set according to your needs and i don't know about the category_id so i just put it as category_id=1 you must take care of it to put the right category id

SELECT *  FROM forum_boards WHERE `status`=1 AND board_id IN (
SELECT board_id FROM forum_permissions WHERE group_id=1 AND `read`=1) AND category_id=1

Hope it makes sense

Upvotes: 5

Niko
Niko

Reputation: 26730

This problem sounds complicated but it actually isn't. You can select all categories based on a given group ID by selecting from "permissions" and joining in "boards" based on the given board_id and then further joining in "categories" based on the category_id of the boards. This would return each category as often as it is indirectly listed in "permissions". To avoid that, you usually group by the category ID so that each distinct category is only included once in the result set. To exclude categories with no readable boards, you now select only those boards via the "permissions" table:

SELECT c.*
FROM permissions AS p
JOIN boards AS b ON b.board_id = p.board_id
JOIN categories AS c ON c.category_id = b.category_id
WHERE p.group_id = 100 AND p.can_read = 1
GROUP BY c.category_id

I've renamed your column "read" into "can_read" in this example. See this fiddle for a demo: http://sqlfiddle.com/#!2/07e74/1

For "boards in a specific category", the whole thing is even simpler because you don't need to group by anything - just join "permissions" with "boards" and apply restrictions:

SELECT b.*
FROM permissions AS p
JOIN boards AS b ON b.board_id = p.board_id
WHERE p.group_id = 100 AND p.can_read = 1 AND b.category_id = 1

Demo: http://sqlfiddle.com/#!2/07e74/2

Upvotes: 3

Related Questions