Jump to:

22977 Posts in 11806 Topics by 2826 members

General Questions

SilverStripe Forums » General Questions » [ss3.0] DataList Filter / innerJoin combo escaping bug

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

Page: 1
Go to End
Author Topic: 146 Views
  • svandragt
    Avatar
    Community Member
    43 Posts

    [ss3.0] DataList Filter / innerJoin combo escaping bug Link to this post

    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?

  • svandragt
    Avatar
    Community Member
    43 Posts

    Re: [ss3.0] DataList Filter / innerJoin combo escaping bug Link to this post

    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?

  • simon_w
    Avatar
    Forum Moderator
    467 Posts

    Re: [ss3.0] DataList Filter / innerJoin combo escaping bug Link to this post

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

    146 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.