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

<% control Openhouses %>

....

<% end_control >%

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

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

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

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

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

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