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

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[

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

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.

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

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