Merge branch 'bodies_str-factoring'

35 files changed, 191 insertions, 316 deletions

diff --git a/bin/fixmystreet.com/showcouncilrates b/bin/fixmystreet.com/showcouncilrates
index 1dacae597..a3465c575 100755
--- a/bin/fixmystreet.com/showcouncilrates
+++ b/bin/fixmystreet.com/showcouncilrates
@@ -38,7 +38,6 @@ my $stats = dbh()->selectall_arrayref($query, { Slice => {} });
 my @councils;
 foreach my $row (@$stats) {
     if ($row->{council}) {
-        $row->{council} =~ s/\|.*//g;
         my @council_ids = split(/,/, $row->{council});
         push(@councils, @council_ids);
         $row->{council} = \@council_ids;
diff --git a/bin/update-all-reports b/bin/update-all-reports
index f6cd34210..0627cedc2 100755
--- a/bin/update-all-reports
+++ b/bin/update-all-reports
@@ -57,8 +57,7 @@ while ( my @problem = $problems->next ) {
         $problem{bodies} = 0;
     } else {
         # Add to bodies it was sent to
-        (my $bodies = $problem{bodies_str}) =~ s/\|.*$//;
-        @bodies = split( /,/, $bodies );
+        @bodies = split( /,/, $problem{bodies_str} );
         $problem{bodies} = scalar @bodies;
     }
     foreach my $body ( @bodies ) {
diff --git a/bin/update-schema b/bin/update-schema
index 57d1d8ad3..dff2255d5 100755
--- a/bin/update-schema
+++ b/bin/update-schema
@@ -195,6 +195,7 @@ else {
 # By querying the database schema, we can see where we're currently at
 # (assuming schema change files are never half-applied, which should be the case)
 sub get_db_version {
+    return '0035' if column_exists('problem', 'bodies_missing');
     return '0034' if ! function_exists('ms_current_timestamp');
     return '0033' if ! function_exists('angle_between');
     return '0032' if table_exists('moderation_original_data');
diff --git a/db/alert_types.sql b/db/alert_types.sql
index 4022ddaf7..471fd905f 100644
--- a/db/alert_types.sql
+++ b/db/alert_types.sql
@@ -93,7 +93,7 @@ values ('postcode_local_problems_state', '', '',
     'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
--- New problems sent to a particular council
+-- New problems sent to a particular body
 insert into alert_type
 (ref, head_sql_query, head_table,
     head_title, head_link, head_description,
@@ -107,8 +107,7 @@ values ('council_problems', '', '',
       ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
      ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
      ''internal referral'' ) AND
-    (bodies_str like ''%''||?||''%'' or bodies_str is null) and
-    areas like ''%,''||?||'',%''',
+    regexp_split_to_array(bodies_str, '','') && ARRAY[?]',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
 );
@@ -128,7 +127,7 @@ values ('ward_problems', '', '',
      ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
      ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
      ''internal referral'' ) AND
-    (bodies_str like ''%''||?||''%'' or bodies_str is null) and
+    (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and
     areas like ''%,''||?||'',%''',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
diff --git a/db/alert_types_eha.pl b/db/alert_types_eha.pl
deleted file mode 100644
index b090522e6..000000000
--- a/db/alert_types_eha.pl
+++ /dev/null
@@ -1,28 +0,0 @@
-# This file is only here so that the strings from alert_types_eha.sql
-# get into the .po translation file.
-
-# New problems anywhere on the site
-    _('New reports on reportemptyhomes.com'),
-
-# New fixed problems anywhere on the site
-    _('Properties recently reported as put back to use on reportemptyhomes.com'),
-    _('The latest properties reported back to use by users'),
-
-# New problems around a location
-    _('New local reports on reportemptyhomes.com'),
-    _('The latest local reports reported by users'),
-
-# New problems around a postcode
-    _('New reports on reportemptyhomes.com near {{POSTCODE}}'),
-
-# New problems sent to a particular council
-    _('New reports to {{COUNCIL}} on reportemptyhomes.com'),
-    _('The latest reports for {{COUNCIL}} reported by users'),
-
-# New problems within a particular ward sent to a particular council
-    _('New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com'),
-    _('The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users'),
-
-# New problems within a particular voting area (ward, constituency, whatever)
-    _('New reports within {{NAME}}\'s boundary on reportemptyhomes.com'),
-    _('The latest reports within {{NAME}}\'s boundary reported by users'),
diff --git a/db/alert_types_eha.sql b/db/alert_types_eha.sql
deleted file mode 100644
index e4ec0c986..000000000
--- a/db/alert_types_eha.sql
+++ /dev/null
@@ -1,95 +0,0 @@
--- New updates on a particular problem report
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('new_updates', 'select * from problem where id=?', 'problem',
-    'Updates on {{title}}', '/', 'Updates on {{title}}',
-    'comment', 'comment.state=''confirmed''', 'created desc',
-    'Update by {{name}}', '/report/{{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update');
-
--- New problems anywhere on the site
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('new_problems', '', '',
-    'New reports on reportemptyhomes.com', '/', 'The latest empty properties reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
-
--- New fixed problems anywhere on the site
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('new_fixed_problems', '', '',
-    'Properties recently reported as put back to use on reportemptyhomes.com', '/', 'The latest properties reported back to use by users',
-    'problem', 'problem.state in (''fixed'', ''sixed - council'', ''fixed - user'')', 'lastupdate desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
-
--- New problems around a location
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('local_problems', '', '',
-    'New local reports on reportemptyhomes.com', '/', 'The latest local reports reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-
--- New problems around a postcode
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('postcode_local_problems', '', '',
-    'New reports on reportemptyhomes.com near {{POSTCODE}}', '/', 'The latest local reports reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-
--- New problems sent to a particular council
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('council_problems', '', '',
-    'New reports to {{COUNCIL}} on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%''
-        or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
-);
-
--- New problems within a particular ward sent to a particular council
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('ward_problems', '', '',
-    'New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com', '/reports',
-    'The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%''
-        or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
-);
-
--- New problems within a particular voting area (ward, constituency, whatever)
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('area_problems', '', '',
-    'New reports within {{NAME}}''s boundary on reportemptyhomes.com', '/reports',
-    'The latest reports within {{NAME}}''s boundary reported by users', 'problem',
-    'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and areas like ''%,''||?||'',%''', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
-);
-
diff --git a/db/schema.sql b/db/schema.sql
index 609a1445d..ea2698b0e 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -132,6 +132,7 @@ create table problem (
     latitude double precision not null,
     longitude double precision not null,
     bodies_str text, -- the body(s) we'll report this problem to
+    bodies_missing text, -- the body(s) we had no contact details for
     areas text not null, -- the mapit areas this location is in
     category text not null default 'Other',
     title text not null,
@@ -206,6 +207,7 @@ create index problem_state_latitude_longitude_idx on problem(state, latitude, lo
 create index problem_user_id_idx on problem ( user_id );
 create index problem_external_body_idx on problem(lower(external_body));
 create index problem_radians_latitude_longitude_idx on problem(radians(latitude), radians(longitude));
+create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ','));
 
 create table questionnaire (
     id serial not null primary key,
diff --git a/db/schema_0035-bodies_str-tidying.sql b/db/schema_0035-bodies_str-tidying.sql
new file mode 100644
index 000000000..f5e1dbbdd
--- /dev/null
+++ b/db/schema_0035-bodies_str-tidying.sql
@@ -0,0 +1,31 @@
+begin;
+
+alter table problem add bodies_missing text;
+
+update problem
+    set bodies_missing = split_part(bodies_str, '|', 2),
+        bodies_str = split_part(bodies_str, '|', 1)
+    where bodies_str like '%|%';
+
+create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ','));
+
+UPDATE alert_type set item_where =
+    'problem.non_public = ''f'' and problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+      ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+     ''internal referral'' ) AND
+    regexp_split_to_array(bodies_str, '','') && ARRAY[?]'
+    WHERE ref = 'council_problems';
+
+UPDATE alert_type set item_where =
+    'problem.non_public = ''f'' and problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+     ''internal referral'' ) AND
+    (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and
+    areas like ''%,''||?||'',%'''
+    WHERE ref = 'ward_problems';
+
+commit;
diff --git a/notes/cobranding.txt b/notes/cobranding.txt
index 3e902abc8..a62747397 100644
--- a/notes/cobranding.txt
+++ b/notes/cobranding.txt
@@ -10,8 +10,7 @@ NB: this is moderately specific to producing cobrands for UK councils
 1: copy an exiting perllib/FixMyStreet/Cobrand/ file to perllib/FixMyStreet/Cobrand/ExampleCom.pm
     * Change package name at top of file
     * Change following functions accordingly:
-        site_restriction
-        problems_clause
+        body_restriction
         enter_postcode_text
         area_check
         base_url
diff --git a/perllib/FixMyStreet/App/Controller/Admin.pm b/perllib/FixMyStreet/App/Controller/Admin.pm
index 44b898743..f9ea383f8 100644
--- a/perllib/FixMyStreet/App/Controller/Admin.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin.pm
@@ -70,8 +70,6 @@ sub index : Path : Args(0) {
         return $c->cobrand->admin();
     }
 
-    my $site_restriction = $c->cobrand->site_restriction();
-
     my $problems = $c->cobrand->problems->summary_count;
 
     my %prob_counts =
@@ -85,7 +83,7 @@ sub index : Path : Args(0) {
         for ( FixMyStreet::DB::Result::Problem->visible_states() );
     $c->stash->{total_problems_users} = $c->cobrand->problems->unique_users;
 
-    my $comments = $c->model('DB::Comment')->summary_count( $site_restriction );
+    my $comments = $c->model('DB::Comment')->summary_count( $c->cobrand->body_restriction );
 
     my %comment_counts =
       map { $_->state => $_->get_column('state_count') } $comments->all;
@@ -150,7 +148,6 @@ sub config_page : Path( 'config' ) : Args(0) {
 sub timeline : Path( 'timeline' ) : Args(0) {
     my ($self, $c) = @_;
 
-    my $site_restriction = $c->cobrand->site_restriction();
     my %time;
 
     $c->model('DB')->schema->storage->sql_maker->quote_char( '"' );
@@ -171,7 +168,7 @@ sub timeline : Path( 'timeline' ) : Args(0) {
         push @{$time{$_->whenanswered->epoch}}, { type => 'quesAnswered', date => $_->whenanswered, obj => $_ } if $_->whenanswered;
     }
 
-    my $updates = $c->model('DB::Comment')->timeline( $site_restriction );
+    my $updates = $c->model('DB::Comment')->timeline( $c->cobrand->body_restriction );
 
     foreach ($updates->all) {
         push @{$time{$_->created->epoch}}, { type => 'update', date => $_->created, obj => $_} ;
@@ -538,8 +535,6 @@ sub reports : Path('reports') {
     if (my $search = $c->get_param('search')) {
         $c->stash->{searched} = $search;
 
-        my $site_restriction = $c->cobrand->site_restriction;
-
         my $search_n = 0;
         $search_n = int($search) if $search =~ /^\d+$/;
 
@@ -616,9 +611,10 @@ sub reports : Path('reports') {
         }
 
         if (@$query) {
-            my $updates = $c->model('DB::Comment')->search(
+            my $updates = $c->model('DB::Comment')
+                ->to_body($c->cobrand->body_restriction)
+                ->search(
                 {
-                    %{ $site_restriction },
                     -or => $query,
                 },
                 {
@@ -650,8 +646,6 @@ sub reports : Path('reports') {
 sub report_edit : Path('report_edit') : Args(1) {
     my ( $self, $c, $id ) = @_;
 
-    my $site_restriction = $c->cobrand->site_restriction;
-
     my $problem = $c->cobrand->problems->search( { id => $id } )->first;
 
     $c->detach( '/page_error_404_not_found' )
@@ -874,13 +868,9 @@ sub users: Path('users') : Args(0) {
 sub update_edit : Path('update_edit') : Args(1) {
     my ( $self, $c, $id ) = @_;
 
-    my $site_restriction = $c->cobrand->site_restriction;
-    my $update = $c->model('DB::Comment')->search(
-        {
-            id => $id,
-            %{$site_restriction},
-        }
-    )->first;
+    my $update = $c->model('DB::Comment')
+        ->to_body($c->cobrand->body_restriction)
+        ->search({ id => $id })->first;
 
     $c->detach( '/page_error_404_not_found' )
       unless $update;
@@ -1064,7 +1054,7 @@ sub user_edit : Path('user_edit') : Args(1) {
 sub flagged : Path('flagged') : Args(0) {
     my ( $self, $c ) = @_;
 
-    my $problems = $c->model('DB::Problem')->search( { flagged => 1 } );
+    my $problems = $c->cobrand->problems->search( { flagged => 1 } );
 
     # pass in as array ref as using same template as search_reports
     # which has to use an array ref for sql quoting reasons
@@ -1121,9 +1111,6 @@ sub stats : Path('stats') : Args(0) {
 
         my $bymonth = $c->get_param('bymonth');
         $c->stash->{bymonth} = $bymonth;
-        my ( %body, %dates );
-        $body{bodies_str} = { like => $c->get_param('body') }
-            if $c->get_param('body');
 
         $c->stash->{selected_body} = $c->get_param('body');
 
@@ -1154,14 +1141,12 @@ sub stats : Path('stats') : Args(0) {
             );
         }
 
-        my $p = $c->model('DB::Problem')->search(
+        my $p = $c->cobrand->problems->to_body($c->get_param('body'))->search(
             {
                 -AND => [
                     $field => { '>=', $start_date},
                     $field => { '<=', $end_date + $one_day },
                 ],
-                %body,
-                %dates,
             },
             \%select,
         );
diff --git a/perllib/FixMyStreet/App/Controller/Dashboard.pm b/perllib/FixMyStreet/App/Controller/Dashboard.pm
index c3aa35008..faddaa89e 100644
--- a/perllib/FixMyStreet/App/Controller/Dashboard.pm
+++ b/perllib/FixMyStreet/App/Controller/Dashboard.pm
@@ -89,6 +89,7 @@ sub index : Path : Args(0) {
     my ( $self, $c ) = @_;
 
     my $body = $c->forward('check_page_allowed');
+    $c->stash->{body} = $body;
 
     # Set up the data for the dropdowns
 
@@ -112,7 +113,6 @@ sub index : Path : Args(0) {
     $c->stash->{category} = $c->get_param('category');
 
     my %where = (
-        bodies_str => $body->id, # XXX Does this break in a two tier council? Restriction needs looking at...
         'problem.state' => [ FixMyStreet::DB::Result::Problem->visible_states() ],
     );
     $where{areas} = { 'like', '%,' . $c->stash->{ward} . ',%' }
@@ -155,7 +155,7 @@ sub index : Path : Args(0) {
         %$prob_where,
         'me.confirmed' => { '>=', $dtf->format_datetime( $now->clone->subtract( days => 30 ) ) },
     };
-    my $problems_rs = $c->cobrand->problems->search( $params );
+    my $problems_rs = $c->cobrand->problems->to_body($body)->search( $params );
     my @problems = $problems_rs->all;
 
     my %problems;
@@ -270,12 +270,14 @@ sub export_as_csv {
 sub updates_search : Private {
     my ( $self, $c, $time ) = @_;
 
+    my $body = $c->stash->{body};
+
     my $params = {
         %{$c->stash->{where}},
         'me.confirmed' => { '>=', $time },
     };
 
-    my $comments = $c->model('DB::Comment')->search(
+    my $comments = $c->model('DB::Comment')->to_body($body)->search(
         $params,
         {
             group_by => [ 'problem_state' ],
@@ -302,7 +304,7 @@ sub updates_search : Private {
         my $col = shift @$vars;
         my $substmt = "select min(id) from comment where me.problem_id=comment.problem_id and problem_state in ('"
             . join("','", @$vars) . "')";
-        $comments = $c->model('DB::Comment')->search(
+        $comments = $c->model('DB::Comment')->to_body($body)->search(
             { %$params,
                 problem_state => $vars,
                 'me.id' => \"= ($substmt)",
@@ -319,7 +321,7 @@ sub updates_search : Private {
         $counts{$col} = int( ($comments->get_column('time')||0) / 60 / 60 / 24 + 0.5 );
     }
 
-    $counts{fixed_user} = $c->model('DB::Comment')->search(
+    $counts{fixed_user} = $c->model('DB::Comment')->to_body($body)->search(
         { %$params, mark_fixed => 1, problem_state => undef }, { join     => 'problem' }
     )->count;
 
@@ -327,7 +329,7 @@ sub updates_search : Private {
         %{$c->stash->{prob_where}},
         'me.confirmed' => { '>=', $time },
     };
-    $counts{total} = $c->cobrand->problems->search( $params )->count;
+    $counts{total} = $c->cobrand->problems->to_body($body)->search( $params )->count;
 
     $params = {
         %{$c->stash->{prob_where}},
@@ -335,7 +337,7 @@ sub updates_search : Private {
         state => 'confirmed',
         '(select min(id) from comment where me.id=problem_id and problem_state is not null)' => undef,
     };
-    $counts{not_marked} = $c->cobrand->problems->search( $params )->count;
+    $counts{not_marked} = $c->cobrand->problems->to_body($body)->search( $params )->count;
 
     return \%counts;
 }
diff --git a/perllib/FixMyStreet/App/Controller/My.pm b/perllib/FixMyStreet/App/Controller/My.pm
index 83d5f7adb..9db9386f5 100644
--- a/perllib/FixMyStreet/App/Controller/My.pm
+++ b/perllib/FixMyStreet/App/Controller/My.pm
@@ -37,10 +37,6 @@ sub my : Path : Args(0) {
     my $params = {
         state => [ keys %$states ],
     };
-    $params = {
-        %{ $c->cobrand->problems_clause },
-        %$params
-    } if $c->cobrand->problems_clause;
 
     my $category = $c->get_param('filter_category');
     if ( $category ) {
@@ -48,7 +44,9 @@ sub my : Path : Args(0) {
         $c->stash->{filter_category} = $category;
     }
 
-    my $rs = $c->user->problems->search( $params, {
+    my $rs = $c->user->problems
+        ->to_body($c->cobrand->body_restriction)
+        ->search( $params, {
         order_by => { -desc => 'confirmed' },
         rows => 50
     } )->page( $p_page );
diff --git a/perllib/FixMyStreet/App/Controller/Report/New.pm b/perllib/FixMyStreet/App/Controller/Report/New.pm
index b540a1961..e9332d852 100644
--- a/perllib/FixMyStreet/App/Controller/Report/New.pm
+++ b/perllib/FixMyStreet/App/Controller/Report/New.pm
@@ -884,12 +884,13 @@ sub process_report : Private {
         } else {
             # construct the bodies string:
             #  'x,x' - x are body IDs that have this category
-            #  'x,x|y' - x are body IDs that have this category, y body IDs with *no* contact
             my $body_string = join( ',', map { $_->body_id } @contacts );
-            $body_string .=
-              '|' . join( ',', map { $_->id } @{ $c->stash->{missing_details_bodies} } )
-                if $body_string && @{ $c->stash->{missing_details_bodies} };
             $report->bodies_str($body_string);
+            # Record any body IDs which might have meant to match, but had no contact
+            if ($body_string && @{ $c->stash->{missing_details_bodies} }) {
+                my $missing = join( ',', map { $_->id } @{ $c->stash->{missing_details_bodies} } );
+                $report->bodies_missing($missing);
+            }
         }
 
         my @extra;
diff --git a/perllib/FixMyStreet/App/Controller/Reports.pm b/perllib/FixMyStreet/App/Controller/Reports.pm
index 4582843bd..25d5cd82e 100644
--- a/perllib/FixMyStreet/App/Controller/Reports.pm
+++ b/perllib/FixMyStreet/App/Controller/Reports.pm
@@ -269,9 +269,7 @@ sub rss_ward : Path('/rss/reports') : Args(2) {
         # Problems sent to a council
         $c->stash->{type} = 'council_problems';
         $c->stash->{title_params} = { COUNCIL => $c->stash->{body}->name };
-        # XXX This looks up in both bodies_str and areas, but is only using body ID.
-        # This will not work properly in any install where body IDs are not === area IDs.
-        $c->stash->{db_params} = [ $c->stash->{body}->id, $c->stash->{body}->id ];
+        $c->stash->{db_params} = [ $c->stash->{body}->id ];
     }
 
     # Send on to the RSS generation
@@ -417,29 +415,16 @@ sub load_and_group_problems : Private {
         $where->{category} = $category;
     }
 
+    my $problems = $c->cobrand->problems;
+
     if ($c->stash->{ward}) {
         $where->{areas} = { 'like', '%,' . $c->stash->{ward}->{id} . ',%' };
-        $where->{bodies_str} = [
-            undef,
-            $c->stash->{body}->id,
-            { 'like', $c->stash->{body}->id . ',%' },
-            { 'like', '%,' . $c->stash->{body}->id },
-        ];
+        $problems = $problems->to_body($c->stash->{body});
     } elsif ($c->stash->{body}) {
-        # XXX FixMyStreet used to have the following line so that reports not
-        # currently sent anywhere could still be listed in the appropriate
-        # (body/area), as they were the same.  Now they're not, not sure if
-        # there's a way to do this easily.
-        #$where->{areas} = { 'like', '%,' . $c->stash->{body}->id . ',%' };
-        $where->{bodies_str} = [
-        #    undef,
-            $c->stash->{body}->id,
-            { 'like', $c->stash->{body}->id . ',%' },
-            { 'like', '%,' . $c->stash->{body}->id },
-        ];
+        $problems = $problems->to_body($c->stash->{body});
     }
 
-    my $problems = $c->cobrand->problems->search(
+    $problems = $problems->search(
         $where,
         {
             order_by => $c->cobrand->reports_ordering,
@@ -463,7 +448,6 @@ sub load_and_group_problems : Private {
             }
         } else {
             # Add to bodies it was sent to
-            # XXX Assumes body ID matches "council ID"
             my $bodies = $problem->bodies_str_ids;
             foreach ( @$bodies ) {
                 next if $_ != $c->stash->{body}->id;
diff --git a/perllib/FixMyStreet/App/Controller/Rss.pm b/perllib/FixMyStreet/App/Controller/Rss.pm
index 7aafc99ff..b58b79937 100755
--- a/perllib/FixMyStreet/App/Controller/Rss.pm
+++ b/perllib/FixMyStreet/App/Controller/Rss.pm
@@ -264,9 +264,9 @@ sub add_row : Private {
     (my $link = $alert_type->item_link) =~ s/{{(.*?)}}/$row->{$1}/g;
     (my $desc = _($alert_type->item_description)) =~ s/{{(.*?)}}/$row->{$1}/g;
 
-    my $hashref_restriction = $c->cobrand->site_restriction;
+    my $hashref_restriction = $c->cobrand->body_restriction;
     my $base_url = $c->cobrand->base_url;
-    if ( $hashref_restriction && $hashref_restriction->{bodies_str} && $row->{bodies_str} && $row->{bodies_str} ne $hashref_restriction->{bodies_str} ) {
+    if ( $hashref_restriction && $row->{bodies_str} && $row->{bodies_str} ne $hashref_restriction ) {
         $base_url = $c->config->{BASE_URL};
     }
     my $url = $base_url . $link;
diff --git a/perllib/FixMyStreet/Cobrand/Default.pm b/perllib/FixMyStreet/Cobrand/Default.pm
index c3185ea05..8cd392073 100644
--- a/perllib/FixMyStreet/Cobrand/Default.pm
+++ b/perllib/FixMyStreet/Cobrand/Default.pm
@@ -45,15 +45,6 @@ sub country {
     return '';
 }
 
-=head1 problems_clause
-
-Returns a hash for a query to be used by problems (and elsewhere in joined
-queries) to restrict results for a cobrand.
-
-=cut
-
-sub problems_clause {}
-
 =head1 problems
 
 Returns a ResultSet of Problems, restricted to a subset if we're on a cobrand
@@ -66,20 +57,20 @@ sub problems {
     return $self->{c}->model('DB::Problem');
 }
 
-=head1 site_restriction
+=head1 body_restriction
 
-Return a site key and a hash of extra query parameters if the cobrand uses a
-subset of the FixMyStreet data. Parameter is any extra data the cobrand needs.
-Returns a site key of 0 and an empty hash if the cobrand uses all the data.
+Return an extra query parameter to restrict reports to those sent to a
+particular body.
 
 =cut
 
-sub site_restriction { return {}; }
+sub body_restriction {}
+
 sub site_key { return 0; }
 
 =head2 restriction
 
-Return a restriction to pull out data saved while using the cobrand site.
+Return a restriction to data saved while using this specific cobrand site.
 
 =cut
 
diff --git a/perllib/FixMyStreet/Cobrand/SeeSomething.pm b/perllib/FixMyStreet/Cobrand/SeeSomething.pm
index 75f1c32e8..9ae15fd8d 100644
--- a/perllib/FixMyStreet/Cobrand/SeeSomething.pm
+++ b/perllib/FixMyStreet/Cobrand/SeeSomething.pm
@@ -10,14 +10,9 @@ sub council_name { return 'See Something Say Something'; }
 sub council_url { return 'seesomething'; }
 sub area_types  { [ 'MTD' ] }
 
-sub site_restriction {
+sub body_restriction {
     my $self = shift;
-    return { bodies_str => { IN => $self->council_id  } };
-}
-
-sub problems_clause {
-    my $self = shift;
-    return { bodies_str => { IN => $self->council_id  } };
+    return $self->council_id;
 }
 
 sub area_check {
diff --git a/perllib/FixMyStreet/Cobrand/UKCouncils.pm b/perllib/FixMyStreet/Cobrand/UKCouncils.pm
index e653ae522..2c231bc39 100644
--- a/perllib/FixMyStreet/Cobrand/UKCouncils.pm
+++ b/perllib/FixMyStreet/Cobrand/UKCouncils.pm
@@ -22,10 +22,11 @@ sub path_to_web_templates {
     ];
 }
 
-sub site_restriction {
+sub body_restriction {
     my $self = shift;
-    return { bodies_str => sprintf('%d', $self->council_id) };
+    return $self->council_id;
 }
+
 sub site_key {
     my $self = shift;
     return $self->council_url;
@@ -35,23 +36,9 @@ sub restriction {
     return { cobrand => shift->moniker };
 }
 
-# Different function to site_restriction due to two-tier use
-sub problems_clause {
-    my $self = shift;
-
-    if ($self->is_two_tier) {
-        return { bodies_str => {
-            like => ('%' . $self->council_id . '%')
-        }};
-    }
-    else {
-        return { bodies_str => sprintf('%d', $self->council_id) };
-    }
-}
-
 sub problems {
     my $self = shift;
-    return $self->{c}->model('DB::Problem')->search( $self->problems_clause );
+    return $self->{c}->model('DB::Problem')->to_body($self->council_id);
 }
 
 sub base_url {
diff --git a/perllib/FixMyStreet/DB/Result/AdminLog.pm b/perllib/FixMyStreet/DB/Result/AdminLog.pm
index d26eba7f5..fcf909692 100644
--- a/perllib/FixMyStreet/DB/Result/AdminLog.pm
+++ b/perllib/FixMyStreet/DB/Result/AdminLog.pm
@@ -31,6 +31,7 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
   "user_id",
   { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
@@ -51,7 +52,7 @@ __PACKAGE__->belongs_to(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2014-07-31 15:58:54
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:okGiaKaVYaTrlz0LCV01vA
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2015-08-13 16:33:38
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:RCi1FEwb9T2MZ2X+QOTTUA
 
 1;
diff --git a/perllib/FixMyStreet/DB/Result/Alert.pm b/perllib/FixMyStreet/DB/Result/Alert.pm
index 21010064b..35cce8368 100644
--- a/perllib/FixMyStreet/DB/Result/Alert.pm
+++ b/perllib/FixMyStreet/DB/Result/Alert.pm
@@ -39,6 +39,7 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
   "whendisabled",
   { data_type => "timestamp", is_nullable => 1 },
@@ -64,8 +65,8 @@ __PACKAGE__->belongs_to(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2013-09-10 17:11:54
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:d9yIFiTGtbtFaULXZNKstQ
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2015-08-13 16:33:38
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:5RNyB430T8PqtFlmGV/MUg
 
 # You can replace this text with custom code or comments, and it will be preserved on regeneration
 
diff --git a/perllib/FixMyStreet/DB/Result/AlertSent.pm b/perllib/FixMyStreet/DB/Result/AlertSent.pm
index 154f8b42e..83043a33b 100644
--- a/perllib/FixMyStreet/DB/Result/AlertSent.pm
+++ b/perllib/FixMyStreet/DB/Result/AlertSent.pm
@@ -20,6 +20,7 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
 );
 __PACKAGE__->belongs_to(
@@ -30,8 +31,8 @@ __PACKAGE__->belongs_to(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2013-09-10 17:11:54
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:COwsprqRSNZS1IxJrPYgMQ
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2015-08-13 16:33:38
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:/+Vodu8VJxJ0EY9P3Qjjjw
 
 
 # You can replace this text with custom code or comments, and it will be preserved on regeneration
diff --git a/perllib/FixMyStreet/DB/Result/Comment.pm b/perllib/FixMyStreet/DB/Result/Comment.pm
index 0c1072a78..0caaa8968 100644
--- a/perllib/FixMyStreet/DB/Result/Comment.pm
+++ b/perllib/FixMyStreet/DB/Result/Comment.pm
@@ -33,6 +33,7 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
   "confirmed",
   { data_type => "timestamp", is_nullable => 1 },
@@ -88,8 +89,8 @@ __PACKAGE__->belongs_to(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2014-07-31 15:59:43
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:08AtJ6CZFyUe7qKMF50MHg
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2015-08-13 16:33:38
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ZR+YNA1Jej3s+8mr52iq6Q
 #
 
 __PACKAGE__->load_components("+FixMyStreet::DB::RABXColumn");
diff --git a/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm b/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm
index 13ecf82cd..d7240cd5d 100644
--- a/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm
+++ b/perllib/FixMyStreet/DB/Result/ModerationOriginalData.pm
@@ -35,6 +35,7 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
 );
 __PACKAGE__->set_primary_key("id");
@@ -46,7 +47,7 @@ __PACKAGE__->belongs_to(
   {
     is_deferrable => 0,
     join_type     => "LEFT",
-    on_delete     => "NO ACTION",
+    on_delete     => "CASCADE,",
     on_update     => "NO ACTION",
   },
 );
@@ -54,12 +55,12 @@ __PACKAGE__->belongs_to(
   "problem",
   "FixMyStreet::DB::Result::Problem",
   { id => "problem_id" },
-  { is_deferrable => 0, on_delete => "NO ACTION", on_update => "NO ACTION" },
+  { is_deferrable => 0, on_delete => "CASCADE,", on_update => "NO ACTION" },
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2014-07-31 15:59:43
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:yR1Vi7cJQrX67dFwAcJW6w
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2015-08-13 16:33:38
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:DBtGjCJykDtLnGtkj638eA
 
 
 # You can replace this text with custom code or comments, and it will be preserved on regeneration
diff --git a/perllib/FixMyStreet/DB/Result/Problem.pm b/perllib/FixMyStreet/DB/Result/Problem.pm
index caf748f1e..88794ee52 100644
--- a/perllib/FixMyStreet/DB/Result/Problem.pm
+++ b/perllib/FixMyStreet/DB/Result/Problem.pm
@@ -55,6 +55,7 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
   "confirmed",
   { data_type => "timestamp", is_nullable => 1 },
@@ -73,6 +74,7 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
   "whensent",
   { data_type => "timestamp", is_nullable => 1 },
@@ -102,6 +104,8 @@ __PACKAGE__->add_columns(
   { data_type => "integer", default_value => 0, is_nullable => 1 },
   "subcategory",
   { data_type => "text", is_nullable => 1 },
+  "bodies_missing",
+  { data_type => "text", is_nullable => 1 },
 );
 __PACKAGE__->set_primary_key("id");
 __PACKAGE__->has_many(
@@ -130,8 +134,8 @@ __PACKAGE__->belongs_to(
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07035 @ 2014-07-31 15:57:02
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:EvD4sS1mdJJyI1muZ4TrCw
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2015-08-13 16:33:38
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Go+T9oFRfwQ1Ag89qPpF/g
 
 # Add fake relationship to stored procedure table
 __PACKAGE__->has_one(
@@ -400,7 +404,7 @@ sub check_for_errors {
 
     $errors{bodies} = _('No council selected')
       unless $self->bodies_str
-          && $self->bodies_str =~ m/^(?:-1|[\d,]+(?:\|[\d,]+)?)$/;
+          && $self->bodies_str =~ m/^(?:-1|[\d,]+)$/;
 
     if ( !$self->name || $self->name !~ m/\S/ ) {
         $errors{name} = _('Please enter your name');
@@ -448,8 +452,7 @@ sub confirm {
 sub bodies_str_ids {
     my $self = shift;
     return unless $self->bodies_str;
-    (my $bodies = $self->bodies_str) =~ s/\|.*$//;
-    my @bodies = split( /,/, $bodies );
+    my @bodies = split( /,/, $self->bodies_str );
     return \@bodies;
 }
 
diff --git a/perllib/FixMyStreet/DB/Result/Token.pm b/perllib/FixMyStreet/DB/Result/Token.pm
index 90715bdd9..a60e23839 100644
--- a/perllib/FixMyStreet/DB/Result/Token.pm
+++ b/perllib/FixMyStreet/DB/Result/Token.pm
@@ -22,13 +22,14 @@ __PACKAGE__->add_columns(
     data_type     => "timestamp",
     default_value => \"current_timestamp",
     is_nullable   => 0,
+    original      => { default_value => \"now()" },
   },
 );
 __PACKAGE__->set_primary_key("scope", "token");
 
 
-# Created by DBIx::Class::Schema::Loader v0.07017 @ 2012-03-08 17:19:55
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:+LLZ8P5GXqPetuGyrra2vw
+# Created by DBIx::Class::Schema::Loader v0.07035 @ 2015-08-13 16:33:38
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:HkvzOY5STjOdXN64hxg5NA
 
 use mySociety::AuthToken;
 
diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
index d2264139a..dde30b59c 100644
--- a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
@@ -70,8 +70,6 @@ sub email_alerts ($) {
             # this is for the new_updates alerts
             next if $row->{non_public} and $row->{user_id} != $row->{alert_user_id};
 
-            my $hashref_restriction = $cobrand->site_restriction( $row->{cobrand_data} );
-
             FixMyStreet::App->model('DB::AlertSent')->create( {
                 alert_id  => $row->{alert_id},
                 parameter => $row->{item_id},
@@ -90,8 +88,9 @@ sub email_alerts ($) {
                 $data{state_message} = _("This report is currently marked as open.");
             }
 
+            my $hashref_restriction = $cobrand->body_restriction;
             my $url = $cobrand->base_url( $row->{alert_cobrand_data} );
-            if ( $hashref_restriction && $hashref_restriction->{bodies_str} && $row->{bodies_str} ne $hashref_restriction->{bodies_str} ) {
+            if ( $hashref_restriction && $row->{bodies_str} ne $hashref_restriction ) {
                 $url = mySociety::Config::get('BASE_URL');
             }
             # this is currently only for new_updates
@@ -172,7 +171,7 @@ sub email_alerts ($) {
 
         my $longitude = $alert->parameter;
         my $latitude  = $alert->parameter2;
-        my $hashref_restriction = $cobrand->site_restriction( $alert->cobrand_data );
+        my $hashref_restriction = $cobrand->body_restriction;
         my $d = mySociety::Gaze::get_radius_containing_population($latitude, $longitude, 200000);
         # Convert integer to GB locale string (with a ".")
         $d = mySociety::Locale::in_gb_locale {
@@ -197,7 +196,7 @@ sub email_alerts ($) {
                 parameter => $row->{id},
             } );
             my $url = $cobrand->base_url( $alert->cobrand_data );
-            if ( $hashref_restriction && $hashref_restriction->{bodies_str} && $row->{bodies_str} ne $hashref_restriction->{bodies_str} ) {
+            if ( $hashref_restriction && $row->{bodies_str} ne $hashref_restriction ) {
                 $url = mySociety::Config::get('BASE_URL');
             }
             $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n";
diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
index abdc46868..270501efc 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Comment.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
@@ -4,19 +4,24 @@ use base 'DBIx::Class::ResultSet';
 use strict;
 use warnings;
 
+sub to_body {
+    my ($rs, $body_restriction) = @_;
+    return FixMyStreet::DB::ResultSet::Problem::to_body($rs, $body_restriction);
+}
+
+
 sub timeline {
-    my ( $rs, $restriction ) = @_;
+    my ( $rs, $body_restriction ) = @_;
 
     my $prefetch = 
         FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ?
         [ qw/user/ ] :
         [];
 
-    return $rs->search(
+    return $rs->to_body($body_restriction)->search(
         {
             state => 'confirmed',
             created => { '>=', \"current_timestamp-'7 days'::interval" },
-            %{ $restriction },
         },
         {
             prefetch => $prefetch,
@@ -25,10 +30,10 @@ sub timeline {
 }
 
 sub summary_count {
-    my ( $rs, $restriction ) = @_;
+    my ( $rs, $body_restriction ) = @_;
 
-    return $rs->search(
-            $restriction,
+    return $rs->to_body($body_restriction)->search(
+        undef,
         {
             group_by => ['me.state'],
             select   => [ 'me.state', { count => 'me.id' } ],
diff --git a/perllib/FixMyStreet/DB/ResultSet/Contact.pm b/perllib/FixMyStreet/DB/ResultSet/Contact.pm
index 6fa6a03a0..f402b5461 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Contact.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Contact.pm
@@ -4,6 +4,8 @@ use base 'DBIx::Class::ResultSet';
 use strict;
 use warnings;
 
+sub me { join('.', shift->current_source_alias, shift || q{})  }
+
 =head2 not_deleted
 
     $rs = $rs->not_deleted();
@@ -14,7 +16,7 @@ Filter down to not deleted contacts - which have C<deleted> set to false;
 
 sub not_deleted {
     my $rs = shift;
-    return $rs->search( { deleted => 0 } );
+    return $rs->search( { $rs->me('deleted') => 0 } );
 }
 
 sub summary_count {
diff --git a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm
index 670673ab3..a6b00ef7b 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm
@@ -19,12 +19,10 @@ sub nearby {
         if $interval;
     $params->{id} = { -not_in => $ids }
         if $ids;
-    $params = {
-        %{ $c->cobrand->problems_clause },
-        %$params
-    } if $c->cobrand->problems_clause;
     $params->{category} = $category if $category;
 
+    $rs = FixMyStreet::DB::ResultSet::Problem::to_body($rs, $c->cobrand->body_restriction);
+
     my $attrs = {
         prefetch => 'problem',
         bind => [ $mid_lat, $mid_lon, $dist ],
diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
index 0aa7c8b3a..8f0d23080 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
@@ -20,6 +20,18 @@ sub set_restriction {
     $site_key = $key;
 }
 
+sub to_body {
+    my ($rs, $bodies) = @_;
+    return $rs unless $bodies;
+    unless (ref $bodies eq 'ARRAY') {
+        $bodies = [ map { ref $_ ? $_->id : $_ } $bodies ];
+    }
+    $rs = $rs->search(
+        \[ "regexp_split_to_array(bodies_str, ',') && ?", [ {} => $bodies ] ]
+    );
+    return $rs;
+}
+
 # Front page statistics
 
 sub recent_fixed {
@@ -236,7 +248,7 @@ sub send_reports {
 
     my $states = [ 'confirmed', 'fixed' ];
     $states = [ 'unconfirmed', 'confirmed', 'in progress', 'planned', 'closed' ] if $site eq 'zurich';
-    my $unsent = FixMyStreet::App->model("DB::Problem")->search( {
+    my $unsent = $rs->search( {
         state => $states,
         whensent => undef,
         bodies_str => { '!=', undef },
@@ -320,15 +332,19 @@ sub send_reports {
             $cobrand->process_additional_metadata_for_email($row, \%h);
         }
 
-        # XXX Needs locks!
-        # XXX Only copes with at most one missing body
-        my ($bodies, $missing) = $row->bodies_str =~ /^([\d,]+)(?:\|(\d+))?/;
-        my @bodies = split(/,/, $bodies);
-        $bodies = FixMyStreet::App->model("DB::Body")->search(
-            { id => \@bodies },
+        my $bodies = FixMyStreet::App->model("DB::Body")->search(
+            { id => $row->bodies_str_ids },
             { order_by => 'name' },
         );
-        $missing = FixMyStreet::App->model("DB::Body")->find($missing) if $missing;
+
+        my $missing;
+        if ($row->bodies_missing) {
+            my @missing = FixMyStreet::App->model("DB::Body")->search(
+                { id => [ split /,/, $row->bodies_missing ] },
+                { order_by => 'name' }
+            )->get_column('name')->all;
+            $missing = join(' / ', @missing) if @missing;
+        }
 
         my @dear;
         my %reporters = ();
@@ -400,7 +416,7 @@ sub send_reports {
         $h{missing} = '';
         if ($missing) {
             $h{missing} = '[ '
-              . sprintf(_('We realise this problem might be the responsibility of %s; however, we don\'t currently have any contact details for them. If you know of an appropriate contact address, please do get in touch.'), $missing->name)
+              . sprintf(_('We realise this problem might be the responsibility of %s; however, we don\'t currently have any contact details for them. If you know of an appropriate contact address, please do get in touch.'), $missing)
               . " ]\n\n";
         }
 
@@ -468,7 +484,7 @@ sub send_reports {
             }
         }
         my $sending_errors = '';
-        my $unsent = FixMyStreet::App->model("DB::Problem")->search( {
+        my $unsent = $rs->search( {
             state => [ 'confirmed', 'fixed' ],
             whensent => undef,
             bodies_str => { '!=', undef },
diff --git a/perllib/Open311/GetServiceRequestUpdates.pm b/perllib/Open311/GetServiceRequestUpdates.pm
index f7b758137..1e5f4dc6b 100644
--- a/perllib/Open311/GetServiceRequestUpdates.pm
+++ b/perllib/Open311/GetServiceRequestUpdates.pm
@@ -48,12 +48,12 @@ sub fetch {
 
         $self->suppress_alerts( $body->suppress_alerts );
         $self->system_user( $body->comment_user );
-        $self->update_comments( $o, { areas => $body->areas }, );
+        $self->update_comments( $o, $body );
     }
 }
 
 sub update_comments {
-    my ( $self, $open311, $body_details ) = @_;
+    my ( $self, $open311, $body ) = @_;
 
     my @args = ();
 
@@ -63,7 +63,7 @@ sub update_comments {
         push @args, $self->start_date;
         push @args, $self->end_date;
     # default to asking for last 2 hours worth if not Bromley
-    } elsif ( ! $body_details->{areas}->{$AREA_ID_BROMLEY} ) {
+    } elsif ( ! $body->areas->{$AREA_ID_BROMLEY} ) {
         my $end_dt = DateTime->now();
         my $start_dt = $end_dt->clone;
         $start_dt->add( hours => -2 );
@@ -75,7 +75,7 @@ sub update_comments {
     my $requests = $open311->get_service_request_updates( @args );
 
     unless ( $open311->success ) {
-        warn "Failed to fetch ServiceRequest Updates for " . join(",", keys %{$body_details->{areas}}) . ":\n" . $open311->error
+        warn "Failed to fetch ServiceRequest Updates for " . $body->name . ":\n" . $open311->error
             if $self->verbose;
         return 0;
     }
@@ -90,10 +90,8 @@ sub update_comments {
         my $problem;
         my $criteria = {
             external_id => $request_id,
-            # XXX This assumes that areas will actually only be one area.
-            bodies_str => { like => '%' . join(",", keys %{$body_details->{areas}}) . '%' },
         };
-        $problem = FixMyStreet::App->model('DB::Problem')->search( $criteria );
+        $problem = FixMyStreet::App->model('DB::Problem')->to_body($body)->search( $criteria );
 
         if (my $p = $problem->first) {
             my $c = $p->comments->search( { external_id => $request->{update_id} } );
diff --git a/perllib/Open311/GetUpdates.pm b/perllib/Open311/GetUpdates.pm
index 5007a1f82..bc55086f0 100644
--- a/perllib/Open311/GetUpdates.pm
+++ b/perllib/Open311/GetUpdates.pm
@@ -17,9 +17,8 @@ sub get_updates {
             api_key      => $body->api_key
         );
 
-        my $reports = FixMyStreet::App->model('DB::Problem')->search(
+        my $reports = FixMyStreet::App->model('DB::Problem')->to_body($body)->search(
             {
-                bodies_str => { like => "\%" . $body->id . "\%" },
                 state => { 'IN', [qw/confirmed fixed/] },
                 -and => [
                     external_id => { '!=', undef },
diff --git a/t/app/model/problem.t b/t/app/model/problem.t
index 0a5f63b05..82569d72a 100644
--- a/t/app/model/problem.t
+++ b/t/app/model/problem.t
@@ -457,7 +457,8 @@ foreach my $test ( {
         email_count   => 1,
         to            => qr'Gloucestershire County Council" <2226@example',
         dear          => qr'Dear Gloucestershire County Council,',
-        body          => $body_ids{2226}  . '|' . $body_ids{2649},
+        body          => $body_ids{2226},
+        body_missing  => $body_ids{2649},
         missing       => qr'problem might be the responsibility of Fife.*Council'ms,
     }, {
         %common,
@@ -529,6 +530,7 @@ foreach my $test ( {
         $problem->discard_changes;
         $problem->update( {
             bodies_str => $test->{ body },
+            bodies_missing => $test->{ body_missing },
             state => 'confirmed',
             confirmed => \'current_timestamp',
             whensent => $test->{ unset_whendef } ? undef : \'current_timestamp',
diff --git a/t/cobrand/two_tier.t b/t/cobrand/two_tier.t
index ad664aabd..b3d6ca7db 100644
--- a/t/cobrand/two_tier.t
+++ b/t/cobrand/two_tier.t
@@ -6,11 +6,11 @@ use FixMyStreet;
 use FixMyStreet::Cobrand;
 
 my @cobrands = (
-    [ hart => '%2333%' ],
-    [ oxfordshire  => '%2237%' ],
-    [ eastsussex   => '%2224%' ],
-    [ stevenage    => '%2347%' ],
-    [ warwickshire => '%2243%' ],
+    [ hart => 2333 ],
+    [ oxfordshire  => 2237 ],
+    [ eastsussex   => 2224 ],
+    [ stevenage    => 2347 ],
+    [ warwickshire => 2243 ],
 );
 
 FixMyStreet::override_config {
@@ -18,11 +18,10 @@ FixMyStreet::override_config {
 }, sub {
 
     for my $c (@cobrands) {
-        my ($m, $like) = @$c;
+        my ($m, $id) = @$c;
         my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($m);
-        my $problems_clause = $cobrand->problems_clause;
-        is_deeply $problems_clause,
-            { bodies_str => { like => $like } }, "problems_clause for $m";
+        my $body_restriction = $cobrand->body_restriction;
+        is $body_restriction, $id, "body_restriction for $m";
     }
 };
 
diff --git a/t/open311/getservicerequestupdates.t b/t/open311/getservicerequestupdates.t
index dac10d69b..0ab5b232d 100644
--- a/t/open311/getservicerequestupdates.t
+++ b/t/open311/getservicerequestupdates.t
@@ -18,6 +18,11 @@ my $user = FixMyStreet::App->model('DB::User')->find_or_create(
     }
 );
 
+my %bodies = (
+    2482 => FixMyStreet::App->model("DB::Body")->new({ id => 2482 }),
+    2651 => FixMyStreet::App->model("DB::Body")->new({ id => 2651 }),
+);
+
 my $requests_xml = qq{<?xml version="1.0" encoding="utf-8"?>
 <service_requests_updates>
 <request_update>
@@ -342,9 +347,8 @@ for my $test (
         $problem->state( $test->{start_state} );
         $problem->update;
 
-        my $council_details = { areas => { 2482 => 1 } };
         my $update = Open311::GetServiceRequestUpdates->new( system_user => $user );
-        $update->update_comments( $o, $council_details );
+        $update->update_comments( $o, $bodies{2482} );
 
         is $problem->comments->count, 1, 'comment count';
         $problem->discard_changes;
@@ -382,9 +386,8 @@ foreach my $test (
 
         $problem->comments->delete;
 
-        my $council_details = { areas => { 2482 => 1 } };
         my $update = Open311::GetServiceRequestUpdates->new( system_user => $user );
-        $update->update_comments( $o, $council_details );
+        $update->update_comments( $o, $bodies{2482} );
 
         my $comment = $problem->comments->first;
         is $comment->created, $dt, 'created date set to date from XML';
@@ -451,9 +454,8 @@ for my $test (
         my $o = Open311->new( jurisdiction => 'mysociety', endpoint => 'http://example.com', test_mode => 1, test_get_returns => { 'servicerequestupdates.xml' => $local_requests_xml } );
 
 
-        my $council_details = { areas => { $test->{area_id} => 1 } };
         my $update = Open311::GetServiceRequestUpdates->new( system_user => $user );
-        $update->update_comments( $o, $council_details );
+        $update->update_comments( $o, $bodies{$test->{area_id}} );
 
         is $problem->comments->count, $test->{p1_comments}, 'comment count for first problem';
         is $problem2->comments->count, $test->{p2_comments}, 'comment count for second problem';
@@ -491,8 +493,7 @@ subtest 'using start and end date' => sub {
         end_date => $end_dt,
     );
 
-    my $council_details = { areas => { 2482 => 1 } };
-    $update->update_comments( $o, $council_details );
+    $update->update_comments( $o, $bodies{2482} );
 
     my $start = $start_dt . '';
     my $end = $end_dt . '';
@@ -551,18 +552,17 @@ subtest 'check that existing comments are not duplicated' => sub {
         system_user => $user,
     );
 
-    my $council_details = { areas => { 2482 => 1 } };
-    $update->update_comments( $o, $council_details );
+    $update->update_comments( $o, $bodies{2482} );
 
     $problem->discard_changes;
     is $problem->comments->count, 2, 'two comments after fetching updates';
 
-    $update->update_comments( $o, $council_details );
+    $update->update_comments( $o, $bodies{2482} );
     $problem->discard_changes;
     is $problem->comments->count, 2, 're-fetching updates does not add comments';
 
     $problem->comments->delete;
-    $update->update_comments( $o, $council_details );
+    $update->update_comments( $o, $bodies{2482} );
     $problem->discard_changes;
     is $problem->comments->count, 2, 'if comments are deleted then they are added';
 };
@@ -612,8 +612,7 @@ foreach my $test ( {
             system_user => $user,
         );
 
-        my $council_details = { areas => { 2482 => 1 } };
-        $update->update_comments( $o, $council_details );
+        $update->update_comments( $o, $bodies{2482} );
 
         $problem->discard_changes;
         is $problem->comments->count, 2, 'two comments after fetching updates';
@@ -678,8 +677,7 @@ foreach my $test ( {
             suppress_alerts => $test->{suppress_alerts},
         );
 
-        my $council_details = { areas => { 2482 => 1 } };
-        $update->update_comments( $o, $council_details );
+        $update->update_comments( $o, $bodies{2482} );
         $problem->discard_changes;
 
         my $alerts_sent = FixMyStreet::App->model('DB::AlertSent')->search(