<?xml version="1.0" encoding="UTF-8"?>
<model package="myshoppackage" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
<object class="MYshop" table="myshop_shops" extends="xPDOSimpleObject">
<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
<index alias="Name" name="Name" primary="false" unique="false" type="BTREE">
<column key="name" length="" collation="A" null="false" />
</index>
<composite alias="ShopProducts" class="MYshopProducts" local="id" foreign="shop" cardinality="many" owner="local" />
</object>
<object class="MYproduct" table="myshop_products" extends="xPDOSimpleObject">
<field key="title" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
<field key="sku" dbtype="int" precision="20" attributes="unsigned" phptype="integer" null="false" default="0" />
<index alias="Title" name="title" primary="false" unique="false" type="BTREE">
<column key="title" length="" collation="A" null="false" />
</index>
<index alias="SKU" name="SKU" primary="false" unique="true" type="BTREE">
<column key="sku" length="" collation="A" null="false" />
</index>
<composite alias="ShopProducts" class="MYshopProducts" local="id" foreign="product" cardinality="many" owner="local" />
</object>
<object class="MYshopProducts" table="myshop_shops_products" extends="xPDOObject">
<field key="shop" dbtype="int" attributes="unsigned" precision="10" phptype="integer" null="false" index="pk" />
<field key="product" dbtype="int" attributes="unsigned" precision="10" phptype="integer" null="false" index="pk" />
<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE">
<column key="shop" length="" collation="A" null="false" />
<column key="product" length="" collation="A" null="false" />
</index>
<aggregate alias="Product" class="MYproduct" local="product" foreign="id" cardinality="one" owner="foreign" />
<aggregate alias="Shop" class="MYshop" local="shop" foreign="id" cardinality="one" owner="foreign" />
</object>
</model>$productId = 1;
$shopNames = array();
$c = $modx->newQuery('MYshopProducts');
$c->sortby('Shop.name', 'ASC');
$c->where(
array('product' => $productId)
);
$results = $modx->getCollectionGraph('MYshopProducts', {'Shop:{} }, $c);
foreach ($results as $result) {
$shopNames[] = $results->Shop->get('name');
}
SELECT
p.*,
GROUP_CONCAT(s.name SEPARATOR ', ') AS shops
FROM
myshop_products p
LEFT JOIN myshop_shops_products ps ON p.id = ps.product
LEFT JOIN myshop_shops s ON s.id = ps.shop
GROUP BY
p.id
ORDER BY
p.title