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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Many-to-many Unknown column


Go to End


4 Posts   1753 Views

Avatar
tahbit

Community Member, 24 Posts

4 July 2014 at 8:27pm

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!!! (???)

$getCourses = Course::get()->innerJoin("course_coursecategories", "\"Rel\".\"CourseID\" = \"Course\".\"ID\"", "Rel")
	 ->where("\"course_coursecategories\".CourseCategoryID = $courseCategoryId")
	 ->where("\"Course\".\"SubjectAreaID\" = $subjectId");

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!

Many thanks!

Avatar
tahbit

Community Member, 24 Posts

4 July 2014 at 8:48pm

I have worked out one error (speechmarks) but I still have the same problem in that CourseCategoryID does not exist - which as I say is impossible - its there!!

Amended:

$getCourses = Course::get()->innerJoin("course_coursecategories", "\"Rel\".\"CourseID\" = \"Course\".\"ID\"", "Rel")
	 ->where("\"Course\".\"SubjectAreaID\" = $subjectId")
	 ->where("\"course_coursecategories\".\"CourseCategoryID\" = $courseCategoryId");

Thanks in advance!

Avatar
the_elliot

Community Member, 2 Posts

4 July 2014 at 9:09pm

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 DB::query() function

Avatar
tahbit

Community Member, 24 Posts

4 July 2014 at 9:28pm

Hi Elliot,

Yeah - I've just resolved it! I ended up using SS's SQL query class and used this solution:

$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('course');
$sqlQuery->selectField('Title');

$sqlQuery->addInnerJoin('course_coursecategories','"course_coursecategories"."CourseID" = "course"."ID"');
$sqlQuery->addWhere("course_coursecategories.CourseCategoryID = $courseCategoryId");

$rawSQL = $sqlQuery->sql();
 
// Execute and return a Query object
$result = $sqlQuery->execute();

var_dump($result);

The docs for anyone else in a similar fix is: http://doc.silverstripe.org/framework/en/reference/sqlquery

Thanks for your assistance.