We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 12728
    • 6 Posts
    I try to understand how xPDO works and as you understand - I faced some problems smiley
    And I hope some one can point me on my mistakes.
    As demo DB I used Sakila Sample Database http://dev.mysql.com/doc/sakila/en/sakila.html

    So I made pdo connection this way
    <?php 
    include("xpdo/xpdo.class.php");
    
    $xpdo= new xPDO('mysql:host=localhost;dbname=sakila',"root","pass",
    					array (
    						XPDO::OPT_CACHE_PATH => 'cache/',
    						XPDO::OPT_TABLE_PREFIX => '',
    						XPDO::OPT_HYDRATE_FIELDS => true,
    						XPDO::OPT_HYDRATE_RELATED_OBJECTS => true,
    						XPDO::OPT_HYDRATE_ADHOC_FIELDS => true,
    						XPDO::OPT_VALIDATE_ON_SAVE => true,
    					),
    					array (
    						PDO::ATTR_ERRMODE => 'PDO_ERRMODE_SILENT',
    						PDO::ATTR_PERSISTENT => false,
    						PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
    					)
    				); 
    ?>
    

    generated and parsed such Model (now I wrote only important part of the model, because there are much more tables in demo DB)

    <?xml version="1.0" encoding="UTF-8"?>
    <model package="MyDBModel" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM">
    	<object class="Actor" table="actor" extends="xPDOObject">
    		<field key="actor_id" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" index="pk"  generated="native" />
    		<field key="first_name" dbtype="varchar" precision="45" phptype="string" null="false" />
    		<field key="last_name" dbtype="varchar" precision="45" phptype="string" null="false" index="index" />
    		<field key="last_update" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP"  extra="on update current_timestamp" />
    		<composite alias="FilmActor" class="FilmActor" local="actor_id" foreign="actor_id" cardinality="many" owner="local" />
    	</object>
    	<object class="Film" table="film" extends="xPDOObject">
    		<field key="film_id" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" index="pk"  generated="native" />
    		<field key="title" dbtype="varchar" precision="255" phptype="string" null="false" index="index" />
    		<field key="description" dbtype="text" phptype="string" null="true" />
    		<field key="release_year" dbtype="year" precision="4" phptype="string" null="true" />
    		<field key="language_id" dbtype="tinyint" precision="3" attributes="unsigned" phptype="integer" null="false" index="index" />
    		<field key="original_language_id" dbtype="tinyint" precision="3" attributes="unsigned" phptype="integer" null="true" index="index" />
    		<field key="rental_duration" dbtype="tinyint" precision="3" attributes="unsigned" phptype="integer" null="false" default="3" />
    		<field key="rental_rate" dbtype="decimal" precision="4,2" phptype="float" null="false" default="4.99" />
    		<field key="length" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="true" />
    		<field key="replacement_cost" dbtype="decimal" precision="5,2" phptype="float" null="false" default="19.99" />
    		<field key="rating" dbtype="enum" precision="'G','PG','PG-13','R','NC-17'" phptype="string" null="true" default="G" />
    		<field key="special_features" dbtype="set" precision="'Trailers','Commentaries','Deleted Scenes','Behind the Scenes'" phptype="string" null="true" />
    		<field key="last_update" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP"  extra="on update current_timestamp" />
    		<composite alias="FilmActor" class="FilmActor" local="film_id" foreign="film_id" cardinality="many" owner="local" />
    	</object>
    	<object class="FilmActor" table="film_actor" extends="xPDOObject">
    		<field key="actor_id" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" index="pk" />
    		<field key="film_id" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" index="pk" />
    		<field key="last_update" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP"  extra="on update current_timestamp" />
    		<aggregate alias="Actor" class="Actor" local="actor_id" foreign="actor_id" cardinality="one" owner="foreign" />
    		<aggregate alias="Film" class="Film" local="film_id" foreign="film_id" cardinality="one" owner="foreign" />
    	</object>
    </model>


    I think db relations is quite simple and clear.
    So when I make some simple data request like $xpdo->getObject(’Actor’, 1); - all works perfect.

    I faced problems in queries with relations.
    When I try request like example in documentation

    <?php 
    $actor = $xpdo->getObject('Actor',1);
      
    echo "Actor - ".$actor->first_name."  ".$actor->last_name."<br>";
    
    $actors = $actor->getMany('FilmActor'); 
     foreach ($actors as $actor) {  
        echo "Film ".$actor->film_id;
    	echo " / ".$actor->getOne('Film')->title."<br>";
     } ?> 


    I get a proper data like

    Actor - PENELOPE GUINESS
    Film 1 / ACADEMY DINOSAUR
    Film 23 / ANACONDA CONFESSIONS
    Film 25 / ANGELS LIFE
    Film 106 / BULWORTH COMMANDMENTS
    ......


    but when I try to optimize it (like in documentation) this way

    <?php 
    $c = $xpdo->newQuery('FilmActor');  
    $c->innerJoin('Film','Film','Film.film_id = FilmActor.film_id');
    $c->innerJoin('Actor','Actor','Actor.actor_id = FilmActor.actor_id');
    $c->where(array(  
        'FilmActor.actor_id' => 1,
    ));
    $xpdo->setDebug(true);
    $items = $xpdo->getCollection('FilmActor',$c);
    ?>


    i can`t get related fields from Film and Actor tables, Debuger show that xPDO make such request

    SELECT `FilmActor`.`actor_id` AS `FilmActor_actor_id`, `FilmActor`.`film_id` AS `FilmActor_film_id`, `FilmActor`.`last_update` AS `FilmActor_last_update` FROM `film_actor` AS `FilmActor` JOIN `film` `Film` ON Film.film_id = FilmActor.film_id JOIN `actor` `Actor` ON Actor.actor_id = FilmActor.actor_id WHERE `FilmActor`.`actor_id` = ? Array


    and we can see that there are no Film and Actor fields in SELECT statement, but JOIN statement is alright.
    So can you tell me - what am I doing wrong here ? smiley


    And the second problem I got.
    Tried to use xPDOCriteria like this

    <?php 
    $actorTable = $xpdo->getTableName('Actor');  
    $query= new xPDOCriteria($xpdo,'  
         SELECT * FROM '.$actorTable.'  
         WHERE `actor_id` < :index  
         LIMIT 10  
    ',array(  
    ':index' => 2,
    ));  
    $actors = $xpdo->getCollection('Actor',$query);  
    print_r($actors);
    ?>


    but as a result I receive

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 261900 bytes) in /Applications/XAMPP/xamppfiles/htdocs/xPDOTest/xpdo/om/xpdoobject.class.php  on line 1502


    (this is on Mac. Or just broke connection with server on Windows local server).
    So can you tell where is the mistake in this xPDOCriteria statement ?

    P/S Server info : Apache 2 , PHP 5.3.0 , MySQL 5.1.37 (pdo_mysql has the same version)
      -> MODx-CMS.ru Русскоязычное сообщество / Russian community
    • Quote from: iJack at Feb 11, 2010, 02:42 PM

      I faced problems in queries with relations.
      ...

      but when I try to optimize it (like in documentation) this way

      <?php 
      $c = $xpdo->newQuery('FilmActor');  
      $c->innerJoin('Film','Film','Film.film_id = FilmActor.film_id');
      $c->innerJoin('Actor','Actor','Actor.actor_id = FilmActor.actor_id');
      $c->where(array(  
          'FilmActor.actor_id' => 1,
      ));
      $xpdo->setDebug(true);
      $items = $xpdo->getCollection('FilmActor',$c);
      ?>


      i can`t get related fields from Film and Actor tables, Debuger show that xPDO make such request

      SELECT `FilmActor`.`actor_id` AS `FilmActor_actor_id`, `FilmActor`.`film_id` AS `FilmActor_film_id`, `FilmActor`.`last_update` AS `FilmActor_last_update` FROM `film_actor` AS `FilmActor` JOIN `film` `Film` ON Film.film_id = FilmActor.film_id JOIN `actor` `Actor` ON Actor.actor_id = FilmActor.actor_id WHERE `FilmActor`.`actor_id` = ? Array


      and we can see that there are no Film and Actor fields in SELECT statement, but JOIN statement is alright.
      So can you tell me - what am I doing wrong here ? smiley
      If you are trying to get all the related objects in a single request, you can simply use xPDO::getCollectionGraph():
      <?php
      $items = $xpdo->getCollectionGraph('FilmActor', '{"Film":{},"Actor":{}}', array(
          'FilmActor.actor_id' => 1,
      ));
      ?>

      You could also use xPDOQuery::bindGraph() to build the criteria as well:
      <?php
      $c = $xpdo->newQuery('FilmActor');
      $c->bindGraph('{"Film":{},"Actor":{}}');
      $c->where(array(  
          'FilmActor.actor_id' => 1,
      ));
      $xpdo->setDebug(true);
      $items = $xpdo->getCollectionGraph('FilmActor', '{"Film":{},"Actor":{}}', $c);
      ?>

      Then you can access the related objects without issuing another query:
      <?php
      foreach ($items as $item) {
          echo "Film " . $item->film_id;
          echo " / " . $item->Film->title;
          echo " / " . implode(" ", $item->Actor->get(array('first_name', 'last_name'))) . "<br />";
      }
      ?>


      Quote from: iJack at Feb 11, 2010, 02:42 PM

      And the second problem I got.
      Tried to use xPDOCriteria like this

      <?php 
      $actorTable = $xpdo->getTableName('Actor');  
      $query= new xPDOCriteria($xpdo,'  
           SELECT * FROM '.$actorTable.'  
           WHERE `actor_id` < :index  
           LIMIT 10  
      ',array(  
      ':index' => 2,
      ));  
      $actors = $xpdo->getCollection('Actor',$query);  
      print_r($actors);
      ?>


      but as a result I receive

      Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 261900 bytes) in /Applications/XAMPP/xamppfiles/htdocs/xPDOTest/xpdo/om/xpdoobject.class.php  on line 1502

      xPDOCriteria is the base class for xPDOQuery; however, xPDO::getObject*() and getCollection*() methods require either raw criteria to be passed (raw criteria meaning primary key values, an associative array of bindings, raw SQL where clauses, and/or any combination of these nested into arrays) or an xPDOQuery instance, not an xPDOCriteria instance. IOW, this is valid:
      <?php
      $query= new $xpdo->newQuery('Actor', array('actor_id:<' => 2));
      $query->limit(10);
      $actors = $xpdo->getCollection('Actor',$query);
      foreach ($actors as $actor) {
          print_r($actor->toArray());
      }
      ?>

      FYI, print_r on the entire collection of objects can produce some excessive output (and use a lot of memory); it’s better to output the fields using xPDOObject::toArray() or limit the output in some other way.

      Use of xPDOCriteria is more for when you don’t want to return objects (though you can use xPDOQuery this way as well), maybe something like this:
      <?php
      $actorTable = $xpdo->getTableName('Actor');  
      $query= new xPDOCriteria($xpdo, "SELECT * FROM {$actorTable} WHERE `actor_id` < :index LIMIT 10", array(
          ':index' => 2,
      ));
      if ($query->prepare() && $query->stmt->execute()) {
          while ($actor = $query->stmt->fetch(PDO::FETCH_ASSOC)) {
              print_r($actor); /* this is just an associative array representing a row from the result set instead of an object */
          }
      }
      ?>
        • 12728
        • 6 Posts
        Thanks, Jason, your answer helped me a lot. But I still can`t get some things.

        To make it clear for me - while using newQuery with innerJoin , leftJoin, rightJoin we won`t receive all related field from joined tables? This methods only allows us to use fields from joined tables in request (i.e. WHERE filtering by related fields) , but do not return this fields ?

        In your documentation example http://svn.modxcms.com/docs/display/xPDO20/Retrieving+Objects
        <?php
        $c = $xpdo->newQuery('Box');  
        $c->innerJoin('BoxOwner','Owner'); // arguments are: className, alias  
        $c->innerJoin('User','User','Owner.user = User.id');   
        // note the 3rd argument that defines the relationship in the innerJoin  
          
        $c->where(array(  
           'Box.width' => 5,  
            'User.user' => 2,  
        ));  
        $c->sortby('Box.name','ASC');  
        $c->limit(5,5);  
        $boxes = $xpdo->getCollection('Box',$c);
        ?>


        I won`t get "User.user" field in $boxes ? To get it I should use getOne() at each $boxes or something. Right ?
        This is not very clear moment and not mentioned in documentation, while getCollection(Object)Graph are not documented at all - but these are very important functions.


        The second thing. When I make some straightforward request like
        <?php
        $query= $xpdo->newQuery('Actor');
        $query->where(array('actor_id:<=' => 20));
        $query->limit(10);
        
        $actors = $xpdo->getCollection('Actor',$query);
        foreach ($actors as $actor) {
           print_r($actor->toArray());
        }
        ?>

        I receive proper answer , but if I try to use SELECT method I receive an error
        This query
        <?php
        $query= $xpdo->newQuery('Actor');
        $query->select('Actor.actor_id, Actor.last_name'); 
        $query->where(array('actor_id:<=' => 20));
        $query->limit(10);
        
        $actors = $xpdo->getCollection('Actor',$query);
        foreach ($actors as $actor) {
           print_r($actor->toArray());
        }
        ?>

        generate such a request
        SELECT Actor.actor_id, Actor.last_name FROM `actor` AS `Actor` WHERE `Actor`.`actor_id` <= ? LIMIT 10 Array

        but script fall with the same error as the last times
        Fatal error: Allowed memory size of 134217728 bytes exhausted

        I tried $query->select(’actor_id, last_name’); and $query->select(’`Actor`.`actor_id`, `Actor`.`last_name`’); - all them generates proper working queries, but script just fall down, while processing it with the same error (even when I comment print_r($actor->toArray()); so memory overflow appears while $xpdo->getCollection(), not while print_r() ).
        How can I correctly limit selected columns in request ?



        And about the xPDOCriteria. I think I understood about it from your previous post. But then the manual is wrong http://svn.modxcms.com/docs/display/xPDO20/Retrieving+Objects
        <?php   
        $boxTable = $xpdo->getTableName('Box');  
           $query= new xPDOCriteria($xpdo,'  
                SELECT * FROM '.$boxTable.'  
                WHERE `width` > :width  
                LIMIT 10  
            ',array(  
              ':width' => 4,  
            ));  
           $boxes = $xpdo->getCollection('Box',$query);  
        ?>

        because raw xPDOCriteria is passed into getCollection here. Or I still don`t get something ? smiley
          -> MODx-CMS.ru Русскоязычное сообщество / Russian community
        • Quote from: iJack at Feb 15, 2010, 09:30 AM

          To make it clear for me - while using newQuery with innerJoin , leftJoin, rightJoin we won`t receive all related field from joined tables? This methods only allows us to use fields from joined tables in request (i.e. WHERE filtering by related fields) , but do not return this fields ?
          You can manually set what fields are returned in the result set using xPDOQuery::select(). The bindGraph() method auto-adds the select() and joins necessary to get the related objects in one query.

          Quote from: iJack at Feb 15, 2010, 09:30 AM

          In your documentation example http://svn.modxcms.com/docs/display/xPDO20/Retrieving+Objects
          <?php
          $c = $xpdo->newQuery('Box');  
          $c->innerJoin('BoxOwner','Owner'); // arguments are: className, alias  
          $c->innerJoin('User','User','Owner.user = User.id');   
          // note the 3rd argument that defines the relationship in the innerJoin  
            
          $c->where(array(  
             'Box.width' => 5,  
              'User.user' => 2,  
          ));  
          $c->sortby('Box.name','ASC');  
          $c->limit(5,5);  
          $boxes = $xpdo->getCollection('Box',$c);
          ?>


          I won`t get "User.user" field in $boxes ? To get it I should use getOne() at each $boxes or something. Right ?
          This is not very clear moment and not mentioned in documentation, while getCollection(Object)Graph are not documented at all - but these are very important functions.
          Not unless you select() them, just like in any SQL statement. Feel free to volunteer to help improve/add-to the documentation.

          Quote from: iJack at Feb 15, 2010, 09:30 AM

          The second thing. When I make some straightforward request like
          <?php
          $query= $xpdo->newQuery('Actor');
          $query->where(array('actor_id:<=' => 20));
          $query->limit(10);
          
          $actors = $xpdo->getCollection('Actor',$query);
          foreach ($actors as $actor) {
             print_r($actor->toArray());
          }
          ?>

          I receive proper answer , but if I try to use SELECT method I receive an error
          This query
          <?php
          $query= $xpdo->newQuery('Actor');
          $query->select('Actor.actor_id, Actor.last_name'); 
          $query->where(array('actor_id:<=' => 20));
          $query->limit(10);
          
          $actors = $xpdo->getCollection('Actor',$query);
          foreach ($actors as $actor) {
             print_r($actor->toArray());
          }
          ?>

          generate such a request
          SELECT Actor.actor_id, Actor.last_name FROM `actor` AS `Actor` WHERE `Actor`.`actor_id` <= ? LIMIT 10 Array

          but script fall with the same error as the last times
          Fatal error: Allowed memory size of 134217728 bytes exhausted
          I don’t know why the error (not obvious), but don’t select only a couple of columns for an object and then run toArray() or you will execute an additional query for each column you did not select for it to be populated from the database. This is called lazy loading.

          Quote from: iJack at Feb 15, 2010, 09:30 AM

          I tried $query->select(’actor_id, last_name’); and $query->select(’`Actor`.`actor_id`, `Actor`.`last_name`’); - all them generates proper working queries, but script just fall down, while processing it with the same error (even when I comment print_r($actor->toArray()); so memory overflow appears while $xpdo->getCollection(), not while print_r() ).
          How can I correctly limit selected columns in request ?
          Make sure the generated query is correct and not returning too much data. Before calling getCollection, do $query->prepare() and then $query->toSQL() to output the SQL that is going to be executed.

          Quote from: iJack at Feb 15, 2010, 09:30 AM

          And about the xPDOCriteria. I think I understood about it from your previous post. But then the manual is wrong http://svn.modxcms.com/docs/display/xPDO20/Retrieving+Objects
          <?php   
          $boxTable = $xpdo->getTableName('Box');  
             $query= new xPDOCriteria($xpdo,'  
                  SELECT * FROM '.$boxTable.'  
                  WHERE `width` > :width  
                  LIMIT 10  
              ',array(  
                ':width' => 4,  
              ));  
             $boxes = $xpdo->getCollection('Box',$query);  
          ?>

          because raw xPDOCriteria is passed into getCollection here. Or I still don`t get something ? smiley
          You can do that, I was just explaining that xPDOQuery knows the relationships you need automatically. Feel free to write raw SQL in an xPDOCriteria, but you’ll need to be responsible for the results.
            • 12728
            • 6 Posts
            Funny things... I played with these "debuger tools" you equiped me with. And find out, that my problem is in getCollection. Select() and Joins in other combinations are working with getObject, getCollectionGraph and perfectly with "$query->stmt->execute() / $query->stmt->fetch()", but falls on getCollection (so... I`ll just try to avoid it for some time tongue maybe it is something with my test db, I just dont know what else to think).

            And as I understand there is no way to limit selected columns after bindGraph()? newQuery with custom Joins select() must be used for these purposes ?
              -> MODx-CMS.ru Русскоязычное сообщество / Russian community
            • Quote from: iJack at Feb 15, 2010, 11:33 PM

              Funny things... I played with these "debuger tools" you equiped me with. And find out, that my problem is in getCollection. Select() and Joins in other combinations are working with getObject, getCollectionGraph and perfectly with "$query->stmt->execute() / $query->stmt->fetch()", but falls on getCollection (so... I`ll just try to avoid it for some time tongue maybe it is something with my test db, I just dont know what else to think).
              What specific code is failing on getCollection but is working with getObject/getCollectionGraph? These use basically the same object loader functions, so it’s not clear to me exactly where your problem is...

              Quote from: iJack at Feb 15, 2010, 11:33 PM

              And as I understand there is no way to limit selected columns after bindGraph()? newQuery with custom Joins select() must be used for these purposes ?
              Well, you can manually clear the select columns in xPDOQuery::$query[’columns’], and add back the ones you want, but you MUST include the primary keys for each object in the graph or it will not work; you MUST always at least select the primary key columns to return an xPDOObject instance.