->joinAdd()

->joinAdd() – add another dataobject to build a create join query

Synopsis

void $DB_DataObject->joinAdd ( object $dataobject , string $joinType , string $joinAs , string $joinCol )

Description

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.

    using specific join Columns

    
    user->friend (is a id of a person)
    user->mother (is a id of another person)

Note

This function can not be called statically.

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..

Example

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
}
?>

Resulting SQL

        
SELECT * FROM image
  LEFT JOIN product_image 
    ON (image.id = product_image.image_id)
  WHERE product_image.id = 24

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
}
?>

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);
Build the select component of a query (usually for joins) (Previous) Automatic Setters and Getters using overload (Next)
Last updated: Sat, 16 Feb 2019 — Download Documentation
Do you think that something on this page is wrong? Please file a bug report.
View this page in:
  • English

User Notes:

Note by: avkachalov@gmail.com
There is a possibility to join using complex conditions. You can pass second and other arguments as an associative array and an extra argument useWhereAsOn becomes availabile in that case.

See sources for full documentation. Quote:

/*
If second Argument is array, it is assumed to be an associative
array with arguments matching below = eg.
'joinType' => 'INNER',
'joinAs' => '...'
'joinCol' => ....
'useWhereAsOn' => false
*/
Note by: alan@akbkhome.com
You may want to use $do->selectAs($joinedObj, 'prefix_%s') with the above example.
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/