Relation In The Same Table

I’ve this structure:


utenti

 id

 user

 pass

 ...


zone

 id

 nome


zone_utenti

 id_utente (utenti.id)

 id_zona (zone.id)


azioni

 id

 id_utente (utenti.id)

 descrizione

 ...

and this data:


utenti

ID | User

1  | nicola

2  | foo

3  | bar


zone

ID | nome

1  | Area 1

2  | Area 2

3  | Area 3


zone_utenti

id_utente | id_zona 

1         | 1     

1         | 2       

2         | 1

3         | 3


azioni

ID | id_utente | descrizione

1  | 2         | foo action

2  | 3         | bar action

If a user logs should be able to see only the actions performed by users who are in that same area.

There are multiple :: MANY MANY relations for the same table "users".

In the example above log-in as “nicola” I can see only the actions of “foo” but not “bar” because isn’t in my zone(s)!

In the model, users have the relations




'azioni' => array(self::HAS_MANY, 'Azioni', 'id_utente'),

'zone' => array(self::MANY_MANY, 'Zone', 'zone_utenti(id_utente, id_zona)'),



Nel modello azioni ho




'utente' => array(self::BELONGS_TO, 'Utenti', 'id_utente', 'with' => 'zone'),



How can I get a CActiveDataProvider that through a CDbCriteria me back a list of users?

This is a workaround… ugly, but it works:


$criteria->with = array(

'utente' => array(

    ...

    'condition' => 'utente.id IN ( 

SELECT id_utente 

FROM zone_utenti

WHERE id_zona IN ( '.implode(',', $loggedUser->getRelatedZonesId()).' ) )',

    )

); 

logged in user’s zone:


$loggedUser->getRelatedZonesId

any better solution?