21286 Posts in 5733 Topics by 2602 members
| Go to End | Next > | |
| Author | Topic: | 2135 Views |
-
Return joined data from two tables
3 June 2010 at 4:02pm
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
-
Re: Return joined data from two tables
3 June 2010 at 7:45pm
<% control Openhouses %>
....
<% end_control >%
-
Re: Return joined data from two tables
3 June 2010 at 7:48pm
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
-
Re: Return joined data from two tables
3 June 2010 at 11:02pm
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
-
Re: Return joined data from two tables
3 June 2010 at 11:10pm Last edited: 3 June 2010 11:20pm
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
-
Re: Return joined data from two tables
4 June 2010 at 2:48pm
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',
); -
Re: Return joined data from two tables
4 June 2010 at 2:55pm Last edited: 4 June 2010 2:58pm
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
-
Re: Return joined data from two tables
4 June 2010 at 8:45pm
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
| 2135 Views | ||
| Go to Top | Next > |
