Jump to:

3372 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Custom SQL Query via SQLQuery()-> Bug?

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

Page: 1
Go to End
Author Topic: 3186 Views
  • cabby
    Avatar
    Community Member
    15 Posts

    Custom SQL Query via SQLQuery()-> Bug? Link to this post

    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

  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Custom SQL Query via SQLQuery()-> Bug? Link to this post

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

  • Hamish
    Avatar
    Community Member
    712 Posts

    Re: Custom SQL Query via SQLQuery()-> Bug? Link to this post

    See 4491.

  • cabby
    Avatar
    Community Member
    15 Posts

    Re: Custom SQL Query via SQLQuery()-> Bug? Link to this post

    Great! Thanks for fixing this.

  • Sanchez
    Avatar
    Community Member
    6 Posts

    Re: Custom SQL Query via SQLQuery()-> Bug? Link to this post

    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
          }

  • cabby
    Avatar
    Community Member
    15 Posts

    Re: Custom SQL Query via SQLQuery()-> Bug? Link to this post

    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.

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