SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PEAR::DB_DataObject and many-to-many relationships

    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    In front of my computer
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :
    PHP Code:
    /**
     * 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;

    participate to the best Php Wiki
    my blog ...

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    the many-to-many relationship

    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?

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    In front of my computer
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :
    PHP Code:
    $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..
    participate to the best Php Wiki
    my blog ...

  5. #5
    SitePoint Enthusiast konky2000's Avatar
    Join Date
    Mar 2003
    Location
    Oakland
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Konky 2000 Collections - Japanese stickers and floaty pens

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    In front of my computer
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    participate to the best Php Wiki
    my blog ...

  7. #7
    SitePoint Enthusiast konky2000's Avatar
    Join Date
    Mar 2003
    Location
    Oakland
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Konky 2000 Collections - Japanese stickers and floaty pens

  8. #8
    SitePoint Member
    Join Date
    Apr 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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'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.

    PHP Code:
        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!

    Quote Originally Posted by konky2000
    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

  9. #9
    SitePoint Member scorps's Avatar
    Join Date
    Jul 2004
    Location
    Estonia
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pgroves
    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.

    PHP Code:
         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

  10. #10
    SitePoint Member ngmather's Avatar
    Join Date
    Jun 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Going off the examples at the documentation for joinAdd(), and keeping in line with the getPictures() methods previously suggested, how about the following:

    PHP Code:
    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:
    PHP Code:
    [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.")

  11. #11
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Tampa, FL
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How about this one...

    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:

    PHP Code:
    $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?

  12. #12
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Tampa, FL
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind. I did it by manually writing the SQL and using media->query(); Took 20 minutes (just as the manual suggested it might...)

  13. #13
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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

    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 Code:
    <?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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •