How To Scope By Tertiary Relationship Condition?

I have 3 models:

  1. Customer

  2. CustomerLogin

  3. CustomerDeposit

They are related as follows:




## SQL schema

customer.id = customer_login.customer_id

customer.id = customer_deposit.customer_id



The "status" of a customer is determined by the "status" attribute of their login. So to retrieve all "active" customers, I do a query like this:




<?php

// Find all customers with active logins.

Customer::model()->with(array('login' =>array(

    'condition' =>'login.status = :customer_login_active',

    'params' =>array(':customer_login_active' =>CustomerLogin::STATUS_ACTIVE),

)))->findAll();



How should I write a query to retrieve all deposits for active customers? (And by query I really mean scope, since I would like this encapsulated in a method.)

Is it considered good practice to do something like this? This seems like a lot of multi-level complexity and dependence for such a simple query and I’m wondering if there’s a better pattern.




<?php

// Find all deposits for customers with active logins.

CustomerDeposit::model()->with(array(

    'customer' =>array(

        'login' =>array(

            'condition' =>'login.status = :login_active',

            'params' =>array(':login_active' =>CustomerLogin::STATUS_ACTIVE),

        ),

    )

))->findAll();



Never mind, it’s in the docs:




CustomerDeposit::model()->with('customer:active')->findAll();



But how about with dynamic scopes?

Well I suppose you can define any sophisticated query conditions inside named scopes

and then just use it CustomerDeposit::model()->customerStatus(1)->findAll();

Actually I just implemented the docs version, and it does not work in CGridView. Do you know if this is a known bug? (The relation’s scope is not applied to the query - you need to explicitly restate the condition.)