Re: DB_DataObject Joins

Justin Thank you very much works like a charm, and as suspected it was something as easy as making sure the ordering of the joins was correct :) Be nice to see this in the examples page for those that have brain freezes every now and again. I have added to my wiki anyway http://wiki.triangle-solutions.com/index.php/PEAR_DB_DataObject Regards Ian Warner Director Triangle Solutions Ltd Justin Patrin wrote:

On 8/12/06, Ian Warner <iwarner@triangle-solutions.com> wrote:

I have the following code to join three tables:

     $sql1 = DB_DataObject::factory('Sms_shortcodes');
     $sql2 = DB_DataObject::factory('Sms_keywords');
     $sql3 = DB_DataObject::factory('Sms_services');
     $sql1->joinAdd($sql2, 'LEFT');
     $sql2->joinAdd($sql3, 'LEFT');

     $sql1->shortcode = $shortcode;
     $sql1->find();

I want to emulate:

     $sql1 = "SELECT *
               FROM sms_shortcodes A, sms_keywords B, sms_services C
              WHERE A.shortcode = '" . $shortcode . "'
                AND A.shortcode_id = B.shortcode_id
                AND B.service_id = C.service_id";

As you can see Sms_shortcodes is linked to Sms_keywords and Sms_keywords linked to Sms_services this seems impossible to do in DO using the join ADD as the I must use $sql2->joinAdd($sql3, 'LEFT'); but then this does not get added the query construct I need to use $sql1->joinAdd($sql3, 'LEFT'); but then it links to the wrong table. With the above DO i get: DO_Sms_services: CONNECT: Checking for database database_ in options DO_Sms_shortcodes: ERROR: joinAdd: sms_services has no link with sms_shortcodes DO_Sms_shortcodes: find: DO_Sms_shortcodes: QUERY: SELECT * FROM sms_shortcodes LEFT JOIN admin.sms_keywords ON admin.sms_keywords.shortcode_id=sms_shortcodes.shortcode_id WHERE ( sms_shortcodes.shortcode = '80508' ) Any help aqppreciated I may just be missing something very obvious, but it seems to me you can only join two tables or join many tables that are all linked to the main parent.

For one join, call joinAdd on the DO you're going to query with. For a linked join call joinAdd on the middle table's DO, then call joinAdd on the main table's DO. keywords->joinAdd(services) shortcodes->joinAdd(keywords) shortcodes->find()