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've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

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   4013 Views

Avatar
BlueScreen

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

Avatar
martimiz

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

var_dump($data->first());

To convert, maybe do something like this:

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

http://doc.silverstripe.org/sapphire/en/reference/sqlquery

Avatar
swaiba

Forum Moderator, 1899 Posts

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

Avatar
martimiz

Forum Moderator, 1391 Posts

8 April 2011 at 9:50pm

Yep, SilverStripe documentation getting better and better :-)

Avatar
BlueScreen

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

Avatar
(deleted)

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.

Avatar
martimiz

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

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

Avatar
swaiba

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.