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.

General Questions

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

[SOLVED] INNER JOIN problem with syntax


Reply

6 Posts   2341 Views

Avatar
Harley

4 September 2009 at 11:14am (Last edited: 6 September 2009 5:23am), Community Member, 153 Posts

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

Avatar
Willr

5 September 2009 at 6:59pm Forum Moderator, 5511 Posts

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[

Avatar
Harley

5 September 2009 at 9:27pm Community Member, 153 Posts

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

Avatar
Willr

5 September 2009 at 9:31pm Forum Moderator, 5511 Posts

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.

Avatar
Harley

6 September 2009 at 5:22am Community Member, 153 Posts

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

Avatar
Willr

6 September 2009 at 5:11pm Forum Moderator, 5511 Posts

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