PHP Pear - DataObject Code Snippet using addJoin

Author

DataObject Code Snippet using addJoin

Ed Greenberg

2005-01-31, 3:57 am

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>

Alan Knowles

2005-01-31, 8:57 pm

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>

Sponsored Links