We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 52884
    • 25 Posts
    Hi there and a happy new Year to everyone!

    I am struggling with building a query in a getList Processor.
    I have two DB tables, shops and products. Now I am trying to list some products from the products DB table in a Custom Manager Page Grid. I would like to have 1 column in this grid, where all the shops are listed that offer this product. This is a many2many relationship, since each product can be offered by multiple shops and shops can offer multiple products. What would be the best way to build the query in the getList Processor? If have tried to adapt the code from https://docs.modx.com/xpdo/2.x/getting-started/creating-a-model-with-xpdo/defining-a-schema/defining-relationships#DefiningRelationships-RelatingManytoMany in the prepareQueryBeforeCount function in the getlist processor for the products, but that led to a 500 error.

    My shema:

    <?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>
    [ed. note: balihoo last edited this post 7 years, 3 months ago.]
      • 3749
      • 24,544 Posts
      I think you want to base your query on MyShopProducts, where 'product' = 1, with a join of the MYshop object.

      You should also be able to do it with getCollectionGraph():

      $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');
      }




        Did I help you? Buy me a beer
        Get my Book: MODX:The Official Guide
        MODX info for everyone: http://bobsguides.com/modx.html
        My MODX Extras
        Bob's Guides is now hosted at A2 MODX Hosting
        • 52884
        • 25 Posts
        Thanks Bob!

        I think my problem was that I tried to use that in the prepareRow function instead of the prepareQueryBeforeCount where it actually belongs which still throws an error 500. I had a look at some extras on github and basically all of them using the prepareQueryBeforeCount function for this, which makes sense to me now.

        I have the correct query in SQL but I am not sure how to translate it to xpdo so that I can use it in the prepareQueryBeforeCount function of a getlistprocessor:

        --> results in a table with columns id, title, sku and shops:

        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

          • 3749
          • 24,544 Posts
          Did you try using getCollectionGraph()? It would do most of that for you.
            Did I help you? Buy me a beer
            Get my Book: MODX:The Official Guide
            MODX info for everyone: http://bobsguides.com/modx.html
            My MODX Extras
            Bob's Guides is now hosted at A2 MODX Hosting
            • 52884
            • 25 Posts
            If I understand getCollectionGraph() right, I already get a specific result or set of results as objects.

            However, I actually wanted to edit the query in prepareQueryBeforeCount function, so that I can also search/filter for shop names in the cmp grid, etc. - I think my initial post was a bit misleading since I already gave an example which led in a wrong direction. Sorry for that.

              • 3749
              • 24,544 Posts

              Take a look at this processor for an example:

              core/model/modx/processors/security/access/getlist.class.php [ed. note: BobRay last edited this post 7 years, 3 months ago.]
                Did I help you? Buy me a beer
                Get my Book: MODX:The Official Guide
                MODX info for everyone: http://bobsguides.com/modx.html
                My MODX Extras
                Bob's Guides is now hosted at A2 MODX Hosting