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.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

General Questions /

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

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

DataObject::get works but not SQLQuery()?

Go to End

8 Posts   3473 Views


Community Member, 36 Posts

8 April 2011 at 4:17pm

Edited: 08/04/2011 4:18pm

I have a simple table set up like this:

1. hello 100 30
2. hellp 100 32
3. help 25 32

I 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);

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();

$data = $sqlQuery->execute();

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?


Forum Moderator, 1391 Posts

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


To convert, maybe do something like this:

$myDataObjectSet = singleton('testtable')->buildDataObjectSet($data);


Forum Moderator, 1899 Posts

8 April 2011 at 9:32pm


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);
			$rec = $records->next();

		return $dos;


Forum Moderator, 1391 Posts

8 April 2011 at 9:50pm

Yep, SilverStripe documentation getting better and better :-)


Community Member, 36 Posts

11 April 2011 at 11:05am

Edited: 11/04/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


Community Member, 473 Posts

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.


Forum Moderator, 1391 Posts

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

>>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...


Forum Moderator, 1899 Posts

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.