Having great success with DB_DataObject. (Thanks Alan et al!)
I'm wondering how best to do a join where various fields are used for select and deep child ones for sort critera, like this.
SELECT * FROM tTrain tr, tSchedRun sr, tTime tm WHERE tr.date='2002-05-14' AND sr.id=tr.tSchedRun_id AND tm.id=sr.tTime_id ORDER BY tm.runTime;
Even if one fully uses table linking, it seems like there has
to be procedural code which also causes a lot of SQL
transactions, instead of doing it all in just one DB query.
have a look at the joinAdd() method in CVS, (and the last release)..
|
* joinAdd - adds another dataobject to this, building a joined query.
*
* example (requires links.ini to be set up correctly)
* // get all the images for product 24
* $i = new DataObject_Image();
* $pi = new DataObjects_Product_image();
* $pi->product_id = 24; // set the product id to 24
* $i->joinAdd($pi); // add the product_image connectoin
* $i->find();
* while ($i->fetch()) {
* // do stuff
* }
* // an example with 2 joins
* // get all the images linked with products or productgroups
* $i = new DataObject_Image();
* $pi = new DataObject_Product_image();
* $pgi = new DataObject_Productgroup_image();
* $i->joinAdd($pi);
* $i->joinAdd($pgi);
* $i->find();
* while ($i->fetch()) {
* // do stuff
* }
*
The original design docs are in here
http://cvs.php.net/co.php/pear/DB_DataObject/TODO
Regards
Alan
|
1. How to sort by "deep child" table columns?
In the case above, I'm selecting by a parent table value and
then sorting by a deep child value. The only way I can see
to accomplish the sort is to load the deep child id's into an array, then sort by them, and then query the whole mess
in that order again.
2. If you use the ::query method giving it something like the
above, how do you indicate what sort of object is returned?
I've tried replacing the "*" with something like
join(",", array_keys($t->_get_table()) to try to stuff the
results into my object of choice. No good.
3. If I have multiple nested levels of auto linked tables, is
there a way to say to an object, "recursively getLinks on all
your children?"
Eg, must I
while ($t->fetch()) { $t->getLinks();
$t->_fooField->getLinks();
$t->_fooField->_blahField->getLinks();
$t->_fooField->_blahField->_zorkField->getLinks();
....
}
If so, that seems like a waste of transactions since that's one for the
fetch and one more for each getLinks().
Maybe ::fetch or ::find could take a "recurse" arg?
TIA and Regards,
--
Can you help out?
Need Consulting Services or Know of a Job?
http://www.akbkhome.com