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

[ss3.0] DataList Filter / innerJoin combo escaping bug


Reply


3 Posts   330 Views

Avatar
svandragt

Community Member, 44 Posts

14 January 2014 at 5:26am

Edited: 14/01/2014 5:28am

I seem to get bit by a datalist filter bug in combination with an innerJoin.

I have the following relationship:
Course has_many CourseInstances
CourseInstance has_one Course

Let's report on all the CourseInstances with an empty duration sorted by Course Title:

Filter on the Duration:

      $instances = CourseInstance::get();
      $instances = $instances->innerJoin('Course', '"Course"."ID" = "CourseInstance"."CourseID"');
      return $instances->filter('Duration',"")->Sort("Course.Title");

result:
[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "CourseInstance"."ID") AS "0" FROM "CourseInstance" INNER JOIN "Course" ON "Course"."ID" = "CourseInstance"."CourseID" WHERE ("Duration" = '') Column 'Duration' in where clause is ambiguous

Ok, I'll be more explicit and filter on Course.Duration:

      $instances = CourseInstance::get();
      $instances = $instances->innerJoin('Course', '"Course"."ID" = "CourseInstance"."CourseID"');
      return $instances->filter('Course.Duration',"")->Sort("Course.Title");

result:
[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "CourseInstance"."ID") AS "0" FROM "CourseInstance" INNER JOIN "Course" ON "Course"."ID" = "CourseInstance"."CourseID" WHERE ("Course.Duration" = '') Unknown column 'Course.Duration' in 'where clause'

Clearly I should have escaped Course.Duration:

      $instances= CourseInstance::get();
      $instances = $instances->innerJoin('Course', '"Course"."ID" = "CourseInstance"."CourseID"');
      return $instances->filter('"Course"."Duration"',"")->Sort("Course.Title");

result:
[User Error] Couldn't run query: SELECT DISTINCT count(DISTINCT "CourseInstance"."ID") AS "0" FROM "CourseInstance" INNER JOIN "Course" ON "Course"."ID" = "CourseInstance"."CourseID" WHERE ("\"Course\".\"Duration\"" = '') 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 'Course\".\"Duration\"" = '')' at line 1

I've tried seperating table from field using single quotes, escaped single quotes, double quotes, escaped double quotes.
I've tried combining the key with the value in combination of the above.

The result is always that it doesn't escape enough, that it double escapes or that the for loop fails because I've combined the two filter arguments.

How?

Avatar
svandragt

Community Member, 44 Posts

14 January 2014 at 5:45am

The answer is btw:

$instances = $instances->where("CourseInstances.Duration IS NULL");

But I would like to know if this is a bug and if not how do I do this with a filter?

Avatar
simon_w

Forum Moderator, 474 Posts

14 January 2014 at 8:49am

$instances = $instances->filter('Course.Duration:ExactMatch', '');

You don't need to do the join yourself. However, there's currently no way to do a IS NULL with the filter() method, so if your values are actually null you will still need to do the join and then use where().