Getting to this conversation late... fantastic SQL, but I don't think it solves the problem
- You are cheating a bit by having pre-fetched the ids for the context hierarchy
-- which I made easy on purpose with the context 'path' field.
- The cost of that UNION over the user table can be huge
- The rolecap resolution cannot be solved by a mere SUM/MULTIPLY unfortunately. The tricky nexted logic I wrote... well, I did try to resolve with simple adds and multiplications, but it's just not like that. Consider scenarios with various conflicting roles assigned at different context depths.
Petr's proposed simplification may help there, but still, the scenarios with various conflicting roles... I don't think they can be done in pure SQL.
And if they can, the SQL is perhaps so costly as to be frankly undesirable.
Here is something I would like to stress: the most important thing is to have
- SQL queries that are as efficient as possible across the many different use cases (many courses, many role assignments, many role overrides, many users) and various DBs. This is incredibly hard to test -- we took the leap with 1.9, found plenty of "bad" cases to fix, and have been polishing things quite a bit. (well, you guys have, I went olpc-way...)
- A _bound_ number of SQL queries regadless of the size of those variables above.
That the number is _bound_ and hopefully low does NOT mean that "1 query" is a good answer (or even the best!) to the problem.
So my question are:
- Is the 1-query solution correct?
- Is the 1-query solution faster / more efficient for all the scenarios?