PEAR::DB_DataObject and many-to-many relationships

[markusf]

Been looking at the DB_DataObject framework in the excellent PEAR package, and think it looks great.

But one thing is bugging me: I can't seem to find a good way of handling many-to-many relationships using the [databasename].links.ini file (or for that sake, the [dataobj]->getLink() function)

How do i best handle this within the DB_DataObject framework?

To illustrate i can show you an example:

3 tables:

news
- id
- name
- PRI KEY (id)

pictures
- id
- path
- PRI KEY (id)

news_pictures
- news_id
- picture_id
- PRI KEY (news_id, picture_id)

--- how do I pull out which active pictures belong to a specific news ?

In advance: thank you so much :-)

Markus

[zimba]

DB_DataObject doesn't have many-to-many facilities.

What I would do, is add getPictures() to the news class, and getNews() to the pictures class.

Here is a quick code (untested) illustration :

/**
 * Gets the pictures associated to the news
 *
 * @param string
 * @return DataObject_Pictures
 */
function getPictures($conditions = '')
{
  $pictures = DB_DataObject::factory('pictures');
  $pictures->whereAdd('id = news_pictures.pictures_id');
  $pictures->whereAdd('news_pictures.news_id = news.id');
  if (!empty($conditions))
  {
    $pictures->whereAdd($conditions);
  }
  $pictures->find();
  return $pictures;
} 

[markusf]

Thank you so much for your help!, however this data is in the news_pictures table. (so the same pictures can be used in different news).

that means that the getPictures method could first fetch the picture_id's from the lookuptable (news_pictures), and then fetch the pictures and return the correct pictures as iteratable picture-dataobject!?

Or are there other ways of doing this using the links.ini file?

[zimba]

The sample I gave does exactly this, just that it doesn't need to pass trough getting the picture id's from the news_picture because of the conditions.

What you get in return is a picture object.
You can then :

$news->get($news_id);
$picture = $news->getPictures();

while($picture->fetch())
{
  echo "<img scr=\"{$picture->filename}\" width=\"{$picture->width}\" height=\"{$pictures->height}\" />\n";
} 

links.ini is not usefull because DB_DataObject doesn't support many-to-many natively. Like I said before..

[konky2000]

Thank you. I was struggling with the exact same problem with DB_DataObject.

The lack of many-to-many support definately reduces the value of the links.ini file.

[zimba]

Yup, I don't know any PHP ORM that supports many to many relationsships (maybe MetaBase ?)

If you look for a good alternative in another language, look at ActiveRecord ( http://ar.rubyonrails.com ), it's written in Ruby. It can be used with the RubyOnRails framework.

[konky2000]

I'm having trouble with the example code. (I know it was more meant to illustrate a point, and wasn't meant to necessarily work.)

But it seems that DB_DataObject::whereAdd() only works for conditionals within a table.

To join multiple tables, it appears that you have to use DB_DataObject::joinAdd() instead.

I have to start to wonder about the value of DB_DataObject as a whole with such weak support for what should be a standard use of linked tables.

[pgroves]

I'm having trouble with the example code. (I know it was more meant to illustrate a point, and wasn't meant to necessarily work.)

But it seems that DB_DataObject::whereAdd() only works for conditionals within a table.

To join multiple tables, it appears that you have to use DB_DataObject::joinAdd() instead.

I'm a newcomer to DB_Dataobject myself, and have had similar problems with linked tables.

I think you're correct in that whereAdd() only works within a single table. However, I don't think that joinAdd() will help either, this is for joining objects, not tables. Or at least if you are to get joinAdd() to work, you need to do more than just replacing whereAdd() with joinAdd() in the example code. However, if someone can correct me with some examples of code, I would be a happy man!

Anyway, to get around this, instead of whereAdd, I've had to use a raw query, e.g.

    function getPictures($str_conditions = '') 
    { 
          $obj_pictures = DB_DataObject::factory('pictures'); 
          
          $str_query = "SELECT * FROM pictures,  news_pictures WHERE news_pictures.news_id  =  {$this->news_id}) AND pictures.id = news_pictures.pictures_id";
          
          if (!empty($str_conditions)) 
          { 
                $str_query .= " " . $str_conditions; 
          }           
           
          
          $obj_pictures ->query("$str_query");    
 
          return $obj_pictures; 
    } 

This works, but seems a bit "wrong" to me, as its not doing things in a very OO way, but I can't see how else to get this to work....

Anyway, if anyone has any better ideas OR has any comments about my method, I'd love to hear from you!

I have to start to wonder about the value of DB_DataObject as a whole with such weak support for what should be a standard use of linked tables.

I think it still looks fairly useful, though I think this does indeed weaken its value - after all what database *doesn't* have linked tables?

Hope this helps!

Paul

[scorps]

I'm a newcomer to DB_Dataobject myself, and have had similar problems with linked tables.

I think you're correct in that whereAdd() only works within a single table. However, I don't think that joinAdd() will help either, this is for joining objects, not tables. Or at least if you are to get joinAdd() to work, you need to do more than just replacing whereAdd() with joinAdd() in the example code. However, if someone can correct me with some examples of code, I would be a happy man!

Anyway, to get around this, instead of whereAdd, I've had to use a raw query, e.g.

     function getPictures($str_conditions = '') 
     { 
           $obj_pictures = DB_DataObject::factory('pictures'); 
           
         $str_query = "SELECT * FROM pictures, news_pictures WHERE news_pictures.news_id = {$this->news_id}) AND pictures.id = news_pictures.pictures_id";
           
           if (!empty($str_conditions)) 
           { 
                 $str_query .= " " . $str_conditions; 
           }           
            
           
           $obj_pictures ->query("$str_query");    
  
           return $obj_pictures; 
     } 

This works, but seems a bit "wrong" to me, as its not doing things in a very OO way, but I can't see how else to get this to work....

Anyway, if anyone has any better ideas OR has any comments about my method, I'd love to hear from you!



I think it still looks fairly useful, though I think this does indeed weaken its value - after all what database *doesn't* have linked tables?

Hope this helps!

Paul

I first looked also Dataobject but because of its weak and messy support for linked tables i chose MDB_Querytool instead. Its very comfortable to use

Mart

[ngmather]

Going off the examples at the documentation for joinAdd(), and keeping in line with the getPictures() methods previously suggested, how about the following:

function getPictures()
{
  $pictures = DB_DataObject::factory( 'pictures' );
  $news_pictures = DB_DataObject::factory( 'news_pictures' );
  $news_pictures->setNews_id( $this->getNews_id() ); 
  $pictures->joinAdd( $news_pictures ); 
  $pictures->find();
    
  return $pictures;
} 

And you'd need your links.ini set up with:

[news_pictures]
news_id = news:id
pictures_id = pictures:id 

I think that should do it... it seems slightly confusing to me, but seems to work. It does it in the slightly more abstracted fashion that someone wanted (although I note that the documentation actually says "Be careful when using this, raw queries may be clearer than using joinAdd.")

[hotgazpacho]

I have a similar, yet more complex problem. The main table is called media. I have another couple of tables, one provides a listing of keywords, and another maps keywords to media, similar to the original poster.

I want to enable a search of media by keyword, allowing for multiple keywords. For a query where ANY of the terms (OR) is acceptable, the following code snippet works just fine:

$keyword = DB_DataObject::factory('keyword');
foreach ($terms as $term) {
  $term = trim($term);
  // $data['values']['searchPage']['whereAdd'] is either OR or AND
  $keyword->whereAdd("keyword.term LIKE '%" . $keyword->escape($term) . "%'", $data['values']['searchPage']['whereAdd'] );
}

// Add the Joins
$media_has_keyword = DB_DataObject::factory('media_has_keyword');
$media_has_keyword->joinAdd($keyword);
$media->joinAdd($media_has_keyword);
$media->find(); 

This will not work when $data['values']['searchPage']['whereAdd'] = 'AND', because one keyword.term can never be like A and like B at the same time.

One way I thought about getting around this was creating a new $keyword object for each search term, and LEFT or RIGHT joining them to one $media_has_keyword. This did not work, but maybe I did not implement it correctly.

Any one have any thoughts on how I can use DB_DataObjects to require the media to have a mapping for ALL the keywords a user might specify?

[hotgazpacho]

Nevermind. I did it by manually writing the SQL and using media->query(); Took 20 minutes (just as the manual suggested it might...)

[tclineks]

Been looking at the DB_DataObject framework in the excellent PEAR package, and think it looks great.

But one thing is bugging me: I can't seem to find a good way of handling many-to-many relationships using the [databasename].links.ini file (or for that sake, the [dataobj]->getLink() function)

How do i best handle this within the DB_DataObject framework?

To illustrate i can show you an example:

3 tables:

news
- id
- name
- PRI KEY (id)

pictures
- id
- path
- PRI KEY (id)

news_pictures
- news_id
- picture_id
- PRI KEY (news_id, picture_id)

--- how do I pull out which active pictures belong to a specific news ?

In advance: thank you so much :-)

Markus

Ressurection!

Here is a fairly clean solution.

Using getLink/getLinks could give you results in object form but creates additional queries.

This assumes a link table with a news_id and a picture_id field.
links.ini:
[link_picture_news]
news_id = news:id
picture_id = picture:id

<?php
...
$link_picture_news = new Link_picture_news();
$news = new News();
$picture = new Picture();

// example where conditions
$news->id = 3;
$picture->active = 1; // assuming we have an active field

$link_picture_news->joinAdd($news);

$picture->joinAdd($link_picture_news);

if ($picture->find())
  while($picture->fetch()) {
    (handle picture here);
  } 
...
?>

This brings in redundant data from the news (and the link_picture_news) table -- if this is a problem look into selectAs() and you can prevent selection from the news table.