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

Avatar
svandragt

14 January 2014 at 5:26am (Last edited: 14 January 2014 5:28am), Community Member, 44 Posts

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

14 January 2014 at 5:45am Community Member, 44 Posts

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

14 January 2014 at 8:49am Forum Moderator, 474 Posts

$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().