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   2395 Views

Avatar
Harley

Community Member, 153 Posts

4 September 2009 at 11:14am

Edited: 06/09/2009 5:23am

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

Forum Moderator, 5513 Posts

5 September 2009 at 6:59pm

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

Community Member, 153 Posts

5 September 2009 at 9:27pm

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

Forum Moderator, 5513 Posts

5 September 2009 at 9:31pm

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

Community Member, 153 Posts

6 September 2009 at 5:22am

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

Forum Moderator, 5513 Posts

6 September 2009 at 5:11pm

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