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

Many-to-many Unknown column


Reply

4 Posts   233 Views

Avatar
tahbit

4 July 2014 at 8:27pm Community Member, 24 Posts

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

4 July 2014 at 8:48pm Community Member, 24 Posts

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

4 July 2014 at 9:09pm Community Member, 1 Post

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]

Avatar
tahbit

4 July 2014 at 9:28pm Community Member, 24 Posts

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.