Data from a table with variable name

[mithras]

I want a RBAC system with seperated objects in different domains. Each plugin has its own domain: for domain "blog" there are objects "article", "reaction", "tag" and "setting" and so on.

 

I'm using MySQL 4.0.21 and created these tables:

users
id (int)
name (varchar)

groups
id (int)
name (varchar)

user_in_groups
group_id (int)
user_id (int)

actions
id (int)
name (varchar)

domains
id (int)
name (varchar)
object_table (varchar)

permissions
id (int)
domain_id (int)
object_id (int)
group_id (int)
action_id (int)

In the field domains.object_table is for each domain the table listed which contains the objects for that domain. The table looks like this:

object_table_X
id (int)
name (varchar)

Now I'm trying to get all the objects and their actions for a specific user. Getting domain, action and object_id is no problem:

SELECT a.name action, d.name domain, p.object_id object
FROM permissions p
LEFT JOIN actions a ON p.action_id = a.id
LEFT JOIN groups g ON p.group_id = g.id
LEFT JOIN domains d ON p.domain_id = d.id
LEFT JOIN user_in_groups u ON u.group_id = g.id
WHERE u.user_id =1

 

But I don't want the object_id but the object name. I tried this query:

SELECT o.name object, d.name domain, a.name action
FROM permissions p
LEFT JOIN actions a 
  ON p.action_id = a.id
LEFT JOIN groups g 
  ON p.group_id = g.id
LEFT JOIN domains d 
  ON p.domain_id = d.id
LEFT JOIN user_in_groups u 
  ON u.group_id = g.id
LEFT JOIN (SELECT object_table FROM domains d2 WHERE d2.id = p.object_id) o 
  ON p.object_id = o.id
WHERE u.user_id =1

But it failed with the following error:

Unknown table 'p' in where clause 

I thought the p in the subquery is allowed; it's also mentioned at this manual page: http://dev.mysql.com/doc/refman/5.0/en/comparisons-using-subqueries.html.

 

Does somebody know how to get the object name? For me, the difficult part is getting the table name from a field in the same query as where you use this table name.

[fenway]

Why not just join the domains table again? Why the subquery?