CakePhp getting the number of entries of each code

Question

I 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

tjb74 · Accepted Answer · 2022-09-21 14:31:02Z

1

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();

answered Sep 21, 2022 at 14:31

tjb74

671 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CakePhp getting the number of entries of each code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related