Re: DataObject Code Snippet using addJoin

in more recent versions AFAIR whereAdd() resets the where, along with returning what was there already. so: $w1 = clone($do); // THESE SHOULD BE ESCAPED, UNLESS YOU TRUST THE DATA? $w1->whereAdd("category_id = {$category[0]}"); $w1->whereAdd("category_id = {$category[1]}","OR"); $w1->whereAdd("category_id = {$category[0]}"); $w2 = clone($do); $w2->whereAdd("{$search_date} >= c.date_begins"); $w2->whereAdd("{$search_date} <= c.date_ends", "AND"); $do->whereAdd($w1->whereAdd()); $do->whereAdd($w2->whereAdd(),"AND"); One thing to note is that Mysql (I know for sure) does not optimize WHERE queries with JOINS, so if you can move a condition to JOIN, it often speeds up queries no end: eg. SELECT ....... FROM sometable JOIN anothertable ON anothertable.link_id = sometable.id AND anothertable.category in (1,2,4) WHERE ..... unfortunatly DataObjects joinAdd() method doesnt support this directly, so the best alternative is to write methods like function doXyzJoin() { $this->_join .= " JOIN nothertable ON anothertable.link_id = sometable.id AND anothertable.category in (1,2,4) " } * Now if someone wants to send me a patch to the manual :) Regards Alan Ed Greenberg wrote:

I went looking to do a job with DataObject and could not find suitable examples for building a (not very) complex SQL statement, so herewith I'm posting my working results. If somebody wants to modify this and add it to a document somewhere, I'm happy to have helped. What I wanted to do in pseudo-sql:

    select *
    from
        item i,calendar_detail c
    where
        (
            category_id = {$category[0]}
            or category_id = {$category[1]}
            or   etc...
            or category_id = {$category[n]}
        )
        and
            i.item_id = c.item_id
        and
        (
            {$search_date} >= c.date_begins
            and {$search_date} <= c.date_ends
        )
        and i.region_id = {$region_id}

What I wound up with (pseudo-php):

    $item = DB_DataObject::factory('item');
    $subItem = DB_DataObject::factory('calendar_detail');
    $item->joinAdd($subItem,"");    // neither inner, outer, left nor right join
    $item->whereAdd('region_id = '.$region_id);
    $item->whereAdd("calendar_detail.date_begins <= '".$search_date."'");
    $item->whereAdd("calendar_detail.date_ends >= '".$search_date."'");
    // need whereAdd for each subcategory_id whose item_class == calendar
    $orclause = "AND (";    // note the paren in the orclause on the first time through
    foreach($categories as $category_id) {
        $item->whereAdd("category_id = ".$category_id, $orclause);
        $orclause = "OR";    // note that the or clause has changed
    }
    $item->whereAdd(")","");    // note the closing paren
    $item->orderBy("post_date");
    $rows=$item->find();
    // now go iterate through the result set with $item->fetch...

It was truly amazing to me that I could build the parenthesized OR that way. It was better than constructing my own SQL since I would have had to join a bunch of $category_id = statements as we iterated through the $categories array. I am not sure how clear this is, or how helpful, but I've gotten so much help on this list, that I thought I'd give back a bit, even if I am only giving confusion :) </edg>