Hi - can anyone identify why this SQL statement will not work? Its really irritating because I just get an unknown column error on the CourseCategoryID. Which is complete rubbish because it DOES exist!!! (???)
I have used two WHERE clauses because I am assuming the WHERE's in SS double up as AND as well.
The only subtle difference I can ascertain is that when SS creates look-up tables it does not create class names for them thus you have to refer to the table directly. To be honest if anyone can just show me how to do a straight SQL statement in SS I'd be happy with that. I know its not the done thing but I just need to get this sorted!
It's hard to tell without knowing more about what you're trying to do or seeing your Course model, but you're presumably receiving that error because CourseCategoryID does not exist on the "Course" table - where() is called in the context of Course::get() in your example code. Simply creating a Course model and a Category model would be sufficient to set up a relation table without resorting to innerJoin, which would have a schema resembling this:
(id, localKey, foreignKey)
id | course_id | category_id
source: inferred from http://api.silverstripe.org/master/source-class-ManyManyList.html#107
Is this a table that was created within the framework or some legacy code that was generated elsewhere? You can write straight SQL using the [url=http://doc.silverstripe.org/framework/en/reference/sqlquery]DB::query() function[/url]