Sub queries in custom finders


#1

Hi, I want to make sub queries in custom finders but I don’t know how to make it, in cakePhp 3.x releases.
My SQL request is like on the image bellowrequete

And the finder method in scr/model/Table is like this:
public function findEtatStock(Query $query){
$query = $this->find()
->select([
‘produits.designation’,
‘produits.stock’,
‘approvisionnements.qteentree’,
‘ventes.qtesortie’,
‘etat_stock’ => ‘??’ // the place of the request
]);

        $query
            ->innerJoinWith('Approvisionnements')
            ->innerJoinWith('Ventes')
            ->where([
                ['approvisionnements.produit_id = produits.id'], ['ventes.produit_id = produits.id']
            ])
            ->groupBy('produits.desingation');
    return $query;
}