From 54329470c1a432ba669f4d510e9b50ab790ffa39 Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Sun, 27 Dec 2015 21:50:27 +0100
Subject: /individual: let the DB do the join

Needs quite some RAM at the moment, but it's better in the long run
---
 index.pl | 38 +++++++++++++-------------------------
 1 file changed, 13 insertions(+), 25 deletions(-)

diff --git a/index.pl b/index.pl
index 7bb6b9a..1298b8a 100644
--- a/index.pl
+++ b/index.pl
@@ -742,47 +742,35 @@ get '/individual' => sub {
 		$where_clause .= ' and delay is not null';
 	}
 
-	my $messages = [];
-	my $res      = $self->app->dbh->selectall_arrayref(
+	my $res = $self->app->dbh->selectall_arrayref(
 		qq{
 		select station_codes.name, scheduled_time, delay, is_canceled,
-		stations.name, train_types.name, train_no, lines.name, platform
-		from departures
+		stations.name, train_types.name, train_no, lines.name, platform,
+		}
+		  . join( ', ', map { "msg$_" } ( 1 .. 99 ) ) . qq{
+		from departures_with_messages
 		join station_codes on station = station_codes.id
 		join stations on destination = stations.id
 		join train_types on train_type = train_types.id
 		left outer join lines on line_no = lines.id
 		where $where_clause
 		order by $order
-		limit 1000
+		limit 100
 	}
 	);
 
-	for my $msg ( 1 .. 99 ) {
-		my $msg_res = $self->app->dbh->selectall_arrayref(
-			qq{
-			select (msgtable.train_id is not null)
-			from departures
-			left outer join msg_$msg as msgtable
-			using (scheduled_time, train_id)
-			where $where_clause
-			order by $order
-			limit 1000
-			}
-		);
-		for my $i ( 0 .. $#{$res} ) {
-			if ( $msg_res->[$i][0] ) {
-				push( @{ $messages->[$i] }, $translation{$msg} // $msg );
-			}
-		}
-	}
-
 	for my $i ( 0 .. $#{$res} ) {
+		my @messages;
 		my $row = $res->[$i];
+		for my $msg ( 1 .. 99 ) {
+			if ( $row->[ 8 + $msg ] ) {
+				push( @messages, $translation{$msg} // $msg );
+			}
+		}
 		$row->[0]
 		  = Travel::Status::DE::IRIS::Stations::get_station( $row->[0] )->[1];
 		$row->[4] = decode( 'utf-8', $row->[4] );
-		push( @{$row}, $messages->[$i] );
+		$row->[9] = [@messages];
 	}
 
 	$self->render(
-- 
cgit v1.2.3