SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Zend_Db_Table - Cross Table Joins

    So I was perusing through the documentation for Zend_Db_Table and I came across one of its method that allows you to run cross table joins.

    PHP Code:
    $select $user->select()->setIntegrityCheck(false);
              
    $select->from('Roles as r', array('us_id'))
                     ->
    join('Users as u''r.us_id = u.us_id')
                     ->
    where('r.us_id = ? '$id);
              
    print_r($user->fetchAll($select)->toArray()); 
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  2. #2
    SitePoint Zealot
    Join Date
    May 2008
    Location
    Montreal
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would you like to discuss this? Aside from it being an INNER join (whether or not it is explicit or implicit), is there something that you consider remarkable about how they are doing joins?

  3. #3
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Peter Goodman View Post
    Would you like to discuss this? Aside from it being an INNER join (whether or not it is explicit or implicit), is there something that you consider remarkable about how they are doing joins?
    mmm nothing remarkable. However, Zend_Db_Table is some what tricky. The table data gateway implementation forces Zend_Db_Table to operate solely on a single table, which is perfectly reasonable when writing and removing data. However, when retrieving data, you may need the ability to perform cross table joins.

    Attempting to join another table - while using Zend_Db_Table - with out setIntegrityCheck(false) will produce the following error

    Code:
    [message:protected] => Select query cannot join with another table
    You could get past this altogether and just instantiate Zend_Db_Select or write the query manually and pass it through to Zend_Db_Adapter_*->query. I personally prefer to work within my table classes rather those two options.
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  4. #4
    SitePoint Zealot
    Join Date
    May 2008
    Location
    Montreal
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does Zend_Db support views? (essentially they would be premade Zend_Db_Select instances that could then be added to)

  5. #5
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If the view is created within MySQL (for example) itself, then it should work fine.

    I usually create a getSelect() method in my models with a base select query with all the necessary fields or joins prepared. Though I usually avoid Zend_Db_Table and work with a new class / Zend_Db_Select instead.
    Adrian Schneider - Web Developer

  6. #6
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes.

    Code:
    CREATE TABLE t (qty INT, price INT);
    INSERT INTO t VALUES(3, 50), (5, 60);
    CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
    SELECT * FROM v;
    PHP Code:
    $result $db->query('SELECT * FROM v')->fetchAll(); 
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!


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
  •