Jump to:

23377 Posts in 18296 Topics by 2867 members

General Questions

SilverStripe Forums » General Questions » Return joined data from two tables

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1 2
Go to End
Author Topic: 2912 Views
  • B-Side
    Avatar
    Community Member
    42 Posts

    Return joined data from two tables Link to this post

    Hi there - I'm trying to perform an SQL query with a join. Function below:

    OpenhousePage.php:

    function Openhouses() {

       $result = DataObject::get("PropertyPage",
          "('$date' = `Openhouse`.ViewDate )",
          "`Openhouse`.ViewTime",
          "LEFT JOIN `Openhouse` ON `PropertyPage`.ID = `Openhouse`.PropertyPageID",
    ""
          );         
       return $result;   
       }

    This function is returning the correct PropertyPage data, but the data from Openhouse is not available in the template.

    How do I return all the data from a joined query?

    Many thanks in advance,

    Jayne

  • biapar
    Avatar
    Forum Moderator
    435 Posts

    Re: Return joined data from two tables Link to this post

    <% control Openhouses %>

    ....

    <% end_control >%

  • B-Side
    Avatar
    Community Member
    42 Posts

    Re: Return joined data from two tables Link to this post

    Thanks Biapar - that's what I'm using, but it's only returning the data from the PropertyPage Object, not from the Openhouse Object. I think I'm missing some join syntax to gather the data from both?

    Cheers, Jayne

  • mark_s
    Avatar
    Community Member
    78 Posts

    Re: Return joined data from two tables Link to this post

    Hi.

    DataObject::get() always returns objects of the named class (first parameter). You can specify a join in order to help select those objects, but it won't include the joined tables, or even fields from the joined tables. Moreover, if the join causes duplicate objects of the returned class, DataObject::get() will ensure there are no duplicate objects. A key distinction is made between objects and database records, and get() returns the former.

    If you want to work with the results of a join, you can use DB::query() and specify the SQL, and it returns an SS_Query. It extends Iterator, and has a variety of methods for accessing result rows.

    Mark

  • Sean
    Avatar
    Forum Moderator
    921 Posts

    Re: Return joined data from two tables Link to this post

    If you can, getting data using the built in ORM for object relationships might be useful.

    e.g.

    PropertyPage.php:

    class PropertyPage extends Page {

       public static $has_many = array(
          'Openhouses' => 'Openhouse'
       );

       // This overloads the default relationship getter with a custom date query
       // See DataObject::getComponents() for the parameters you can provide to the relationship getter
       public function getOpenhouses() {
          return $this->getComponents('Openhouses', 'Date = '12/12/2000', 'Date DESC');
       }

    }

    Openhouse.php:

    class Openhouse extends DataObject {

       public static $has_one = array(
          'PropertyPage' => 'PropertyPage'
       );

    }

    Code above is simplified for brevity, I've also made an assumption Openhouse is a DataObject subclass, but it could be a Page subclass also. Also, the relationship between PropertyPage and Openhouse could be a many-to-many relationship, but the above could be altered to fit that also, using many_many and belongs_many_many instead of has_many and has_one.

    Essentially the key is having getOpenhouses() on PropertyPage which overrides the default relationship getter, and uses the ORM instead to filter the results, the second parameter is a WHERE clause on getComponents() or getManyManyComponents() if it's a many-to-many relationship.

    Using the above, <% control Openhouses %> would become available in the PropertyPage template, and this lists all related Openhouse objects that are related to that PropertyPage, but instead it's filtered by the getOpenhouses() defined on PropertyPage.

    Hope this helps.

    Sean

  • B-Side
    Avatar
    Community Member
    42 Posts

    Re: Return joined data from two tables Link to this post

    Thanks so much to both of you! I've been working on the following, but I still keep coming back to the problem of returning the data from both Tables. Here's my latest code and any suggestions would be welcome. Thanks again, Jayne

    OpenhousePage.php

    function Openhouses() {
       $week_number = date('W');
       $year = date('Y');
       $day = date("Y-m-d", strtotime($year."W".$week_number."6"));

       $records = DB::query("SELECT *
       FROM `Openhouse`
       LEFT JOIN `PropertyPage` ON `Openhouse`.`PropertyPageID` = `PropertyPage`.`ID`
       WHERE `Openhouse`.`ViewDate` > '2010-05-01'
       ");         
          foreach($records as $record)
             $objects[] = new $record['ClassName']($record);
          if(isset($objects)) $doSet = new DataObjectSet($objects);
          else $doSet = new DataObjectSet();

       return $doSet;

       }

    PropertyPage.php

       public static $has_many = array(
          'Propertyimages' => 'Propertyimage',
          'Openhouses' => 'Openhouse'
       );

    Openhouse.php

       public static $has_one = array(
          'PropertyPage' => 'PropertyPage',
       );

       

  • Sean
    Avatar
    Forum Moderator
    921 Posts

    Re: Return joined data from two tables Link to this post

    Instead of SELECT * try using SELECT `Openhouse`.*

    The trouble you might still run into is with duplicate entries.

    A quick workaround for this is to call removeDuplicates() before returning the DataObjectSet to the template.

    $doSet->removeDuplicates();
    return $doSet;

    Sean

  • mark_s
    Avatar
    Community Member
    78 Posts

    Re: Return joined data from two tables Link to this post

    Hi.

    The query you've got now, and removing duplicates after, is pretty much what DataObject::get does with the join clause. But if you do this:

    $records = DB::query("SELECT `Openhouse`.*, `PropertyPage`.`SomeField`
    FROM `Openhouse`
    LEFT JOIN `PropertyPage` ON `Openhouse`.`PropertyPageID` = `PropertyPage`.`ID`
    WHERE `Openhouse`.`ViewDate` > '2010-05-01'
    ");
    foreach($records as $record)
    $objects[] = new $record['ClassName']($record);
    if(isset($objects)) $doSet = new DataObjectSet($objects);
    else $doSet = new DataObjectSet();

    return $doSet;

    ... you might just find that SomeField appears in resulting object. Not strictly speaking clean, since SomeField isn't declared in Openhouse, but give it a go. It might give you what you're after.

    One other comment: just be aware that if this code is executing in the front end, and PropertyPage is a descendant of Page (and is thereby versioned), you may need to change references to PropertyPage to PropertyPage_Live, if that's what you want.

    Mark

    2912 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.