Yes, you're absolutely right!
In the process of verifying your suggestion, I also discovered an easy way to fake an OUTER JOIN in a postgresql VIEW. As you know, outer joins in postgresql have to be done as some sort of UNION statement, and, as it turns out, according to error messages I got, the use of unions in postgresql views is not supported yet.
In order to make /admin/gp stuff work, by way of the example that comes with Open ACS, you'll see that for each table you want to do general permissions on, you need a "denormalized view" of that table. The example that comes pre-packaged with Open ACS is the fs_versions table, which has a corresponding view called FS_VERSIONS_DENORM_VIEW. In the Open ACS 3.2.4 model, the table used to do the lookup for that "denormalized view" name (via the table that corresponds to it) is general_table_metadata.
So, in order to get any of the general permissions stuff in /admin/gp to work, it's necessary to:
- Create a new entry for the table and view in general_table_metadata.
- Create a view named like the "denorm_view_name" column in your new row in general_table_metadata.
(The convention is {TABLENAME}_DENORM_VIEW.)
In order to get the CHAT_ROOMS_DENORM_VIEW to work, I had to "fake" an outer join between chat_rooms.group_id and user_groups.group_id, since, in this view, I decided I wanted to display the group_name (or null, in the case of null group_id's). My way of doing this was to use "CASE", as follows, in the select statement:
case when chat_rooms.group_id is not null
then user_groups.group_name
else null end as group_name
and then to use the following join:
WHERE ((chat_rooms.group_id = user_groups.group_id) OR (chat_rooms.group_id is null AND user_groups.group_id = 1));
The filter on user_groups.group_id, in "
chat_rooms.group_id is null AND user_groups.group_id = 1", is required in order to "fake" the join; otherwise, you get much more data than you expect for the rows having null group_id's.
I wouldn't be surprised if this way of "faking" outer joins isn't more or less applicable across the board.
