Joining and traversing models in ATK4

Question

I have a following query,

$users = $q->add('Model_User')
            ->join('profile.user_id', 'id')
            ->join('activity.profile_id', 'profile.user_id')
            ->addCondition('timestamp', '>=', date('Y-m-d'))
            ->addCondition('profile.isActive', true);
            ->addCondition('activity.isDelivered', false)
            ->addCondition('activity.priority', '>=', 2);

Now, I want to traverse all 'users' their 'profiles' and 'activity' associated with each profile. Relation between User & Profile is 1:n, relation between Profile and Activity is also 1:n.

Is it possible to get something like this?

foreach($users as $user) {
    foreach($user->profile as $profile) {
        foreach ($profile->activity as $activity) {
            //Some actions
        }
    }
}

Earlier I have used fetching association via ref() but this is slow when number of users are very huge and I don't want to have multiple queries on DB.

My current setup is,

$users = $q->add('Model_User')
            ->addCondition('timestamp', '>=', date('Y-m-d'));

foreach($users as $user) {
    $profiles = $users->ref('Profile', 'user_id');
    foreach(profiles as $profile) {
        if($profile['isActive']) {
            $activities = $profiles->ref('Activity', 'profile_id');
            foreach (activities as $activity) {
                if(!$activity['isDelivered'] && $activity['priority'] >= 2) {
                    //Some actions
                }
            }
        }
    }
}

Answer 1

Something like this should work:

class Model_User_ForMailing extends Model_User {
    function init() {
        parent::init();

        // join profile, add profile fields you need later
        $join_p = $this->join('profile');
        $join_p->addField('email');

        // join activity, add activity fields you need later
        $join_a = $join_p->join('activity');
        $join_a->addField('number','act_number');
        $join_a->addField('description','act_descr');

        // add conditions, important where you add them
        $this->addCondition('timestamp', '>=', date('Y-m-d'));
        $join_p->addCondition('isActive', true);
        $join_a->addCondition('isDelivered', false);
        $join_a->addCondition('priority', '>=', 2);
    }

    function actionSendMail() {
        $to = $this->get('email');
        $subject = 'New activity #' . $this->get('act_number');
        $message = 'You have new activity with this description: ' .
                       $this->get('act_descr');
        mail($to, $subject, $message);
    }
}

class mypage extends Page {
    function init() {
        parent::init();

        $m = $this->add('Model_User_ForMailing');
        foreach ($m as $junk) {
            $m->actionSendMail();
        }
    }
}

But this is completely untested example. I posted it here just to give you idea how to work with joins and extended models. In this case there should be only one SQL request and only one loop, because you actually need to loop by mails not by users/profiles/activities.