CODEWITHSUNDEEP
phpmysqlsqlcakephpcakephp-3.0
Rey Chabby Estrera
Rey Chabby Estrera

Reputation: 11

CakePhp getting the number of entries of each code

I wanted to search for the number of entries each branch code carriesI want to do a search of data and get the number of entries it has on another table

$query = $this->find();
$query->select([
    'name',
    'code',
    'count' => "(
        SELECT
            COUNT(products_branches.id)
        FROM
            products_branches 
        INNER JOIN
            branches
                ON branches.company_id = products_branches.company_id 
                AND branches.code = products_branches.branch_code 
        WHERE 
            products_branches.deleted = 0
            AND products_branches.carried = 1
            AND products_branches.company_id = $company_id
            AND products_branches.branch_code = code
    )",
]);

is there a way that I could use the code fetch in the select and use at as one of the condition in the search condition of the subquery? I want to search the number of entries each name and code has on product_branches table

Upvotes: 0

Views: 39

Answers (1)

tjb74
tjb74

Reputation: 67

What you're looking to do is possible, but you've got a few more steps to complete. You need to make sure you create an Association between the products_branches and branches table first. Then you can use the where() function to do what you want to do. Something like this:

$q = $this-find('all')
    ->select(['Branches.name', 'Branches.code'])
    ->contain(['ProductsBranches'])
    ->where(['ProductsBranches.deleted' => 0, 
             'ProductsBranches.carried' => 1,
             'ProductsBranches.company_id' => $company_id,
             'ProductsBranches.branch_code' => $code]);
$count = $q->all()->count();

Upvotes: 1

Related Questions