<?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 ) ); ?>
<?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>
<?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>"; } ?>
Actor - PENELOPE GUINESS Film 1 / ACADEMY DINOSAUR Film 23 / ANACONDA CONFESSIONS Film 25 / ANGELS LIFE Film 106 / BULWORTH COMMANDMENTS ......
<?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); ?>
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
<?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); ?>
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
If you are trying to get all the related objects in a single request, you can simply use xPDO::getCollectionGraph():
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 ?
<?php $items = $xpdo->getCollectionGraph('FilmActor', '{"Film":{},"Actor":{}}', array( 'FilmActor.actor_id' => 1, )); ?>
<?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); ?>
<?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 />"; } ?>
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:
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
<?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()); } ?>
<?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 */ } } ?>
<?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); ?>
<?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()); } ?>
<?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()); } ?>
SELECT Actor.actor_id, Actor.last_name FROM `actor` AS `Actor` WHERE `Actor`.`actor_id` <= ? LIMIT 10 Array
<?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); ?>
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.
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 ?
Not unless you select() them, just like in any SQL statement. Feel free to volunteer to help improve/add-to the documentation.
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.
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.
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
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.
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 ?
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.
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 ?
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...
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 maybe it is something with my test db, I just dont know what else to think).
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.
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 ?