On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • I just wanted to start a general thread to discuss the benefits of using xPDO over writing SQL directly in your applications. A recent question by someone here in the forums led me to write this post on the subject at my blog. I'd love to hear in this thread what other benefits you think using xPDO has over writing SQL directly for your MODX add-ons or other PHP database applications.
    • Oh geez, so many great things about xPDO


      • Built in security for scrubbing data
      • Speed to generate a query and customize across various functions
      • Collection Graph - once you get the hang of this why would you not want to use it
      • Speed - performance is great since all query are built to the highest of query language standards
      • Portable across installations
      • Not to mention all the others you mentioned in your post

      xPDO ROCKs! thanks
        Evo Revo // Ubuntu, CentOS, Win // Apache 2x, Lighttp (Lighty)
        Visit CharlesMx.com for latest news and status updates.
      • I am way too orientated with xPDO nowadays that it is hard to remember how it was before to be honest. Even I still do fall back to PDO queries with xPDO when I need big datasets and do not need the objects with them,

        But what gives the kicks out of xPDO for me:

        Database agnostic approach for (pretty much) everything (was mentioned in the blog post), but gonna point out few specific things

        But what it did not mention was the actual benefactors that many people miss. For example, Oracle (not official) and Sql Server do not have LIMIT like MySQL and SQLite has. With xPDOQuery the limiting is done in behalf of you, no need to add subqueries or funky stuff just to achieve something that sounds really simple, especially if coming from MySQL World.

        To same topic goes the schema value generated="native" which adds auto_increment (MySQL term) to your tables. No need to hassle with correct syntax for each platform separately. Hopefully will get Oracle support fully compatible soon as there is good example for this: as oracle has no auto increment, this needs to be done with triggers and sequences.

        The xPDOManager takes care of your schema, as long you got right database data types set and if you use standard ANSI SQL datatypes, you are 90% of the time safe and can use the same schema for multiple platforms or just clone it and tweak the changes. Just use createObjectContainer() and you are free to breath.

        Database agnostic field escaping, a pop thing. Commonly without using any abstraction model, most of us would write SQL queries similar to

        SELECT this_column, that_column FROM the_table WHERE this_column = 2


        With xPDO the query gets standardized with proper escaping just for field and table names. (The true one having table aliases etc.. also in the strings, escaped obviously.)

        MySQL: SELECT `this_column`, `that_column` FROM `the_table` WHERE `this_column` = 2
        Oracle: SELECT "this_column", "that_column" FROM "the_table" WHERE "this_column" = 2
        


        Extending objects and "extending" tables with single inherit model

        Something you gotta love is the fact that multiple objects can share the same table without any hassle with the objects themselves. Especially in the case of single inheritance when the child objects are quite powerful all together.

        What else? Oh yes, if you need to run complex queries and still want to have the objects loaded, you can do it. Just simply use xPDOCriteria($xpdo, "SELECT your_raw FROM sql_query .... and lots of complex stuff"); to create the criteria instead of xPDOQuery. This is not database agnostic approach, but with proper escaping with $xpdo->escape() and manipulating the string before hand per driver type, it is pretty easy.

        The criteria element for aggregate and composite joins

        Niftyyy!!! smiley

        I guess most would expect me to write something to this direction I wrote, there is lot of brilliant features there. Not just database agnostic querying, but also cache engines are are used with xPDO methods so you can switch between APC, Memcached and filecache when ever you want.

        Info/Debug/Error logging, quite efficient when you get used to it.

        ..... Could possibly have endless list of stuff here, but I've only written about 20% of the features that xPDO has with all my articles where most are really directed to queries. So do not even know all nifty features is might have. Even do know most of them, but have not studied them entirely.

        Sorry for bolds, had to highlight "headings" somehow
          Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX
        • I want to add that it's just plain *fun* to work with xPDO once you get comfortable with it (and that doesn't take very long). For me, thinking in xPDO is much smoother and more pleasant than thinking in MySQL or PDO.

          For MODXers, there's some information on using xPDO with MODX objects here: http://bobsguides.com/revolution-objects.html


          ------------------------------------------------------------------------------------------
          PLEASE, PLEASE specify the version of MODX you are using.
          MODX info for everyone: http://bobsguides.com/modx.html
            Did I help you? Buy me a beer
            Get my Book: MODX:The Official Guide
            MODX info for everyone: http://bobsguides.com/modx.html
            My MODX Extras
            Bob's Guides is now hosted at A2 MODX Hosting
          • stalemate resolution associate Reply #5, 7 years, 2 months ago
            Jason,

            Thank you for this post! I am that guy who's most comfortable with SQL, and I've never had a need to write database agnostic SQL until relatively recently, but I'm sold on it for sure!

            Thank you for outlining the benefits of this!

            Tom
            • I have a really stupid question. How does one use xPDO for something really simple, such as
              SELECT * FROM modx_system_settings
                Studying MODX in the desert - http://sottwell.com
                Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
                Join the Slack Community - http://modx.org
              • There is no stupid questions smiley

                as xPDOQuery object it would be next (using modx I believe)
                $criteria = $modx->newQuery('modSystemSetting');
                


                Thats it then smiley, the query actually is not "SELECT *" but "SELECT `className`.`fieldname`", but that has no importance (Except it can be faster than * query). Obviously the rows need to be fetched so using getObjectCollection() or getIterator() is required and can be used like next

                $results = $modx->getCollection('modSystemSetting');
                // OR
                $results = $modx->getIterator('modSystemSetting');
                
                //And then just go through each row
                foreach($results as $setting) {
                     //$setting has all the row stuff. You can access those with $setting->get('fieldname'); etc...
                }
                


                I guess not all the info was asked for but always good to have those, someone will come here anyways smiley
                  Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX
                • I am a self-taught PHP/MySQL/JavaScript programmer (which is probably one of my main issues), and I have been doing lots of web app development in PHP for about a decade now... only recently getting into more hefty OOP projects, though.

                  I have a current MODX project with custom tables for which I am using XPDO. I have tried the same thing in the past on a couple of other occasions but gave up (had to get the project out the door). I want so badly to have the kind of experience with XPDO that you guys are describing, but I have found the whole XPDO business to be the most confusing and difficult thing to grasp I have ever done with PHP. I don't know why, but the whole business of setting it up and getting everything to the point of being able to use the above "easy" commands, as well as maintaining and referencing said database, to make my head hurt every time I have to deal with it. Yes, I get database agnosticity, but I doubt that will ever be relevant to my current project (or, likely, any future project) written for a specific client. The extra hours and hours (for my stupid brain) spent setting it all up feels so not worth it. Having said that, I am determined to see this current project through to finish with XPDO, but I gotta say, it's been a huge painful mess for me to do so. I have no doubts that this is all (or at least mostly) due to my own inadequacies in development. Just wondering if there are any other folks out there brave enough to admit that they are in the same boat I am!

                  EDIT: I'm not sure what would make this situation any better for me other than an extra semester or two of computer science. I have the sense that the XPDO docs are simple enough, but I feel like I'm drowning every time I try to wade through them. [ed. note: debussy last edited this post 7 years, 2 months ago.]
                  • Debussy, I understand you completely. I started to play with xPDO bit over a year ago and could not twist my brain around it really. Always made database tables with mysql client or dumpfile and then reverse engineered them with Bob's snippet.

                    First off I used the xPDOCriteria object always to fetch objects with plain MySQL strings. That was so easy and still got the benefits of xPDO objects. To get to know xPDOQuery itself took a lot of time, can't remember why (some project most likely) or how I did learn them, but can remember that I had huge issues with it. And did dislike the approach all together. But as MODX being the main platform that I use when doing something, decided to force my will and spend some time with it. And it did not help that lot of stuff is still undocumented, where I try to fill the gap somehow (that was the why?) smiley
                      Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX
                    • Debussy, can you be more specific about what's confusing you?

                      One thing that helped me a lot was using MODX's xPDO methods to work with MODX objects like resources and chunks. That made the transition to using them with custom tables easier.

                      Since I knew how the MODX objects were related (e.g. Resources and their parents, children, resource groups, etc.), I could then look at the MODX schema (core/model/schema/modx.mysql.schema.xml) and see how the various, one-to-one (resource-parent), one-to-many (resource-children), and many-to-many (resources-resource groups) relationships are described.

                      Here are a couple more links that might help if you haven't seen them:

                      http://bobsguides.com/revolution-objects.html

                      http://bobsguides.com/custom-db-tables.html


                      The second one is nice if you have very simple custom tables that already exist. It will still work if there are relationships between the tables, but you have to edit the schema manually before generating the class and map files.


                      ------------------------------------------------------------------------------------------
                      PLEASE, PLEASE specify the version of MODX you are using.
                      MODX info for everyone: http://bobsguides.com/modx.html
                        Did I help you? Buy me a beer
                        Get my Book: MODX:The Official Guide
                        MODX info for everyone: http://bobsguides.com/modx.html
                        My MODX Extras
                        Bob's Guides is now hosted at A2 MODX Hosting