Does anyone have any suggestions or experience with how to achieve a sub-query in the SELECT that is directly dependent upon the values in the fields of the primary query within CakePHP 3? Query is shown below.
I'm attempting to get the OnHand value for each item in each warehouse. My list of items and warehouses will be dynamic. Some users may see up to 12 warehouses and 2K-3K items. With my query in SQL, I can get an array of items with each item containing 3 - 12 elements. A nice array for easy looping for display purposes.
My query is valid per SQL. I receive about 200 results. I'm sure that I've built the sub-queries in the non-cake way but I couldn't figure out how to achieve the sub-query within the select portion of my query.
I understand now the problem is not directly with pagination, but with the ResultSet (duh, the error message said that from the start). I've attempted the query without pagination. I can worry about pagination later. Just
$query = $this->Inventory->Products->find('all', ['fields' => $select, 'conditions' => $conditions, 'order' => [$sort => $direction]]);
$query->all(), $query->toList() & $query->first() all generate the same ResultSet error.
Examples within CakePHP documentation and most (if not all, I'm sure I've missed some) of the other examples in stack show sub-queries in the FROM or WHERE section of queries or when there is a join between the tables.
OR
Am I going about this incorrectly? Can I achieve the same idea of results in a different query?
Any and all help is greatly appreciated. Thanks.
As well, thanks to @ndm for editing my post to make it more reader friendly. You had to work hard. :)
Environment:
- CakePHP 3.8.6
- PHP 7.3.7
- MSSQL 2014
controller code => index method
debug($select);
yields
[(int) 0 => 'Products.ItemCode',
(int) 1 => 'Products.ItemName',
''03'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '03')',
''04'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '04')',
''05'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '05')',
''06'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '06')',
''07'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '07')',
''08'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '08')',
''09'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '09')',
''14'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '14')',
''15'' => '(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '15')']
debug($conditions);
yields
['Products.validFor' => 'Y',
'Products.ItemCode LIKE ' => 'TCP%']
$this->paginate = [
'fields' => $select,
'conditions' => $conditions,
'order' => [$sort => $direction]
];
$query = $this->Inventory->Products->find('all', $this->paginate);
debug($query);
yields
SELECT
Products.ItemCode AS [Products__ItemCode],
Products.ItemName AS [Products__ItemName],
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '03') AS '03',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '04') AS '04',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '05') AS '05',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '06') AS '06',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '07') AS '07',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '08') AS '08',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '09') AS '09',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '14') AS '14',
(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '15') AS '15'
FROM
OITM Products
WHERE
(Products.validFor = :c0 AND Products.ItemCode like :c1)
ORDER BY
Products.ItemName ASC
$inventory = $this->paginate($query, ['limit' => 50]);
debug($inventory);
yields
Argument 1 passed to Cake\ORM\Entity::__construct() must be of the type array, bool given, called in C:\inetpub\wwwroot\vendor\cakephp\cakephp\src\ORM\ResultSet.php on line 602