Jump to:

23368 Posts in 18143 Topics by 2863 members

General Questions

SilverStripe Forums » General Questions » [SOLVED] INNER JOIN problem with syntax

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: 2259 Views
  • Harley
    Avatar
    Community Member
    153 Posts

    [SOLVED] INNER JOIN problem with syntax Link to this post

    Hello all,

    I wondered if someone can help me out here. I am trying to use this join to retrieve posts of a particular forum I have created called 'Noticeboard'. This is being pulled in on my homepage:

    $noticeboard = DataObject::get("Post", "INNER JOIN SiteTree ON 'Post'.ForumID = 'SiteTree'.ID", "Created DESC", "WHERE SiteTree.Title='Noticeboard'" , $num);

    Now I know the query works because I tested it out with phpMyAdmin.

    So how does the syntax for SS work?

    Any help would be great

    Thanks

    Harley

  • Willr
    Avatar
    Forum Moderator
    5489 Posts

    Re: [SOLVED] INNER JOIN problem with syntax Link to this post

    Hmm you might want to try back ticks for the table names if that query works fine (eg the join is setup correct)

    DataObject::get("Post", "INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID", "Created DESC", "WHERE `SiteTree`.Title='Noticeboard'" , $num);
    [/code[

  • Harley
    Avatar
    Community Member
    153 Posts

    Re: [SOLVED] INNER JOIN problem with syntax Link to this post

    Thanks for the reply, tried what you said but still no joy.

    Here is the error it gave me

    [User Error] Couldn't run query: SELECT `Post`.*, `Post`.ID, if(`Post`.ClassName,`Post`.ClassName,'Post') AS RecordClassName FROM `Post` WHERE `SiteTree`.Title='Noticeboard' WHERE INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.ID ORDER BY Created DESC LIMIT 3 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.' at line 1
    GET /wwwroot/elevator/elevator-home/?flush=1

    Line 400 in C:\xampp\htdocs\wwwroot\elevator\sapphire\core\model\MySQLDatabase.php
    Source

    391    }
    392    
    393    function databaseError($msg, $errorLevel = E_USER_ERROR) {
    394       // try to extract and format query
    395       if(preg_match('/Couldn\'t run query: ([^\|]*)\|\s*(.*)/', $msg, $matches)) {
    396          $formatter = new SQLFormatter();
    397          $msg = "Couldn't run query: \n" . $formatter->formatPlain($matches[1]) . "\n\n" . $matches[2];
    398       }
    399       
    400       user_error($msg, $errorLevel);
    401    }
    402 }
    403
    404 /**
    405 * A result-set from a MySQL database.
    406 * @package sapphire

    Trace

    * Couldn't run query: SELECT `Post`.*, `Post`.ID, if(`Post`.ClassName,`Post`.ClassName,'Post') AS RecordClassName FROM `Post` WHERE `SiteTree`.Title='Noticeboard' WHERE INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.ID ORDER BY Created DESC LIMIT 3 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.' at line 1
    Line 400 of MySQLDatabase.php
    * MySQLDatabase->databaseError(Couldn't run query: SELECT `Post`.*, `Post`.ID, if(`Post`.ClassName,`Post`.ClassName,'Post') AS RecordClassName FROM `Post` WHERE `SiteTree`.Title='Noticeboard' WHERE (INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.ID ORDER BY Created DESC LIMIT 3 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.' at line 1,256)
    Line 102 of MySQLDatabase.php
    * MySQLDatabase->query(SELECT `Post`.*, `Post`.ID, if(`Post`.ClassName,`Post`.ClassName,'Post') AS RecordClassName FROM `Post` WHERE `SiteTree`.Title='Noticeboard' WHERE (INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.ID ORDER BY Created DESC LIMIT 3,256)
    Line 120 of DB.php
    * DB::query(SELECT `Post`.*, `Post`.ID, if(`Post`.ClassName,`Post`.ClassName,'Post') AS RecordClassName FROM `Post` WHERE `SiteTree`.Title='Noticeboard' WHERE (INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID) GROUP BY `Post`.ID ORDER BY Created DESC LIMIT 3)
    Line 426 of SQLQuery.php
    * SQLQuery->execute()
    Line 2404 of DataObject.php
    * DataObject->instance_get(INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID,Created DESC,WHERE `SiteTree`.Title='Noticeboard',3,DataObjectSet)
    Line 2380 of DataObject.php
    * DataObject::get(Post,INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID,Created DESC,WHERE `SiteTree`.Title='Noticeboard',3)
    Line 95 of elevatorHomePage.php
    * elevatorHomePage_Controller->getNoticeboardPosts()
    * call_user_func_array(Array,Array)
    Line 318 of ViewableData.php
    * ViewableData->obj(getNoticeboardPosts)
    Line 72 of .cacheC..xampp.htdocs.wwwroot.elevator.themes.elevator.templates.Layout.elevatorHomePage.ss
    * include(C:\Users\Bob\AppData\Local\Temp\silverstripe-cacheC--xampp-htdocs-wwwroot-Elevator\.cacheC..xampp.htdocs.wwwroot.elevator.themes.elevator.templates.Layout.elevatorHomePage.ss)
    Line 354 of SSViewer.php
    * SSViewer->process(elevatorHomePage_Controller)
    Line 346 of SSViewer.php
    * SSViewer->process(elevatorHomePage_Controller)
    Line 175 of Controller.php
    * Controller->handleAction(HTTPRequest)
    Line 129 of RequestHandler.php
    * RequestHandler->handleRequest(HTTPRequest)
    Line 122 of Controller.php
    * Controller->handleRequest(HTTPRequest)
    Line 29 of ModelAsController.php
    * ModelAsController->handleRequest(HTTPRequest)
    Line 277 of Director.php
    * Director::handleRequest(HTTPRequest,Session)
    Line 121 of Director.php
    * Director::direct(/elevator-home/)
    Line 118 of main.php

    Just out of interest, what is the significance of the backtick?

    Regards

  • Willr
    Avatar
    Forum Moderator
    5489 Posts

    Re: [SOLVED] INNER JOIN problem with syntax Link to this post

    Oh sorry didn't pick up on this last time. Your join has to be the 4th parameter;

    DataObject::get($class, $where, $order, $join, $limit);

    So your query would look like

    DataObject::get("Post", "`SiteTree`.Title='Noticeboard'", "Created DESC", "INNER JOIN `SiteTree` ON `Post`.ForumID = `SiteTree`.ID" , $num);

    Or something along those lines.

  • Harley
    Avatar
    Community Member
    153 Posts

    Re: [SOLVED] INNER JOIN problem with syntax Link to this post

    My man, you hit the nail on the head!

    So just to clarify for future reference, this:- DataObject::get($class, $where, $order, $join, $limit); is always the order of the syntax for any sql statement?

    Glad I got that sorted, I was getting worried for a while there

    Thanks so much again

  • Willr
    Avatar
    Forum Moderator
    5489 Posts

    Re: [SOLVED] INNER JOIN problem with syntax Link to this post

    That is the syntax for any DataObject::get() call or DataObject::get_one() call. If you are using DB::query() that has no order as that passes straight SQL

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