cakePHP 3 - How to get sub-queries in SELECT section

Question

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

First of all, use cakephp ORM methods to select fields, make conditions, order results, etc. Read and learn book.cakephp.org/3/en/orm/query-builder.html — Salines

jsf jsf · Accepted Answer · 2020-03-21T20:54:22

I attempted to Cake the sub-queries via

$select[] = $this->Inventory->find()->select([$key => 'OnHand'])->where(['WhsCode' => $key, 'ItemCode' => 'Products.ItemCode']);

I didn't have much hope and of course, it did not work as Products.ItemCode was being treated as a string instead of the actual DB table & fields when Cake created the query. Re-reading the query builder documentation for Cake (as suggested by @Salinas) still had me stumped as the sub-query section was utilizing joins. My query & sub-queries don't have joins in this particular query.

At length, I realized that I was double single quoting my sub-query alias and warehouse code value when running through my loop of warehouses. Originally, I did so to ensure that my warehouse code value was treated as a string. As I wasn't seeing the double single quotes in debug(sql($query)), I didn't think this was a problem. Ex: $select["'".$key."'"] = "(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = '".$key."')"; That is what seemed to be problem with Cake's ResultSet.

Ultimately, here is my loop to create the sub-queries. I pre-pended text to the sub-query alias and type casted the warehouse code (potential values are all numbers but some are zero prefixed). I still think the sub-query could/should be Caked but as this question doesn't seem to be triggering anyone's interest, I'll close it out as I did solve my problem.

foreach($this->Inventory->coWrhs as $key => $val)
{
    //how about pre-prending something to the select key & try type casting WhsCode
    $select['whrs_'.$key] = "(SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = ".(string)$key.")";
}

Cake's find(), including the $select array for the fields:

$query = $this->Inventory->Products->find('all', [
            'fields' => $select, 
            'conditions' => $conditions, 
            'order' => [$sort => $direction]
         ]);

To ultimately create this query:

SELECT 
  Products.ItemCode AS [Products__ItemCode], 
  Products.ItemName AS [Products__ItemName], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 03) AS [whrs_03], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 04) AS [whrs_04], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 05) AS [whrs_05], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 06) AS [whrs_06], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 07) AS [whrs_07], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 08) AS [whrs_08], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 09) AS [whrs_09], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 14) AS [whrs_14], 
  (SELECT OnHand FROM OITW WHERE ItemCode = Products.ItemCode AND WhsCode = 15) AS [whrs_15] 
FROM 
  OITM Products 
WHERE 
    (Products.validFor = 'Y' AND Products.ItemCode like 'TCP%') 
ORDER BY 
  Products.ItemName ASC OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY

An easy loop-able Caked (somewhat?) array object with pagination built with sub-queries directly dependent upon a field in the primary query.

cakePHP 3 - How to get sub-queries in SELECT section

controller code => index method

1 Answers