migxLoopCollection only returns first result from table joined as one to many

[chris.dempsey78]

Attempting to render this on the frontend with migxLoopCollection works, but only includes the first Option for each Product.

I expected to be able to list multiple options, and for these to be returned as a JSON string or similar.

Actual pseudo output is

• Product 1 name, option_name 1
• Product 2 name, option_name 1
• Product 3 name, option_name 1

Expected pseudo output is

• Product 1 name, option_name 1

• Product 1 name, option_name 2

• Product 2 name, option_name 1

• Product 2 name, option_name 2

• Product 3 name, option_name 1

• Product 3 name, option_name 2

I then expected to be able to format the options JSON string as part of the &tpl template with a custom snippet.

The migxLoopCollection call I’m using is

[[!migxLoopCollection?
  &packageName=`catalog`
  &classname=`product`

  &joins=`[{"alias":"option","selectfields":"name,price"}]`
  &where=`{"published":"1","option.published":"1"}`
  
  &tpl=`migx-loop-row.tpl`
  
  &debug=`1`
]]

Debug shows the SQL query generated is

SELECT `product`.`id`, `product`.`category`, `product`.`name`, `product`.`colour`, `product`.`description`, `product`.`image`, `product`.`deleted`, `product`.`published`, `product`.`pos`, `product`.`createdon`, `product`.`createdby`, `product`.`editedon`, `product`.`editedby`, `option`.`name` AS `option_name`, `option`.`price` AS `option_price` FROM `rap2_catalog_products` AS `product` LEFT JOIN `rap2_catalog_options` `option` ON `product`.`id` = `option`.`rel_product_id` WHERE ( `product`.`published` = 1 AND `option`.`published` = 1 ) 

When I run this query directly on the database the dataset returned contains all the options as expected - each product has 2x options.

I can’t see anything in the Tutorials in the Docs that indicates where I’ve gone wrong but suspect an extra argument needs passed to migxLoopColletion.

Can anyone advise what needs done to extract the related product options?

Thanks

Package schema is below for reference.

<?xml version="1.0" encoding="UTF-8"?>
<model package="catalog" baseClass="xPDOObject" platform="mysql" defaultEngine="INNODB" version="1.1">
    <!-- define object
        - prefix tables with package name to group and separate them from modx core tables or tables created by other packages
    -->
    <object class="product" table="catalog_products" extends="xPDOSimpleObject" >
        <!-- available fields
             - category
             - name
             - colour
             - description
             - image
        -->
        <field key="category" dbtype="varchar" precision="255" phptype="string" null="false" default="" index="index" />
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="colour" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="description" dbtype="text" phptype="string" index="fulltext" />
        <field key="image" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <!-- used by MIGX -->        
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <!-- link related tables
             - base table (catalog_products) to subordinate table (catalog_options)
             - note 'id' is created automatically
        -->
        <composite alias="option" class="option" local="id" foreign="rel_product_id" cardinality="many" owner="local" /> 
        
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>
    </object>
    <!-- define object
         - prefix tables with package name to group and separate them from MODX core tables or tables created by other packages
    -->
    <object class="option" table="catalog_options" extends="xPDOSimpleObject">
        <!-- available fields
             - rel_product (catalog_products.id)
             - name (dimensions)
             - price
        -->
        <!-- related core table id -->
        <field key="rel_product_id" dbtype="int" precision="11" phptype="integer" null="false" default=""/>
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="price" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <!-- used by MIGX -->        
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <!-- link related tables
             - subordinate table (catalog_options) back to base table (catalog_product)
             - note 'id' is created automatically
        -->
        <aggregate  alias="product" class="product" local="rel_product_id" foreign="id" cardinality="one" owner="foreign" /> 
        
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>
    </object>
</model>

[halftrainedharry]

I’m not really familiar with the migxLoopCollection snippet and don’t know what its limitations are.

But you could write your own snippet to query the data. For example with getCollectionGraph.

Here is some example code:

<?php
//add the catalog package
$base_path = $modx->getOption('core_path') . 'components/catalog/';
$modx->addPackage('catalog', $base_path . 'model/');

//query the products
$products = $modx->getCollectionGraph('product', '{"option":{}}', ["published" => 1, "option.published" => 1]);
$output = [];
foreach ($products as $product) {
    $options = [];
    foreach ($product->getMany('option') as $option) {
        $option_array = $option->toArray();
        $options[] = $modx->getChunk('tplOption', $option_array);
    }
    
    $product_array = $product->toArray();
    $product_array["options"] = implode("\n", $options);
    $output[] = $modx->getChunk('tplProduct', $product_array);
}
return implode("\n", $output);

Chunk tplOption:

<li>[[+name]] | [[+price]]</li>

Chunk tplProduct:

<h4>Product: [[+name]]</h4>
<ul>[[+options]]</ul>

[bruno17]

what do you get without &tpl ?

[chris.dempsey78]

Without &tpl it dumps the response to screen as a list of arrays. First 3 are

Array
(
    [id] => 1
    [category] => lawn-memorial
    [name] => Boyd
    [colour] => Black
    [description] => An all polished low maintenance 'Black' granite memorial.

The ornament illustrates the outline of a heart surrounded by roses and leaves, symbolising love.

Normally supplied with a family initial in the ornament. (Please state initial in order notes)
    [image] => images/memorials/boyd.jpg
    [deleted] => 0
    [published] => 1
    [pos] => 1
    [createdon] => 2022-05-18 11:17:05
    [createdby] => 1
    [editedon] => 2022-05-19 09:36:42
    [editedby] => 1
    [option_name] => 750mm x 600mm (2'6" x 2'0")
    [option_price] => 881
    [_alt] => 1
    [_first] => 
    [_last] => 
    [_idx] => 2
    [idx] => 2
    [property.packageName] => catalog
    [property.classname] => product
    [property.joins] => [{"alias":"option","selectfields":"name,price"}]
    [property.sortConfigXXX] => [{"sortby":"RAND()"}]
    [property.where] => {"published":"1","option.published":"1"}
    [property.tplXXX] => migx-loop-row.tplXXX
    [property.debug] => 1
    [_count] => 12
    [_total] => 7
)

Array
(
    [id] => 3
    [category] => lawn-memorial
    [name] => Birnie
    [colour] => Karin Grey
    [description] => This memorial is illustrated in 'Karin Grey' granite with a rustic margin. The headstone has a sanded face and back with rustic edges. The base has a sanded top and rustic sides.
    [image] => images/memorials/birnie.jpg
    [deleted] => 0
    [published] => 1
    [pos] => 2
    [createdon] => 2022-05-18 13:41:11
    [createdby] => 1
    [editedon] => 2022-05-19 10:18:07
    [editedby] => 1
    [option_name] => 750mm x 550mm (2'6" x 1'10")
    [option_price] => 1292
    [_alt] => 1
    [_first] => 
    [_last] => 
    [_idx] => 4
    [idx] => 4
    [property.packageName] => catalog
    [property.classname] => product
    [property.joins] => [{"alias":"option","selectfields":"name,price"}]
    [property.sortConfigXXX] => [{"sortby":"RAND()"}]
    [property.where] => {"published":"1","option.published":"1"}
    [property.tplXXX] => migx-loop-row.tplXXX
    [property.debug] => 1
    [_count] => 12
    [_total] => 7
)

Array
(
    [id] => 4
    [category] => lawn-memorial
    [name] => Haddow
    [colour] => Bon Accord 
    [description] => This all polished fully worked book memorial is shown in 'Bon Accord Dark Grey' granite. It has a carved cord and tassel which has been left unpolished to highlight this feature.

The base has a splayed/sloping front which is ideally suited for a family name or text. 
    [image] => images/memorials/haddow.jpg
    [deleted] => 0
    [published] => 1
    [pos] => 0
    [createdon] => 2022-05-18 15:06:36
    [createdby] => 1
    [editedon] => 2022-05-19 10:38:59
    [editedby] => 1
    [option_name] => 750mm x 600mm (2'6" x 2'0")
    [option_price] => 1500
    [_alt] => 1
    [_first] => 
    [_last] => 
    [_idx] => 6
    [idx] => 6
    [property.packageName] => catalog
    [property.classname] => product
    [property.joins] => [{"alias":"option","selectfields":"name,price"}]
    [property.sortConfigXXX] => [{"sortby":"RAND()"}]
    [property.where] => {"published":"1","option.published":"1"}
    [property.tplXXX] => migx-loop-row.tplXXX
    [property.debug] => 1
    [_count] => 12
    [_total] => 7
)

I expected to see something like this (apologies for the poor syntax representation)

Array
(
    [id] => 1
    [category] => lawn-memorial
    [name] => Boyd
    [colour] => Black

    // nested array of options
    Array
   (
    [option_name] => 750mm x 600mm (2'6" x 2'0")
    [option_price] => 881

    [option_name] => 850mm x 750mm (2'10" x 2'6")
    [option_price] => 913

I was about to look at getCollectionGraph like @halftrainedharry suggested. Any advice you can offer about whether migxLoopCollection can do what I need would be appreciated.

[chris.dempsey78]

I changed migxLoopCollection snippet to dump the $rows returned to screen instead of passing them to $output = $migx->renderOutput($rows,$scriptProperties);.

$c = $migx->prepareQuery($xpdo,$scriptProperties);
$rows = $migx->getCollection($c);
print_r($rows);exit;

This shows the options I’m looking for are there, just not nested as expected. I’m unsure why the second option is not output to screen when &tpl is specified.

    [0] => Array
        (
            [id] => 1
            [category] => lawn-memorial
            [name] => Boyd
            [colour] => Black
            [description] => An all polished low maintenance 'Black' granite memorial.

The ornament illustrates the outline of a heart surrounded by roses and leaves, symbolising love.

Normally supplied with a family initial in the ornament. (Please state initial in order notes)
            [image] => images/memorials/boyd.jpg
            [deleted] => 0
            [published] => 1
            [pos] => 1
            [createdon] => 2022-05-18 11:17:05
            [createdby] => 1
            [editedon] => 2022-05-19 09:36:42
            [editedby] => 1
            [option_name] => 850mm x 750mm (2'10" x 2'6")
            [option_price] => 913
        )

    [1] => Array
        (
            [id] => 1
            [category] => lawn-memorial
            [name] => Boyd
            [colour] => Black
            [description] => An all polished low maintenance 'Black' granite memorial.

The ornament illustrates the outline of a heart surrounded by roses and leaves, symbolising love.

Normally supplied with a family initial in the ornament. (Please state initial in order notes)
            [image] => images/memorials/boyd.jpg
            [deleted] => 0
            [published] => 1
            [pos] => 1
            [createdon] => 2022-05-18 11:17:05
            [createdby] => 1
            [editedon] => 2022-05-19 09:36:42
            [editedby] => 1
            [option_name] => 750mm x 600mm (2'6" x 2'0")
            [option_price] => 881
        )

[bruno17]

If you are looking for nested output, well, you can nest another migxLoopCollection call into the outer tpl

[chris.dempsey78]

@halftrainedharry
That’s perfect, thank you.

Must admit I don’t quite understand what getMany does in the background.

Isolates the options array related to the current product in the loop obviously so they can be formatted and added to the $product_array for access via the [[+options]] placeholder.

It’s a nice working example, I’m sure I can use in future.

@bruno17
Noted re. nesting a second migxLoopCollection call in the outer tpl. Should have run with that the other day as I did see it used in other search examples but I like learning some new options.

Thanks both.