MySQL-specific querys

☆ A M B ☆
24,524 Posts

sottwell Reply #1, 18 years, 1 month ago

I’m working on getting all of the action, processor, and include scripts using the dbapi. Most of the queries are pretty straightforward, and are easily converted to use dbapi functions. But some of them are more complex, and I don’t know how to get them converted. I’m concerned about just leaving the $sql variables as they are and using $modx->db->query($sql), because I suspect that many of them use MySQL-specific syntax, and that would defeat the purpose of the whole exercise, which is making MODx capable of using other RDMS’s by modifying the dbapi file.

Here’s an example:

	$sql = 'SELECT groupnames.*, users.id AS user_id, users.username user_name '.
	       'FROM '.$tbl_membergroup_names.' AS groupnames '.
	       'LEFT JOIN '.$tbl_member_groups.' AS groups ON groups.user_group = groupnames.id '.
	       'LEFT JOIN '.$tbl_manager_users.' AS users ON users.id = groups.member '.
	       'ORDER BY groupnames.name';

How could this query be re-written to work in the dbapi select() function?

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

MODX Staff
10,725 Posts

opengeek Reply #2, 18 years, 1 month ago

This is one of the major reasons I developed xPDO. IMHO, the DBAPI is simply not a robust enough solution to handle abstracting the complexities of many of these queries. The DBAPI is also heavily based on the mysql extension and would need a lot of work to really make it database independent. This is why each class that defines a table in xPDO has a subclass (or derivative class) that is specific to the database engine. If a particular query needs to be modified for a particular database engine, you simply "override" a function in that engine’s subclass. This detail is never exposed to the developer who is using your API; it all takes place behind the scenes, making it super easy to develop with. This has the added benefit of allowing you to optimize the behavior of individual functions for a specific database engine (e.g. using the MySQL-specific REPLACE statement), without having to copy-and-paste an entire file and modify it for each one (which would simply be ignoring the "DRY", or "Don’t Repeat Yourself" principle).

xPDO also includes xPDOQuery, which provides functions to perform various kinds of JOINs, ORDER BY and other statements in simple programmatic ways. The queries become very easy to construct in a script, and are automatically abstracted for each database engine. Here are a few examples; first, a simple example of getting all the user groups and sorting them based on criteria passed in the request...

<?php
$c = $modx->newQuery('modUserGroup');
$c->sortby($_REQUEST['sort'],$_REQUEST['dir']);
$groups = $modx->getCollection('modUserGroup',$c);
?>

...or a more complex example demonstrating some of the more advanced query building capabilities (this retrieves a single "Catalog" along with all of the "Items" it is associated with and the details of each "Item"...

<?php

// ...

$catalogCriteria = $model->newQuery('cpCatalog', $catalogId);
$catalogCriteria->bindGraph('{"Items":{"Item":{}}}');
$catalogCriteria->where(array('Item.active' => 1));
$catalogCriteria->limit($pageLimit, $pageOffset);
$catalog = $model->getObjectGraph('cpCatalog', '{"Items":{"Item":{}}}', $catalogCriteria);

// ...
?>

Note that the bindGraph() function is a shortcut for using JOINs using the relationships that can be defined between tables in your xPDO model that represents them.
...and finally an even more complex scripted query...

<?php

// ...

$catalogCriteria = $model->newQuery('cpCatalogItemXref', array('catalog' => $catalogId));
$catalogCriteria->bindGraph('{"Item":{}}');
if ($hideUnaffordable) {
    $catalogCriteria->innerJoin('cpCatalogItemAttribute', 'attr', array(
        "attr.item = Item.id",
        "attr.name = 'cost'",
        "CAST(attr.value AS UNSIGNED INTEGER) <= {$cost}",
    ));
}
$catalogCriteria->where(array(
    'Item.active' => 1
));

$countCriteria = $model->newQuery('cpCatalogItem', array('active' => 1));
    $countCriteria->innerJoin('cpCatalogItemXref', 'xref', array(
        "xref.catalog = {$catalogId}",
        "cpCatalogItem.id = xref.item",
    ));
if ($hideUnaffordable) {
    $countCriteria->innerJoin('cpCatalogItemAttribute', 'attr', array(
        "attr.item = cpCatalogItem.id",
        "attr.name = 'cost'",
        "CAST(attr.value AS UNSIGNED INTEGER) <= {$cost}",
    ));
}
$itemCount = $model->getCount('cpCatalogItem', $countCriteria);
$modx->setPlaceholder("catalog.itemCount", $itemCount);

$catalogCriteria->limit($pageLimit, $pageOffset);

// uncomment these 2 lines to view the sql that is prepared
//$catalogCriteria->prepare();
//print_r($catalogCriteria->sql);

$catalogs = $model->getCollectionGraph('cpCatalogItemXref', '{"Item":{}}', $catalogCriteria);

// ...

?>

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

☆ A M B ☆
24,524 Posts

sottwell Reply #3, 18 years, 1 month ago

That being the case, then I think I’ll just drop this project. If I’m going to get into all that, I might just as well skip Evolution and dive into Revolution. It would be easier to just rewrite the whole Manager to use SQLite and package it up as an alternative to MODx Evolution (MySQL), and with Revolution so close to getting into beta stages it’s just not worth it.

Somehow I have had for many years the impression that SQL was supposed to be a single language, cross-platform and all that. Then I find out that just about every database has its own way of doing things... like a simple LEFT JOIN in some is LEFT OUTER JOIN in others, and in Oracle it’s a + after the table you want to join!

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