CakePHP Sub Queries

Question

I want to show in the index view all users with a photo and the city. Name and photo are ok. The problem is the city. I created model user:

public $hasOne = array(
        'Photo' => array(
            'className' => 'Photo',
            'dependent' => true
        ),
        'Scheda' => array(
            'className' => 'Scheda',
            'dependent' => true
        )
    );

And the model City

class Citta extends AppModel {
    public $hasOne = 'Citta';
}

and finally the UsersController

  public function index() {
          $data= $this->User->find('all', array(
                    'contain' => array('Photo'),
             'conditions'=>array('User.attivo' => '1'),
             'group'=>array('User.id'),
             'type'=>'INNER'));

$this->loadModel('Citta');      
$citta=$this->Citta->find('all', array(
 'conditions' => array('Scheda.citta_id = Citta.id'),
));
$this->set('user',$data);
$this->set('citta',$citta);        
  }

I received this error

Error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'Citta'

SQL Query: SELECT Citta.id, Citta.value FROM massaggi2.cittas AS Citta LEFT JOIN massaggi2.cittas AS Citta ON (Citta.citta_id = Citta.id) WHERE Scheda.citta_id = Citta.id

What's wrong?

Thanks Alex

Answer 1

Your problem is that Citta hasOne Citta -- both named the same thing. Just change up the name of the association that Citta has, for example:

class Citta extends AppModel {
     public $hasOne = array(
            'OtherCity' => array(
                 'className' => 'Citta',
                 'dependent' => false,
             );
      );
}