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.

Data Model Questions

Custom SQL Query via SQLQuery()-> Bug?


Reply

6 Posts   3389 Views

Avatar
cabby

14 August 2009 at 7:17pm (Last edited: 14 August 2009 7:18pm), Community Member, 15 Posts

Hi there,
I use the SQLQuery() from time to time for some custom queries.
Had to recognize, that if you choose multiple tables you have to add ',' inside the FROM Strings inside the from array.

Here is an example to see what I mean:
$sqlQuery->select = array(
'Referencefile.Name',
'Referencefile.Description',
'File.Filename',
'Referencefile.URL',
'Referencefile.ID as id'
);
$sqlQuery->from = array(
'Referencefile,',
'File,',
'SiteTree'
);

$sqlQuery->where = array(
'Referencefile.Bildtyp="Referenzbild"',
'Referencefile.ReferencePageID='.$referenz['id'],
'Referencefile.AttachmentID=File.ID',
'Referencefile.active = 1',
'SiteTree.ID=Referencefile.ReferencePageID'
);

As you can see I had to use
$sqlQuery->from = array(
'Referencefile,',
'File,',
'SiteTree'
);

If I don't insert the ',' in 'Referencefile,' and File,', the real resulting SQL String look something like that:
...
SELECT ......
FROM Referencefile File SiteTree
WHERE
...

But it has to be
...
SELECT ......
FROM Referencefile, File, SiteTree
WHERE
...

I could only trigger that by adding ',' like in my example above. Is that normal behaviour or instead a little bug in SQLQuery() function?
Tested with Version 2.3.0 and 2.3.3.

Thanx a lot to make that clear!
Silverstripe is a great CMS and we all should make it even better!

cabby

Avatar
Hamish

15 August 2009 at 12:34pm Community Member, 712 Posts

Looks like a bug. Adding a ticket and patch now.

Avatar
Hamish

15 August 2009 at 12:41pm Community Member, 712 Posts

See [url=http://open.silverstripe.com/ticket/4491]4491[/url].

Avatar
cabby

17 August 2009 at 9:44pm Community Member, 15 Posts

Great! Thanks for fixing this.

Avatar
Sanchez

18 August 2009 at 10:32pm Community Member, 6 Posts

Had some tricky times with INNER JOINS and the SQLQuery()

There could be a better way to do this.. better suggestions are flam'n welcome!
I just hope this code helps someone out

      $sqlQuery = new SQLQuery();
      $sqlQuery->select = array( //Gets the column
         'Title'
      );
      $sqlQuery->from=array( //joins Group with Group_Members
         '`Group`',
         'INNER JOIN `Group_Members` ON Group.ID = Group_Members.GroupID'
      );
      $sqlQuery->where=array( //Selects Titles only for GroupIDs that equal 2
         'Group.ID=2'
      );
      $rawSQL = $sqlQuery->sql();
      $result=$sqlQuery->execute(); //executes the query

      foreach($result as $row){
         $dataResult=$row['Title']; //Places Title result into a string variable
      }

Avatar
cabby

19 August 2009 at 7:31pm Community Member, 15 Posts

Whoops. Ingo made clear, that the FROM part of the SQLQuery() Function may contain JOINS. So it wouldn't be a good idea to fix it like you did.
Here is what he wrote:

---
Thats because $from also contains JOIN statements. To my knowledge, its not valid SQL to comma-separate those, right?

If you want to select from multiple tables in a implied cross join (SELECT * FROM TableA, TableB), you'll need to get pass the full string in a single from array value:

$q = new SQLQuery();
$q->from[] = 'TableA, TableB';

Alternatively, use CROSS JOIN explicitly.
---

Thanks Ingo, now I think I know how to deal with these kind of queries inside Silverstripe! I'm not a big MySQL expert, but that should help me.

Please close that thread.