[Catalyst]

Came up against this problem while working with the vBulletin database. It has to do with permissions data and makes an interesting coding challenge. I'm part way through my own solution, but I think it would be fun to see what everyone comes up with.

Here's the details:

A user is a member of one usergroup. For the challenge the usergroupid = 2.

Each usergroup has a default forumpermissions value. The default forumpermissions for this usergroup is 766463

The forums table has a list of the 19 different forums, each has a forumid and possibly a parent forum's id.

The forumpermission table has entries for forumid, usergroupid, forumpermissions. This table only has entries for forums that 1) aren't using the permission of their parent or 2) not using the default permission.

The results of a SQL join filtered on usergroupid=2 are in the attached Excel sheet. The challenge is to write code that will use this data in some way (from the database, array, object collection, etc) to determine the correct forumpermissions value for all the blanks.

An example of the parent child hierarchy and what results should look like is:

ID #10 > #8 > -1 = default of 766463
ID #21 > #20 = 766455
ID #22 = 766447
ID #18 > #17 = 0

Look forward to seeing the most efficient (ie, least number of SQL calls and loops) solution one of you can come up with. If you need data in other formats or have questions post back. Any language is fine, ASP, PHP, Java, etc.

Attached File(s)

•  vbusers.zip (3.85K)
  Number of downloads: 1

[Catalyst]

No takers? I've got a picture of a gauntlet somewhere around here that I can throw down...

I got it working shortly after posting this, just curious to see what other people do and it's an interesting exercise in coding.

[Karl Buckland]

I'd like to take this up, I'm just too busy right now!

[smoseley]

Catalyst, on Feb 22 2007, 05:43 PM, said:

Look forward to seeing the most efficient (ie, least number of SQL calls and loops) solution one of you can come up with. If you need data in other formats or have questions post back. Any language is fine, ASP, PHP, Java, etc.

You can do it all in one query, as follows:

$default_value = "whatever the default value is";
$usergroup = 2

$query = "SELECT
    f1.forumid,
    f1.parentid,
    IF(f1.parentid = -1, IFNULL(fp1.forumpermission, $default_value), IFNULL(fp2.forumpermission, $default_value)) AS forumpermission
FROM forums AS f1
    LEFT JOIN forumpermission AS fp1 ON fp1.forumid = f1.forumid AND usergroup = $usergroup
    LEFT JOIN forums AS f2 ON f1.parentid = f2.forumid
    LEFT JOIN forumpermission AS fp2 ON fp2.forumid = f2.forumid AND usergroup = $usergroup
WHERE 
GROUP BY f1.forumid";

This will export your whole forum list with the correct permissions. It will only work for one level of heirarchy, though. If you add a 3rd level, you'll have to add more joins. It won't be an expensive query, because forum and permissions tables are usually very small.

There may be some bugs, as I haven't tested it (I don't have vBulletin), but if you mysqldump your forums and forumpermission tables here, I can debug it.

This post has been edited by smoseley: 17 January 2008 - 05:47 PM

[smoseley]

So I assume pretty much no one comes to the Databases section?

[marcamos]

smoseley, on Jan 21 2008, 10:16 AM, said:

So I assume pretty much no one comes to the Databases section?

We've got a rather small amount of active members and only a few of them are database geeks, so unfortunately, the traffic here is rather minimal.

[Catalyst]

Yep, in fact I'm going to have to go back and look to see what my solution to that was...

[haku]

I'd like to be more of a database geek