CODEWITHSUNDEEP
cakephpcakephp-2.0cakephp-2.3
hellosheikh
hellosheikh

Reputation: 3015

HABTM with join Cakephp

hello I have a table name University_Industry. It has uni_id and ind_id as foriegn keys of University table and Industry table. So meaning university will have many industries and industries will have many universities. I am getting the result successfully by running this query

public function getAllUniv(){
    return $this->find('all', array(
        'order' => 'uni_id',
        ));
     }

This query returns me universities and their industries.Result look like this

Array
(
    [0] => Array
        (
            [University] => Array
                (
                    [uni_id] => 1
                    [uni_name] => NYC
                )

            [Industry] => Array
                (
                    [0] => Array
                        (
                            [ind_id] => 1
                            [ind_name] => Finance
                            [UniversityIndustry] => Array
                                (
                                    [id] => 1
                                    [uni_id] => 1
                                    [ind_id] => 1
                                )

                        )

                    [1] => Array
                        (
                            [ind_id] => 2
                            [ind_name] => Accounting
                            [UniversityIndustry] => Array
                                (
                                    [id] => 2
                                    [uni_id] => 1
                                    [ind_id] => 2
                                )

                        )

                )

        )

Now I have another table which called users which has uni_id also as a foreign key. So I am looking for a query which can fetch only those universities with the industries which are found in users table. So It should fetch only those universities whose uni_id is exist in users table

I have no idea how can I join users table in my query and get the results.

This is how my University Modal look like at the moment

class University extends AppModel
{   

    public $useTable = 'university';    
    public $primaryKey = 'uni_id';

    public $hasAndBelongsToMany = array(
    'Industry' =>
        array(
            'className'              => 'Industry',
            'joinTable'              => 'university_industry',
            'foreignKey'             => 'uni_id',
            'associationForeignKey'  => 'ind_id',
            'unique'                 => true,
            'conditions'             => '',
            'fields'                 => '',
            'order'                  => '',
            'limit'                  => '',
            'offset'                 => '',
        ),
       );
    public function getAllUniv(){
    return $this->find('all', array(
        'order' => 'uni_id',
        ));
     }
}

Upvotes: 1

Views: 58

Answers (1)

tarikul05
tarikul05

Reputation: 1913

You can join data in your find query.

$this->find('all', array(
    'order' => 'uni_id',
    'joins' => array(  
        array(
        'table' => 'users ',
        'alias' => 'UserJoin',
        'type' => 'inner',
        'conditions' => array(
            'UserJoin.uni_id = University.id'
            )
        ),
    ));

Here is official doc. Hope it will help you

Upvotes: 4

Related Questions