Re: PEAR::Tree limiting fetched fields

Jeroen Houben wrote:

Wolfram Kriesing wrote:

Jeroen Houben wrote:

Below is my table structure. It is a simple structure to store an online book. Each section of the book has subsections, which also have subsections etc etc. PEAR::Tree would be excellent for this, but I don't need to fetch the actual contents of the section when generating the tree (it would only slow stings down when the table grows bigger) so my question is: Can I tell the Tree object to only select sectionID, ParentID and title and not all fields?? Thanks, Jeroen # # Table structure for table sections # CREATE TABLE sections ( sectionID smallint(5) unsigned NOT NULL auto_increment, parentID smallint(6) NOT NULL default '0', title varchar(255) default 'no title', content longtext, PRIMARY KEY (sectionID), KEY title (title) ) TYPE=MyISAM;

sorry, but currently the Tree-package is not capable of this. It is actually meant to handle *only* the tree in the table. I'd suggest you move the 'content' in a seperate table. Your SQL will be faster too, since your table data will be less, and if you only need the tree data at some point it might make a lot of sense. what do you think? -- Wolfram

Yes I had thought of that too. It's a valid solution although I would still prefer to have one table, because it simplifies inserts and updates. From a normalisiation point of view I don't need to split my table into two different tables. I think for now I will just stick to my one table and see how performance is, I don't think I'll ever get into real performance issues as the online book I'm creating is only 4 levels deep and will have a total of +-100 entries in the database. Your class is very helpful to me, keep up the good stuff. Jeroen Hi Wolfram,

In the email above I asked you if it was possible to limit the fields fetched. I had a look at your code and it was really to implement and unless I have overlooked something, my "patch" seems to work. $options can now look like this (I put the code that makes this work at the bottom of this email)

       $options = array(
       'select'            =>'sectionID, parentID, title',
       'table'                =>'sections',
       'columnNameMaps'    =>
           array(
           'id'            =>'sectionID',
           'parentId'         =>'parentID'
           )
       );

I thought about select as being an array of field but then I thought this was more flexible, because now you can do stuff like 'select' => SUM(somefield), anotherfield as B, anotherfield as C ... This setup is a little more prone to errors thought because people might forget to put in their id and parentId. Welll most people won't need this (select will just default to *) and if they do then they should know what they're doing right? Let me know if you can use this. Thanks, Jeroen /** * retreive all the navigation data from the db and call build to build the * tree in the array data and structure *

   *   @version    2001/11/20
   *   @access     public
   *   @author     Wolfram Kriesing <wolfram@kriesing.de>
   *   @return     boolean     true on success

*/ function setup() { // TODO sort by prevId (parentId,prevId $addQuery) too if it exists in the table, or the root might be wrong // TODO since the prevId of the root should be 0

       //
       $select = '';
       if ($this->options['select']) {
           $select = $this->getOption('select');
       } else {
           $select = '*';
       }
             $whereAddOn = '';
       if( $this->options['whereAddOn'] ) {
           $whereAddOn = 'WHERE '.$this->getOption('whereAddOn');
       }

       //
       $orderBy = '';
       if( $this->options['order'] ) {
           $orderBy = ",".$this->options['order'];
       }

       $map = $this->getOption('columnNameMaps');
       if( isset($map['parentId']) ) {
           $orderBy = $map['parentId'].$orderBy;
       } else {
           $orderBy = 'parentId'.$orderBy;
       }

       // build the query this way, that the root, which has no parent (parentId=0)
       // and no previous (prevId=0) is in first place (in case prevId is given)
       $query = sprintf(   "SELECT %s FROM %s %s ORDER BY %s",
                           $select,
                           $this->table,
                           $whereAddOn,
                           $orderBy); //,prevId !!!!
       if( DB::isError( $res = $this->dbh->getAll( $query ) ) ) {
       // FIXXME remove print use debug mode instead
           printf("ERROR - Tree::setup - %s - %s<br>",DB::errormessage($res),$query);
           return $this->_throwError($res->getMessage(),__LINE__);
       }