The relevant table is shown below:
+----+------------+---------+------------+-----------------+----------------------+ | id | account_id | user_id | project_id | title | SUBSTR(body, 1, 20) | +----+------------+---------+------------+-----------------+----------------------+ | 1 | 1 | 1 | 1 | Damians message | Should only be visib | | 2 | 2 | 2 | 2 | Roms message | Should only be visib | | 3 | 1 | 1 | 3 | Branding Info | A message in a share | +----+------------+---------+------------+-----------------+----------------------+
The first message should be visible to the first user, the second message to the second, and then thrid to both. This works fine when viewing just a single project which is a leaf node in the hierachical representation of the project structure, but if viewing a project with one or more sub projects, the message from the sub project should also be displayed.
The contents of the project table is displayed below:
+----+-----+-----+-------------------+------------+-----------+ | id | lft | rgt | name | company_id | client_id | +----+-----+-----+-------------------+------------+-----------+ | 1 | 1 | 4 | Account root | 1 | 0 | | 2 | 1 | 2 | Account root | 2 | 0 | | 3 | 2 | 3 | Alumor rebranding | 1 | 2 | +----+-----+-----+-------------------+------------+-----------+
Projects 1 and 2 are top level projects which belong to users 1 and 2 respectively. The 3rd project is a project where user 1 is the company and user 2 is the client. This means that user 1 “owns” the project. As a direct consequence of this, the project is positioned in the companies project hierachy and not the clients one.
To understand why this causes a problem, I’ll need to explain the way in which messages are retreived in a project with child projects.
Based on the above schema, we can see that each row in the messages table corresponds to the project it belongs to. So to find all the messages in a leaf project, we just need to do a where clause on this ID. If we have multiple projects, we need to look for messages in a range of project IDs, which can be done using a where in clause. This itself would be easy and would work, but we only want to search projects which are sub projects of the current one.
Given the project we’re in now, we can get the left and right values for the project. Any node with a left value between these 2 must be a child of that node, as thats the key principle MPTT works on. In order to limit it to projects in this account, we need to also add a where clause to check the account ID is the same as our account ID.
This works fine for the company account, so if user 1 views the messages sectiion when he’s in the top level account, he see’s the message in the sub project as expected. If we then do the same on the client side, we don’t get the message in the child project (but we do when we view messages in that project). This is because we use the clients account ID as the where clause, and the nested project doesn’t match the clients hierachy.
An easy way to fix this would be to get the companies ID and use that in the where clause. The problem with this is, the company may have other nested projects in that hierachy that the client shouldn’t be seeing. A way around this would be to get the nested hierachy based on the company ID and then do an intersection of the projects IDs with the clients projects
Update
This successfully resolved the problem (but I need to do some checks once I have a more complicated project hierachy to make sure there are no nasty suprises).
However this led to the realisation of another (more serious?) problem. As the project is stored in the companies hierachy, when in the client mode, and in the top level project, it’s classed as a leaf node, as it’s left and right vales are one apart.
In order to resolve this, I’ll have to get all the projects a user is one, get all the projects below the given project in the companies project hierachy, and then intersect the results to get the projets the client should actually see, but I’ll save that for another entry.
Update 2
It turns out this isn’t actually a problem. Only the company needs to see the account in relation to the hierachy, the client just needs to see the problem as is. So all I need too do, is determine if the user is in the company or client account on the project, and if they’re in the client account, just show messages relevant to that project, and if they’re in the company account, we need to check the project hierachy. The only special case for the client is for the account root project, which can easily be identified as it has a left node value of 1. In this case, we just pull back all the users projects.
I guess I don’t fully understand the problem as you explained it. From what I gathered though, your looking for some way to relate the message to a project or sub project and then be able to display the message dependent upon the current user’s role (as client or user of the development team).
If that is indeed the issue, the way I have handled things like that in the past is to nest items in the database by having a field that is a foreign key to its parent. Assign the message to the id of the sub project. Then you can display the message to only members of the team that have been assigned to that sub project and not the entire project. And for the other problem, just check what the user’s role is in the current project/sub project and display messages accordingly. Only the PM should be able to directly contact the customer and send them messages. The other users should only be able to send messages to others in the development team.
If I’m way off base on what the actual problem was, delete this
The problem isn’t about how to store the messages in relation to the project or sub project. Thats the easy part! At any time, you are within the context of a project (even when you’re not in a project, you’re in the “root”, which is itself a project) so when you post a message, it gets links to that project. At this stage, it doesn’t matter if the project is a root project, sub project or sub sub project.
As for the rest of your comment, I think you where right, you don’t quite get what the problem was. It wasn’t about keeping messages from users that where part of a project but not sub project, but instead the client not seeing messages from sub projects which they are on.
Lets say you’re a client, and have 2 projects, project 1 and project 1a (project 1a being a sub project of project 1). When you’re in the context project 1a, you see all messages in 1a as expected. When you’re in project 1, you see all messages in project 1 but nothing from project 1a.
This is because project 1a is a child of project 1, but this relation is defined as part of the companies project hierachy, as they own the project (I use MPTT instead of the adjacency list model as you suggested, read this post for why). So when you go into project 1 as a client, it is considered as a leaf node, as the left and right node values are one apart. As it’s modelled as a leaf node in this context, it doesn’t even go looking for messages in sub projects, as there aren’t any (despite knowing the user is on the sub project as it’s modelled in the project_users table).
Does that make a little more sense? Remember, this “problem” is now all irrelevant after a bit of thinking about how it should be viewed from the customers point of view… they don’t care about sub projects.
You also made a good point at the end about should all the team members within the company see messages from the client and be able to send messages to them or just the project manager? Surely in some cases, you’d want a select few users with these permissions?
Well, just from what I’ve been told, you would not want the team members interacting directly with the customer unless there is something that needs to be directly conveyed to the team member, and the PM is struggling to get the message across. Otherwise, the customer could be asking the team member to add functionality or make changes that haven’t been approved by the PM. Then it has to be reiterated back to the PM first, etc. As I said, this is just what I’ve learned and been told, but the PM should always been somewhat of a mediator between the team members and the customer. It causes problems any other way.
As always, good point!
I think I may have been sucked into the mentality being 37Signals basecamp which I’ve used a few times in the past. They keep everything wide open, so everyone sees everything by default.
Thinking the best approach is to only let the PM or other selected members send and receive messages from the client, and they can then make these visible to the whole team (in either read ir write mode)
Although if you’re using messages for changes, then they should be made via the tickets system. (Although there’s nothing to stop a client sneaing a change request in a message)