• Introducing xPDOQuery#

  • 22303
    9,586
    - MODX Chief Architect

    Jason Coward | MODX Chief Architect

    jasoncoward.com | @drumshaman

    opengeek Reply #1, 4 years, 11 months ago

    Reply
    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:
    <?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()):
    <?php
$criteria= $xpdo->newQuery('modWebUser', $userid);
$criteria->limit(1);
$user= $xpdo->getObject('modWebUser', $criteria);

    and in PHP 5, you can even use the shorthand...
    <?php
$criteria= $xpdo->newQuery('modWebUser')->where($userid)->limit(1);
$user= $xpdo->getObject('modWebUser', $criteria);



  • 6726
    7,075
    - Former MODx evangelist
    .: COO - Commerce Guys - Community Driven Innovation :.


    MODx est l'outil id

    davidm Reply #2, 4 years, 11 months ago

    Reply
    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...


  • 15987
    784

    kjaebker Reply #3, 4 years, 11 months ago

    Reply
    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


  • 22303
    9,586
    - MODX Chief Architect

    Jason Coward | MODX Chief Architect

    jasoncoward.com | @drumshaman

    opengeek Reply #4, 4 years, 11 months ago

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


  • 22815
    1,097
    - MODx's midnight runner
    No, I don't know what OpenGeek's saying half the time either.
    MODx Documentation: The Wiki | My Wiki contributions | Main MODx Documentation
    Forum: Where to post threads about add-ons | Forum Rules
    Like MODx? donate (and/or share your resources)
    Like me? See my Amazon wishlist
    MODx "Most Promising CMS" - so appropriate!

    PaulGregory Reply #5, 4 years, 11 months ago

    Reply
    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?



  • 15987
    784

    kjaebker Reply #6, 4 years, 11 months ago

    Reply
    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.


  • 22303
    9,586
    - MODX Chief Architect

    Jason Coward | MODX Chief Architect

    jasoncoward.com | @drumshaman

    opengeek Reply #7, 4 years, 11 months ago

    Reply
    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[perator]' => 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.


  • 23491
    993
    Mike Reid - www.pixelchutes.com
    MODx Team Member / Contributor
    MultiMedia Manager / SiteSearch / DocPassword / EditArea / We support FoxyCart
    ________________________________
    Where every pixel matters.

    pixelchutes Reply #8, 4 years, 11 months ago

    Reply
    Awesome stuff, Jason. I love it!


  • 715
    15

    airoctive Reply #9, 3 years, 6 months ago

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


  • 22303
    9,586
    - MODX Chief Architect

    Jason Coward | MODX Chief Architect

    jasoncoward.com | @drumshaman

    opengeek Reply #10, 3 years, 6 months ago

    Reply
    Quote from: airoctive at Jul 12, 2008, 02:55 PM
    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 (=):
    $events= $xpdo->getCollection('Events', array ('id:IN' => '(2080,2081,2082,2083,2084,2085)'));

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