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.

Data Model Questions /

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

Custom SQL Query via SQLQuery()-> Bug?


Go to End


6 Posts   5172 Views

Avatar
cabby

Community Member, 15 Posts

14 August 2009 at 7:17pm

Edited: 14/08/2009 7:18pm

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

Community Member, 712 Posts

15 August 2009 at 12:34pm

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

Avatar
Hamish

Community Member, 712 Posts

15 August 2009 at 12:41pm

See 4491.

Avatar
cabby

Community Member, 15 Posts

17 August 2009 at 9:44pm

Great! Thanks for fixing this.

Avatar
Sanchez

Community Member, 6 Posts

18 August 2009 at 10:32pm

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

Community Member, 15 Posts

19 August 2009 at 7:31pm

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.