CakePHP 3 - Query Builder doesn't work correctly for HasMany associations

Question

Table: Services

+-----+--------------+ | id | title | +-----+--------------+ | 1 | Service 1 | +-----+--------------+ | 2 | Service 2 | +-----+--------------+ 

Table: Workshops {HasMany WorkshopServices }

 +-----+---------------+ | id | title | +-----+---------------+ | 1 | Workshop 1 | +-----+---------------+ | 2 | Workshop 2 | +-----+---------------+ 

WorkshopServices table

 +-----+--------------+-------------+ | id | workshop_id | service_id | +-----+--------------+-------------+ | 1 | 1 | 1 | +-----+--------------+-------------+ | 2 | 1 | 2 | +-----+--------------+-------------+ 

I want to find Workshops on service_id

My request

 $this->Workshops->find() ->contain([ 'WorkshopServices' ]) ->where([ 'WorkshopServices.service_id IN'=>[1,2,3] ]); 

Query result

 Unknown column `WorkshopServices.service_id` in 'where clause' 

In fact, the Workshops table does not generate any JOIN with the WorkshopServices table.

How can I write a query to get the correct result from Query Builder ?

Answer 1

I updated the @GabrielFerreira request and grouped the rows WorkshopServices.workshop_id , this solution matches my problem

 $array = [1,2,3]; $this->Workshops->find() ->select([ 'title'=>'Workshops.title', 'id'=>'Workshops.id', ]) ->matching('WorkshopServices', function ($q) use($array) { return $q->where(['WorkshopServices.service_id IN' => $array]); }) ->group(['WorkshopServices.workshop_id']); 

Answer 2

Use collation:

 $array = [1,2,3]; $this->Workshops->find() ->matching('WorkshopServices', function ($q) use($array) { return $q->where(['WorkshopServices.service_id IN' => $array]) });