Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions

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

Return joined data from two tables


Go to End
Reply

10 Posts   3022 Views

Avatar
B-Side

3 June 2010 at 4:02pm Community Member, 42 Posts

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

Avatar
biapar

3 June 2010 at 7:45pm Forum Moderator, 435 Posts

<% control Openhouses %>

....

<% end_control >%

Avatar
B-Side

3 June 2010 at 7:48pm Community Member, 42 Posts

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

Avatar
mark_s

3 June 2010 at 11:02pm Community Member, 78 Posts

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

Avatar
Sean

3 June 2010 at 11:10pm (Last edited: 3 June 2010 11:20pm), Forum Moderator, 921 Posts

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

Avatar
B-Side

4 June 2010 at 2:48pm Community Member, 42 Posts

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',
   );

   

Avatar
Sean

4 June 2010 at 2:55pm (Last edited: 4 June 2010 2:58pm), Forum Moderator, 921 Posts

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

Avatar
mark_s

4 June 2010 at 8:45pm Community Member, 78 Posts

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

Go to Top