We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 17499 ☆ A M B ☆
    • 872 Posts
    When bindGraph, akk the columns are aded to the query automatically.

    How can i override the default select columns before doing getCollectionGraph to limit the retreived columns that i desire ?
      • 17499 ☆ A M B ☆
      • 872 Posts
      Funny thing while testing, came across an issue where my sub select was not accessible directly.

      Example:

      <?php
      
      $graph = '{ "Ancestors": {}, "Descendants": {} }';
      $c = $modx->newQuery('myTree');
      
      // This subquery was not accessible in the loop
      $c->select('( SELECT COUNT(*) 
      	FROM '.$modx->getTableName('myTreeClosure').'
      	WHERE ancestor = myTree.id
          AND descendant != myTree.id ) AS `nb_branches`');
      
      $c->where(array(
          'Descendants.ancestor' => 0,
          'Descendants.depth' => 0,
          'myTree.leaf' => $id,
      ));
      $c->groupBy('myTree.title');
      
      $branches = $modx->getCollectionGraph( 'myTree', $graph, $c );
      
      foreach ($branches as $branch) {
      	//That was not working even though the SQL dump was working correctly
      	$nb = $branch->get('nb_branches');
      }
      


      However, by change the alias of the filed it was then accessible like i wanted it to:

      <?php
      // Give a used prefix to the alias of the retreived field - here i use myTree
      $c->select('( SELECT COUNT(*) 
      	FROM '.$modx->getTableName('myTreeClosure').'
      	WHERE ancestor = myTree.id
          AND descendant != myTree.id ) AS `myTree_nb_branches`');
      	
      //Then the loop was working 
      foreach ($branches as $branch) {
      	$nb = $branch->get('nb_branches');
      	//or
      	$array[] = $branch->toArray();
      }
      


      It works as i want but is there any more cleaner method to achieve the same result ?
        • 22303 MODX Staff
        • 10,725 Posts
        You should never use objects when selecting summary information from a table, e.g. SUM(), COUNT() AVG(), etc. This means anytime you access a field you did not select, it will go to the database again (lazy loading), not to mention the collection has no idea what id a particular object would be. I would recommend just using the PDOStatement after creating the query and simply executing it and working with the result rows manually.
          • 17499 ☆ A M B ☆
          • 872 Posts
          Ok, but how can set the select columns that i want to retreive or remove some columns that are automatically included when using bindGraph ?

            • 22303 MODX Staff
            • 10,725 Posts
            What columns do you want to exclude? You must at least select the primary key fields, plus any other fields that might be involved in loading the object, like class_key when working with modResource. Regardless, you would use the select() method of xPDOQuery. Just keep in mind that the graph depends on the aliases used in the xPDOQuery to define the joins.
              • 17499 ☆ A M B ☆
              • 872 Posts
              Example :

              <?php
              $graph = '{ "Relate": {}, "RelateToo": {} }';
              
              $c = $modx->newQuery('myTable');
              $c->bindGraph( $graph );
              
              //Instead of having all the columns from the Related tables i want to select manually which columns will be retreived
              $c->select( /* getSelectColumns does not work fror the graph ?  */ ); 
              
              if ($c->prepare() && $c->stmt->execute()) {
              	while ($result = $c->stmt->fetch(PDO::FETCH_ASSOC)) {
              		//Loop
              	}
              }
              


              I’ve read on another thread about the necessity of the PK (and Shawn wilkinson’s blog too)

              But if i use $c->select( $modx->getSelectColumns... I can’t access the related table columns to exclude field that i don’t need.

              And since i don’t use getCollection, i would like to specify a custom prefix for the related columns directly in the query too, instead of having Relate_fieldname and RelateToo_fieldname in the returned array.
                • 22303 MODX Staff
                • 10,725 Posts
                The problem here is that bindGraph is a convenience method for adding all the required joins and select statements needed to return the rows required to create instances of each xPDOObject class in the Graph. A feature request to make the select() optional or provide a way to override it might be in order here.

                In the meantime, I would create your joins and select manually to work with this data in the way you are attempting to. Also, getSelectColumns() allows you to set the table alias and prefix for each column.