How to use Inner Join..?


#1

Hai,

Can anyone tell me shortly how to join (inner) two tables ,…?
I referred documentation but i am not getting …

anyone Help me…

Regards,


#2
$data = $this->Model1->find('all')
        ->select(['id', 'name'])
        ->innerJoinWith('Model2', function ($q) use ($filter_status) {
            return $q->where(['Model2.status' => $filter_status]);
        });

or if you dont need the related model data:

$data = $this->Model1->find('all')
->matching('Model2');

you can use deep association notation too:

$data = $this->Model1->find('all')
->matching('Model2.Model3');

if you need retaled model data:

$data = $this->Model1->find('all')
->contain([
    'Model2',
    'Model5',
]);

all this depends on you doing the correct association in the table model


#3

Can u please tell me where can i add that code

sorry i am new to cakephp


#4

in the Controller function


#5

@Diego Couple of small corrections :slight_smile:

Will only work if they are related, and will not include the related data in the result entities.

Does include the related data (in the _matchingData property)

To do an inner join without any association existing, you can use the innerJoin() query method.

@itsdarshan

There is quite a lot of information available in the book. https://book.cakephp.org/3.0/en/orm/associations.html and https://book.cakephp.org/3.0/en/orm/query-builder.html#loading-associations are good starting points.

I also highly recommend taking part in the nearly free interactive training course: https://training.cakephp.org and watching some of the past CakeFest workshop videos on youtube