<?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