Combining a tree structured Group model with its HABTM User model in one VIEW

Question

I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a Has-and-Belongs-to-Many (HABTM) relationship using the join-table groups_users

I want to create a view that creates a new tree which includes all the groups and all the users as children of the groups they belong to. The view should have the columns id, parent_id, model, foreign_key, lft and rght.

The id column should just be the Nth row and parent_id, lft and rght can't be just copied from the Group model, they will have to calculated for the tree of the view.

An example of what the view could look like:

| id | parent_id | model | foreign_key | lft | rght
----------------------------------------------------
|  1 |    NULL   | Group |      1      |  1  |  14  
|  2 |     1     | User  |      1      |  2  |   3  
|  3 |     2     | Group |      2      |  4  |   7  
|  4 |     3     | User  |      3      |  5  |   6  
|  5 |     4     | Group |      3      |  8  |  13  
|  6 |     5     | User  |      2      |  9  |  10  
|  7 |     5     | User  |      4      | 11  |  12  
|  8 |    NULL   | Group |      4      | 15  |  20  
|  9 |     8     | User  |      4      | 16  |  17  
| 10 |     8     | User  |      5      | 18  |  19  

How do I do this?

Can it be done with some super complex SELECT statement or do I need to use a procedure?

I'm not super skilled at MySQL; I can do simple SELECTs and JOINs, but this is way beyond my skill level.

Answer 1

This can't be done with a view.

Views in MySQL cannot reference variables. Try defining even a simple view that references a session variable, and there's no joy. Take a working query that references a variable and try creating a view using that query:

ERROR 1351 (HY000): View's SELECT contains a variable or parameter

Granted, there's a hack-around for this: you can call a stored function that you wrote, which returns the value of the variable you want to be accessible to the view. But, oops, we're already out of the scope of "view".

Additionally, a view, like a query, has no ability to iterate through rows and do something in "this" row because of what happened in "that" row.

Granted, again, there are hacks with variables in queries that can emulate some of that behavior but on the best days they can only look backwards and not forwards, and they don't always "see" the rows in the same sequence that the rows are returned.

I see three possible approaches to this, if SQL is where you really want to do it:

Option 1 involves a stored procedure and a temporary table. A stored procedure can iterate through rows with a cursor, and you'd need a temporary table since stored procedures don't have arrays or hashes. Iterate through your source data, either with a cursor or in a loop that starts by finding the top parent node and then each subsequent select based on what you've found so far... populating the temporary table as you go, taking precautions to avoid infinite loops caused by circular references in parent_id values (gotcha!), and then SELECT from your temporary table within the proc to return its contents as a result set to the client.

You might even end up with a second stored procedure that you call from the first one, which then recursively calls itself, to traverse your tree and build your nested set.

Option 2 involves insert, update, and delete triggers on the group and user tables, which would rebuild your MPTT structures every time any modification is done to group and user.

This would, in a sense, be the "most correct" way to do it, since your left and right ids would never be inconsistent with the underlying data... but as @dogmatic69 has pointed out, it's a very expensive operation... and not without its own snags, since you're limited to what you can to to table_x while you're inside a trigger on table_x.

On the other hand, with this option, when you needed to see the tree, the work to build it has already been done... so, much faster on SELECT.

Option 3 is to calculate the tree values with something other than SQL... so, really, I lied earlier, and there were only two SQL options I've come up with. Calculate it all in PHP, Perl, etc., and then populate the database with the values you calculated, which is how I generally do it. :) But I get away with it, because the parent/child relationships in my databases are not updated by any other process. If they were, I'd be staring down the barrel of Option 2.