->joinAdd() -- add another dataobject to build a create join query
Beschrijving
Builds a Join Query, by adding another dataobject to this one. Be careful when using this,
raw queries may be clearer than using joinAdd.
Thanks to Stijn de Reede for the implementation of this.
Parameter
object $obj - joining object (no value resets the join)
string $joinType - "LEFT" | "INNER " | "RIGHT" | ""
INNER is default, "" indicates
just select ... from a,b,c with no join and
links are added as where items.
Note: 'LEFT' is the same as LEFT OUTER.
string $joinAs - if you want to select the table as anther name
useful when you want to select multiple columns from a secondary table.
string $joinCol - The column on This objects table to match,needed
if this table links to the child object in
multiple places eg.
Note
Deze functie kan niet statisch worden aangeroepen.
The Examples below are not tested, use DB_DataObject::debugLevel(1), to see what
exactly is going on when you use this, and send the author some better examples..
Voorbeeld
Voorbeeld 35-2. Simple simple Join
<?php
// (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
}
?>
|
|
Voorbeeld 35-4. More Complex Join query
<?php
// 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
}
?>
|
|
Voorbeeld 35-5. Resulting SQL SELECT * FROM image
LEFT JOIN product_image
ON (image.id = product_image.image_id)
LEFT JOIN productgroup
ON (image.id = productgroup_image.image_id); |
|
|
->selectAs() (Previous)
|
(Next) ->set*() and ->get*()
|
|
|
Download Documentation
|
Last updated: Mon, 02 Jul 2007 |
|
Do you think that something on this page is wrong? Please file a bug report or add a note.
|
| User Notes: |
Note by: johng@neutralize.com
This took me ages and ages to figure out...
You can do some nice JOINs, so in the above example we can grab the image, product name and group name:
// -------------------------------------------------
// an example with 2 joins
// get all the images linked with products or productgroups
// and the product name and group name.
$i = new DataObject_Image();
$pi = new DataObject_Product_image();
$pgi = new DataObject_Productgroup_image();
$i->joinAdd($pi);
$i->joinAdd($pgi);
$i->selectAdd();
$i->selectAdd( 'image, product.name as p_name, product_group.name as g_name' );
$i->find();
while ($i->fetch()) {
echo $i->image;
echo $i->p_name;
echo $i->g_name;
}
// -------------------------------------------------
Note how the image object has new attributes called p_name and g_name? How cool is that?!
You may need to look at the syntax of addJoin, because you can also do:
// -------------------------------------------------
$i = DB_DataObject::factory('Items');
$status = DB_DataObject::factory('Status');
// is the join in your database.links.ini file? If not use:
// $i->joinAdd( $status, "LEFT", 'status', 'fk_status' );
// it is in the .ini file so use:
$i->joinAdd( $status, "LEFT" );
$i->joinAdd( $status, "LEFT", 'minor_status', 'fk_minor_status' );
$i->selectAdd( 'item.name as name, status.name as status, minor_status.name as minor_status' );
// -------------------------------------------------
This assumes that Items has two foreign keys, both to status (fk_status and fk_minor_status)
I have used 'LEFT' joins because no all items will have a status (it is null) but we still want to list that item.
Don't forget: DB_DataObject::debugLevel(1); is your best friend ever, please invite him to the party and make sure he has a drink.
I hope this helps someone out. I think these type of JOINs are a basic part of SQL, so it's nice to see them included in DB_DataObject. I have logged this a documentation bug, so I hope this may be fixed one day...
monk.e.boy
http://teethgrinder.co.uk/open-flash-chart/
|
|
