How to create a virtual field based on another hasMany table

Question

This is a very similar question to this one, but with a different relationship between the tables.

I have 3 tables: users, notifications, and user_notifications. user_notifications is a join table between users and notifications, and represents an instance of a notification having been sent to a user.

The relations are as follows:

User => hasMany   => UserNotification
UserNotification => belong to => User, Notification  
Notification => hasMany => UserNotification

The columns are as follows: User => id, name
UserNotification => id, user_id, notification_id Notification => id, message

I wanted to create a virtual field called Notification.users that simply held a string list of all the users that had been sent that particular notification, for example:

User => id = 1, name = Bob
User => id = 2, name = Bill

UserNotification => id = 1, user_id = 1, notification_id = 1
UserNotification => id = 2, user_id = 2, notification_id = 1

Notification => id = 1, message = "Hello World!"

So notification 1, "Hello World!" has been sent to users 1 and 2, Bob and Bill. Therefore, the virtual field Notification.users contains a comma-separated list of those two names, and I see:

Notification => id = 1, message = "Hello World!", users = "Bob,Bill"

score 0 · Answer 1 · answered Jul 14 '14 at 15:17

The code that ended up working for me is this:

class Notification extends AppModel
{
    public $virtualFields = array(
        "users" => "SELECT string_agg(users.name, ',') FROM users where users.id IN (SELECT user_notifications.user_id FROM user_notifications where user_notifications.notification_id = Notification.id)"
);

string_agg() aggregates the selected columns into a string, using the optional delimiter provided. One problem I ran into at first was the user of CakePHP aliases ("User", "UserNotification") for the other tables; this lead to "table not provided" errors, so I used the actual database table names. By default, values are fed to string_agg() in no particular order, and this can lead to a somewhat jumbled string. string_agg() supports an order by clause to solve this problem, as follows (note the lack of a comma between the delimiter, ',', and the order by clause):

class Notification extends AppModel
{
    public $virtualFields = array(
        "users" => "SELECT string_agg(users.name, ',' ORDER BY users.name) FROM users where users.id IN (SELECT user_notifications.user_id FROM user_notifications where user_notifications.notification_id = Notification.id)"
);

This works and seems like the simplest solution to me. Is there a more elegant or more correct one?

How to create a virtual field based on another hasMany table

1 Answers1