next »

[OpenGeek]

I'm working hard to get the xPDO documentation up to date and more complete.  In the meantime, I wanted to introduce everyone to the latest feature additions to xPDO 1.0 alpha (currently only available from SVN; I'll update the download packages later today) as of revision 27.

xPDOQuery -- Ok, so I decided some SQL abstraction was necessary. 

Based on feedback from a few early adopters of xPDO, I have added a significant new extension to the xPDO arsenal.  Previously, in order to do more complex queries on the objects, it was necessary to write the SQL to do so.  You could wrap it with an xPDOCriteria object to pass it into the xPDO object handling methods along with some bindings.  But all too common are slight variations of the scaffolding SQL that is automatically handled by the object layer, and we needed a way to get record counts, apply limits, order by clauses, additional where conditions, etc.  Enter xPDOQuery.

xPDOQuery extends the xPDOCriteria class, allowing programmatic control over the generated SQL, via a logical API.  You can still manually wrap SQL with xPDOCriteria, but compare the two approaches:

Old way, using xPDOCriteria:

Code:

<?php
$webUserTable= $xpdo->getTableName('modWebUser');
$sql= "SELECT * FROM {$webUserTable} WHERE `id` = :user_id LIMIT 1";
$bindings= array(
    ':user_id' => array ('value' => $userid, 'type' => PDO_PARAM_INT)
);
$criteria= new xPDOCriteria($this->xpdo, $sql, $bindings);
$user= $xpdo->getObject('modWebUser', $criteria);

Same query, new way, using xPDOQuery (via $xpdo->newQuery()):

Code:

<?php
$criteria= $xpdo->newQuery('modWebUser', $userid);
$criteria->limit(1);
$user= $xpdo->getObject('modWebUser', $criteria);

and in PHP 5, you can even use the shorthand...

Code:

<?php
$criteria= $xpdo->newQuery('modWebUser')->where($userid)->limit(1);
$user= $xpdo->getObject('modWebUser', $criteria);

[davidm]

From my limited perspective code-wise, I can't say I am unhappy with having some SQL abstraction and xPDOQuery seems like something I'd be very comfortable using

I sure am grateful for the update Jason, especially since I try to wrap my head around the whole paradigm shift that the new core is... this heads up will make things easier and smoother for sure...

[kylej]

Jason,
   That looks great, I definatly would have used that on my fantasy site.  I think this will really add to the flexibility and easy of use for xPDO.  Well done.

Kyle

[OpenGeek]

Also, just to exemplify how xPDOQuery helps to really expand the power of the object layer, here are some new functions for xPDO itself which came about as a result of the new xPDOQuery capabilities:
NOTE: A criteria referred to in any of the xPDO parameters below, can be an xPDOCriteria instance, an xPDOQuery instance, a single string or integer primary key value, an array of primary key values in the proper order, or an xPDO conditional expression.  See the examples below:

getCount(className, criteria): returns a count of records of a particular class, optionally using a specified criteria.

Code:

<?php 
// counts all web user records
$xpdo->getCount('modWebUser'); 
// counts all web user records with a username that starts with the letter a
$xpdo->getCount('modWebUser', $xpdo->newQuery('modWebUser', array ('username:LIKE' => 'a%'));

getObjectGraph(className, graph, criteria): returns an object with related objects populated in a single query; automated joins specified by a nested array (or JSON representation of an array) of related classes and their relation keys

Code:

<?php 
// gets a web user with an id of 1, along with their related user profile record
$xpdo->getObjectGraph('modWebUser', '{modWebUserProfile:{internalKey:{}}}', 1);

getCollectionGraph(className, graph, criteria): returns a collection of objects with related objects populated in a single query

Code:

<?php 
// gets all web users and their profiles, sorted by username
$xpdo->getCollectionGraph('modWebUser', '{modWebUserProfile:{internalKey:{}}}', $xpdo->newQuery('modWebUser')->sortby('modWebUser.username'));

[PaulGregory]

getCount, getObjectGraph and getCollectionGraph sound great.

Returning to your first example, I can't see how xPDOQuery knows that $userid should be compared with `id`.
Is that the default, or is that configured somewhere, or is $userid compared against all fields, or what?

And how would you compare $userid against another field instead like `introducedby`?  Would that use the array method seen in the more recent examples?

[kylej]

Paul,  I think in Jasons first example userid id being compared to the primary key, and if you would want to use it against a different field the array method would work.

[OpenGeek]

Right, basically a criteria can be any of the following:

• a primary key value
• an array of primary key values in the correct column order (for compound primary keys)
• an array expression in the form array( '[tableAlias.]fieldName[:operator]' => value)
• an xPDOCriteria instance (or xPDOQuery, since it is a derivative class of xPDOCriteria)

xPDO knows what the primary key fields are and if you just pass a scalar or an array of scalars without associative keys, xPDO assumes that's what you are searching by.

[pixelchutes]

Awesome stuff, Jason. I love it!

[airoctive]

Is it possible to build a WHERE field_name IN clause using this class?

[OpenGeek]

Is it possible to build a WHERE field_name IN clause using this class?

Sure; here is an example where the criteria expression key is used with a modifier indicating to use the IN operator, rather than the default operator (=):

Code:

$events= $xpdo->getCollection('Events', array ('id:IN' => '(2080,2081,2082,2083,2084,2085)'));

or for some strings:

Code:

$events= $xpdo->getCollection('Events', array ('name:IN' => '("EventA","EventB","EventC")'));

[airoctive]

I tried using your code above but I receiving an error in the prepared sql. Looks like the bound value in the IN clause is being escaped using '. Is there any way to get around this?

[OpenGeek]

I tried using your code above but I receiving an error in the prepared sql. Looks like the bound value in the IN clause is being escaped using '. Is there any way to get around this?

ATM, the :IN clause is not working for the reason you describe; when refactoring some issues with quoting last year with prepared statements, I broke this capability.  It's on my list to address, but in the meantime, you can simply include the full expression (avoiding the use of the prepared statement parameter), like so:

Code:

$events= $xpdo->getCollection('Events', array ("id IN (2080,2081,2082,2083,2084,2085)"));