21282 Posts in 5730 Topics by 2601 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 1644 Views |
-
DataObject::get works but not SQLQuery()?
8 April 2011 at 4:17pm Last edited: 8 April 2011 4:18pm
I have a simple table set up like this:
1. hello 100 30
2. hellp 100 32
3. help 25 32I can retrieve the first two lines just fine using DataObject::get with this code:
$crit = "'hell%'";
$obj = "testtable";
$filter = "textfield1 LIKE ".$crit;
$sort = "";
$join = "";
$limit = "";
$data1 = DataObject::get($obj, $filter, $sort, $join, $limit);
Debug::dump($data1);BUT the problem with dataobject::get is that lacks the ability to select specific columns, COUNT or DISTINCT and I want to use the DISTINCT statement for the rest of my code
So I'm trying to use SQLQuery:
$crit = "'hell%'";
$sqlQuery = new SQLQuery();
$sqlQuery->select = array( '*',);
$sqlQuery->from = array("testtable",);
$sqlQuery->where = array("textfield1 LIKE ".$crit,);$rawSQL = $sqlQuery->sql();
Debug::dump($rawSQL);$data = $sqlQuery->execute();
Debug::dump($data);But for some reason the above code always results in nothing but a blank dataobjectset!
According to $rawSQL it tries to run this:
SELECT * FROM testtable WHERE (textfield1 LIKE 'hell%')
Which works just fine if I enter it in phpmyadmin! So why can't silverstripe produce any results? Can someone please point out what is wrong with my code?
-
Re: DataObject::get works but not SQLQuery()?
8 April 2011 at 9:25pm
If the query returns a result in phpmyadmin, it should return one in SilverStripe as well. One thing is - the SQLQuery object returns an Query obect instead of a DataObjectSet...
Try dumping the first item like this
var_dump($data->first());
To convert, maybe do something like this:
$myDataObjectSet = singleton('testtable')->buildDataObjectSet($data);
-
Re: DataObject::get works but not SQLQuery()?
8 April 2011 at 9:32pm
nice!
i've been using...
public static function GetDataObjectSetFromDBQuery($strSQL) {
$records = DB::query($strSQL);$dos = new DataObjectSet();
$rec = $records->next();
while ($rec) {
$do = new DataObject($rec);
$dos->push($do);
$rec = $records->next();
}return $dos;
} -
Re: DataObject::get works but not SQLQuery()?
8 April 2011 at 9:50pm
Yep, SilverStripe documentation getting better and better
-
Re: DataObject::get works but not SQLQuery()?
11 April 2011 at 11:05am Last edited: 11 April 2011 11:36am
Thanks martimiz, that worked like a charm.
EDIT: ...spoke too soon...I ran into a problem with that function when trying to select a single column. I get an error saying 'RecordClassName' is an undefined index
It looks like the function automatically inserts two extra columns called "recordclassname" and "classname" into your results which contain the name of the class while you call while running the SQLQuery. The function needs this in order to do the conversion.
I suspect it is only adding these column when you select '*'
Fortunately swaiba's code does not have this problem and I was able to use that too
-
Re: DataObject::get works but not SQLQuery()?
11 April 2011 at 3:55pm
ClassName is actually a column the ORM adds to each table.
If you have a look at the queries SilverStripe uses (append ?showqueries to the URL while in dev mode), you'll see something similar to:
.CASE WHEN "SiteTree"."ClassName" IS NOT NULL THEN "SiteTree"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
This was for a DataObject::get('SiteTree');
Basically, it's setting RecordClassName to either the ClassName column on the SiteTree table, if it is not null, or SiteTree otherwise. This goes in the SELECT part of a query, usually just before the FROM.
The main problem with using swaiba's code is that it doesn't create an object of the correct type (all objects are generic DataObjects), so any custom code you have in your objects won't be available.
-
Re: DataObject::get works but not SQLQuery()?
11 April 2011 at 10:26pm
So buildDataObjectSet() doesn't seem to be the right choice after all:it seeks for the baseclass everywhere, but NOT in $this->ClassName, it does let you set a ClassName as a fourth param and then never gets to use it because of a bug where it doesn't check on the existence of $record['RecordClassName']. (Anyone want to create a patch for that)?
This is basically what buildDataObjectSet() does (not much different from what Swaiba is doing):
$results = array();
foreach ($data as $record) {
$results[] = new SomeClass($record);
}
return new DataObjectSet($results);@simon:
>>it doesn't create an object of the correct type
that can be easily remedied by doing $do = new SomeDataObjectClass($rec); Maybe add the classname as a param to the function, or use $this->ClassName... -
Re: DataObject::get works but not SQLQuery()?
11 April 2011 at 10:50pm
ah - and I normally use that function when it not just a DataObjectSet stuff like counts, with group by and having clauses - so I'd not create the DataObjects as actual "ClassNames" I just want the DataObject/Set functionality to iterate over the results in the template.
| 1644 Views | ||
|
Page:
1
|
Go to Top |
