I solved this - it works but might be either brilliant or stupid. I added a new field called path. In that field I store the full path from the top level of the tree through all children to 'this' one - something like this:
- Top Level Department > Parent Department > Child Department > This Department I can now report the hierarchy by doing a straight sort on this field without doing any Tree stuff or parsing. I have a beforeSave function that identifies if the save is going to change the title field or the parent_id. If so, it sets a global variable. The afterSave function looks for that variable, and if found it clears the variable (to prevent an endless save loop), gets the full path from this node back to the root, implodes it with ' > ' and updates the 'path' field with a long string. It then finds all children of this node (between its lft and rght values) and updates those too. It works nicely and is quite snappy. I also built a temporary function to populate the file for migration. On Tuesday, 11 June 2013 16:02:01 UTC+1, Jeremy Burns wrote: > > I've got a multi-tenancy site that includes some tables that use the Tree > behaviour. By multi-tenancy I mean that several different clients all store > their data in the same database and they can only access and manage their > own data. > > For example, each client stores their departments in the 'departments' > table. Each department has a client_id field, as well as parent_id, lft and > rght. Each client will have one or more top level departments (where > parent_id is null and client_id = $theirClientId). > > My aim is to produce reports where the departments are ordered by parent > department name -> child department name down through the tree to whatever > level where the nodes at each level are also sorted. Ideally I'd user > Tree->reorder when departments are added or updated so they are stored in > the right order and I can simply sort by lft on find, but I'm finding that > performance is poor. This is partly because if a new top level department > is added I need to reorder where parent_id is null, and that impacts all > top level departments not just those belonging to this client. That can > trigger a lot of cascading updates. > > Is there a way to either: > 1) use Tree->reorder but pass in extra criteria (parent_id = null AND > client_id = 123) or > 2) Sort the data once it's been found - there plenty of examples using the > lft column but that isn't correct if the title field isn't also sorted > correctly when stored. > > If the answer is 1 (which sounds right) it can still trigger an update of > the complete tree if a new top level department is added that begins with > 'A', as all subsequent rows will have to be moved down. > > What's the recommended approach? > > -- Like Us on FaceBook https://www.facebook.com/CakePHP Find us on Twitter http://twitter.com/CakePHP --- You received this message because you are subscribed to the Google Groups "CakePHP" group. To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscr...@googlegroups.com. To post to this group, send email to cake-php@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
