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.

General Questions

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

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

DataObject::get works but not SQLQuery()?


Reply

8 Posts   2358 Views

Avatar
BlueScreen

8 April 2011 at 4:17pm (Last edited: 8 April 2011 4:18pm), Community Member, 36 Posts

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

8 April 2011 at 9:25pm Forum Moderator, 1095 Posts

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

8 April 2011 at 9:32pm Forum Moderator, 1796 Posts

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

8 April 2011 at 9:50pm Forum Moderator, 1095 Posts

Yep, SilverStripe documentation getting better and better :-)

Avatar
BlueScreen

11 April 2011 at 11:05am (Last edited: 11 April 2011 11:36am), Community Member, 36 Posts

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
simon_w

11 April 2011 at 3:55pm Forum Moderator, 474 Posts

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

11 April 2011 at 10:26pm Forum Moderator, 1095 Posts

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

11 April 2011 at 10:50pm Forum Moderator, 1796 Posts

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.