Jump to:

23379 Posts in 18298 Topics by 2867 members

General Questions

SilverStripe Forums » General Questions » DataObject::get works but not SQLQuery()?

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

Page: 1
Go to End
Author Topic: 2298 Views
  • BlueScreen
    Avatar
    Community Member
    36 Posts

    DataObject::get works but not SQLQuery()? Link to this post

    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?

  • martimiz
    Avatar
    Forum Moderator
    1068 Posts

    Re: DataObject::get works but not SQLQuery()? Link to this post

    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

  • swaiba
    Avatar
    Forum Moderator
    1786 Posts

    Re: DataObject::get works but not SQLQuery()? Link to this post

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

  • martimiz
    Avatar
    Forum Moderator
    1068 Posts

    Re: DataObject::get works but not SQLQuery()? Link to this post

    Yep, SilverStripe documentation getting better and better

  • BlueScreen
    Avatar
    Community Member
    36 Posts

    Re: DataObject::get works but not SQLQuery()? Link to this post

    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

  • simon_w
    Avatar
    Forum Moderator
    471 Posts

    Re: DataObject::get works but not SQLQuery()? Link to this post

    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.

  • martimiz
    Avatar
    Forum Moderator
    1068 Posts

    Re: DataObject::get works but not SQLQuery()? Link to this post

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

  • swaiba
    Avatar
    Forum Moderator
    1786 Posts

    Re: DataObject::get works but not SQLQuery()? Link to this post

    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.

    2298 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.