We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 14951
    • 151 Posts
    I'm having a strange issue with a seemingly simple xPDO query.
    $c = $modx->newQuery('erCategories');
    $c->where(array('menu_id' => $mid,'active' => 1,));
    $c->sortby($sort,$dir);
    

    My query works beautifully as long as I don't include the sortby part but returns nothing if I have the sortby part so I decided to add:
    $c->construct(); return $c->toSQL();

    Which returned: (Note: Added line spacing for better readability)
    SELECT `erCategories`.`id` AS `erCategories_id`, 
    `erCategories`.`name` AS `erCategories_name`, 
    `erCategories`.`description` AS `erCategories_description`, 
    `erCategories`.`image` AS `erCategories_image`, 
    `erCategories`.`position` AS `erCategories_position`, 
    `erCategories`.`menu_id` AS `erCategories_menu_id`, 
    `erCategories`.`active` AS `erCategories_active` 
    FROM `modx_easyrestaurant_categories` AS `erCategories` 
    WHERE ( `erCategories`.`menu_id` = 1 
    AND `erCategories`.`active` = 1 ) ORDER BY id ASC
    

    That is when I realized that my orderby part only had "id" as opposed to "erCategories_id"...so I changed the query to:
    $c = $modx->newQuery('erCategories');
    $c->where(array('menu_id' => $mid,'active' => 1,));
    $c->sortby('erCategories_id',$dir);
    

    And then it started working perfectly..

    So I'm wondering...Why do I have to ad erCategories_id manually when all my other fields work fine as just the field name alone?

    Shouldn't this be added automatically?

    And since I'm only querying one object... shouldn't "ORDER BY id ASC" work anyhow without the need for the prefix?

    I'm stumped.... Looking forward to a response from someone smarter than me! (which isn't saying much) wink

    Thanks!
    -Jared

      • 34084 ☆ A M B ☆
      • 756 Posts
      Are you setting the $sort and $dir varibles? In the code pasted they are not being set with something like above the newQuery definition:

      $sort = $modx->getOption('sort',$scriptProperties,'erCategories_id');
      $dir = $modx->getOption('dir',$scriptProperties,'ASC');


      Cheers
        Evo Revo // Ubuntu, CentOS, Win // Apache 2x, Lighttp (Lighty)
        Visit CharlesMx.com for latest news and status updates.
        • 22303 MODX Staff
        • 10,725 Posts
        Does erCategories extend xPDOSimpleObject or xPDOObject?
          • 14951
          • 151 Posts
          xPDOSimpleObject

          and yes...Both $sort and $dir are set =)
            • 22303 MODX Staff
            • 10,725 Posts
            I cannot reproduce this problem with any of my models. What version of xPDO (or MODX) are you using? What database driver?
              • 14951
              • 151 Posts
              I'm running MODX 2.1.3pl on my localhost.

              Server Environment is MAMP Pro: 2.0.1
              MySQL version: 5.5.9
              PHP version: 5.2.17
              PDO Drivers: sqlite, sqlite2, pgsql, mysql
              PDO Driver for MySQL: 5.5.9

              Not sure if that is the correct info or not?

              Here is my DB Schema:
              <?xml version="1.0" encoding="UTF-8"?>
              <model package="easyrestaurant" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM">
              	<object class="erMenus" table="easyrestaurant_menus" extends="xPDOSimpleObject">
              		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
                      <field key="description" dbtype="text" phptype="string" default=""/>
              		<field key="image" dbtype="varchar" precision="255" phptype="string" default=""/>
              		<field key="resource_id" dbtype="int" precision="10" phptype="integer" default="0" />r
              		<field key="active" dbtype="tinyint" precision="1" phptype="integer" default="1"/>
              		
              		<composite alias="menuCategories" class="erCategories" local="id" foreign="menu_id" cardinality="many" owner="local" />
              		<composite alias="menuItems" class="erItems" local="id" foreign="menu_id" cardinality="many" owner="local" />
              		<composite alias="resourceId" class="modResource" local="resource_id" foreign="id" cardinality="one" owner="foreign"/>	
              	</object>
              
              	<object class="erCategories" table="easyrestaurant_categories" extends="xPDOSimpleObject">
              		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
                      <field key="description" dbtype="text" phptype="string" default=""/>
              		<field key="image" dbtype="varchar" precision="255" phptype="string" default=""/>
              		<field key="position" dbtype="int" precision="10" phptype="integer" default="0" />
              		<field key="resource_id" dbtype="int" precision="10" phptype="integer" default="0" />
              		<field key="active" dbtype="tinyint" precision="1" phptype="integer" default="1"/>
              		
              		<aggregate alias="menuResource" class="modResource" local="resource_id" foreign="id" cardinality="one" owner="foreign" />
              		<composite alias="menuItems" class="erItems" local="id" foreign="cat_id" cardinality="many" owner="local" />		
              	</object>
              	
                  <object class="erItems" table="easyrestaurant_items" extends="xPDOSimpleObject">
              		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
                      <field key="description" dbtype="text" phptype="string" default=""/>
                      <field key="price" dbtype="varchar" precision="255" phptype="string" default=""/>
                      <field key="image" dbtype="varchar" precision="255" phptype="string" default=""/>
                      <field key="featured" dbtype="tinyint" precision="1" phptype="integer" default="0"/>
              		<field key="cat_id" dbtype="int" precision="10" phptype="integer" default="0" />
              		<field key="menu_id" dbtype="int" precision="10" phptype="integer" default="0" />
                      <field key="position" dbtype="int" precision="10" phptype="integer" default="0" />
              		<field key="active" dbtype="tinyint" precision="1" phptype="integer" default="1"/>
              
              		<aggregate alias="menuCategories" class="erCategories" local="cad_id" foreign="id" cardinality="one" owner="foreign" />
                  </object>
              </model>
              


              And my snippet which runs great, less having to add the weird fix for the sort issue:
              <?php
              $easyrestaurant = $modx->getService('easyrestaurant','Easyrestaurant',$modx->getOption('easyrestaurant.core_path',null,$modx->getOption('core_path').'components/easyrestaurant/').'model/easyrestaurant/',$scriptProperties);
              if (!($easyrestaurant instanceof Easyrestaurant)) return '';
              
              /* setup default properties */
              $resid = $modx->resource->get('id');
              $tpl = $modx->getOption('tpl',$scriptProperties,'rowTpl');
              $outertpl = $modx->getOption('outertpl',$scriptProperties,'outerTpl');
              $cattpl = $modx->getOption('cattpl',$scriptProperties,'catTpl');
              $sort = $modx->getOption('sort',$scriptProperties,'position');
              $dir = $modx->getOption('dir',$scriptProperties,'ASC');
              $menuid = $modx->getOption('menuid', $scriptProperties,$resid);
              $showinactive = $modx->getOption('showinactive',$scriptProperties,0);
              $separator = $modx->getOption('separator',$scriptProperties,"\n");
              $catSeparator = $modx->getOption('categorySeparator',$scriptProperties,"\n");
              
              $output = '';
              
              /* run db query */
              $sort = "erCategories_" . $sort; // Weird debug necessary to get sortby running
              
              $c = $modx->newQuery('erCategories');
              $c->where(array('resource_id' => $menuid));
              if($showinactive == 0) $c->where(array('active' => 1));
              $c->sortby($sort,$dir);
              //$c->construct(); return $c->toSQL();
              $categories = $modx->getCollection('erCategories', $c );
              
              $output = array();
              foreach ($categories as $category) {
              	$cats = array();
              	$sort = $modx->getOption('sort',$scriptProperties,'position');
              	$sort = "erItems_" . $sort;
              	$catid = $category->get('id');
              	$c = $modx->newQuery('erItems');
              	$c->where(array('cat_id' => $catid));
              	if($showinactive == 0) $c->where(array('active' => 1));
              	$c->sortby($sort,$dir);
              	$items = $modx->getCollection('erItems', $c );
                  if(count($items) >=1) {
              		$categoryArray = $category->toArray();
              		$cats[] = $easyrestaurant->getChunk($cattpl,$categoryArray);
              	}
              	
              	foreach ($items as $item) {
              		$itemArray = $item->toArray();
              		$cats[] = $easyrestaurant->getChunk($tpl,$itemArray);
              	}
              	
              	$cats = implode($separator,$cats);
              	$output[] = $cats;	
              }
              $output = implode($catSeparator,$output);
              
              $output = $easyrestaurant->getChunk($outertpl,array('wrapper' => $output));
              
              return $output;
              

              I tried for 6 hours straight to do this with getObjectGraph but gave up...not sure why I couldn't get it to work.

              Thank you sincerely for your assistance!