Quote from: mregan at Nov 12, 2010, 08:42 PM
Second, what are the advantages of creating an instance of the newQuery class and running these methods over writing an SQL statement?
They are more easily portable and optimizable for other database platforms. IOW, it allows us to abstract as much as possible easily and then override only when necessary to optimize a particular query or method for a specific database engine, typically where abstraction doesn’t make sense. These queries wrapped in metadata also allow for more intelligent and flexible caching features that can greatly reduce database loads in high-traffic situations. I also think the code is easier to read, and in general, doesn’t require those reading the code to know SQL.
Quote from: mregan at Nov 12, 2010, 08:42 PM
And third, is there any way I can just write a statement - similar to the old $modx->db->query($sql)
Yep, just use any of the PDO methods which are available directly from the $modx object, e.g.
<?php
$stmt = $modx->prepare("SELECT * FROM `food` WHERE `type` = ?");
if ($stmt && $stmt->execute(array('beer'))) {
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
}
$stmt = $modx->query("SELECT `name` FROM `food` WHERE `type` = 'beer'");
if ($stmt) var_dump($stmt->fetchAll(PDO::FETCH_COLUMN));
$affectedRows = $modx->exec("UPDATE `food` SET `rating` = 'EXCELLENT' WHERE `type` = 'beer'");
You can also take advantage of result set caching in xPDO by using xPDOCriteria directly (which xPDOQuery is a more advanced derivative form of), e.g.
<?php
$criteria = new xPDOCriteria($modx, "SELECT * FROM `food` WHERE `type` = :type", array('type' => 'beer'));
$stmt = $criteria->prepare();
if ($stmt && $stmt->execute()) {
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
}
Note you can also use raw SQL within the xPDOQuery criteria as well, assuming a custom class called myFood in an opengeek package, e.g.
<?php
$beers = $modx->getCollection('myFood', array(
array('rating' => 'EXCELLENT'),
"`type` LIKE '%beer%' OR `type` IN ('wine','spirits')"
));
MODX Staff
