store related stations; respect them when looking up connections

4 files changed, 69 insertions, 14 deletions

diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index b9074bc..d13b2a7 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -1929,6 +1929,23 @@ my @migrations = (
 			}
 		);
 	},
+
+	# v50 -> v51
+	# store related HAFAS stations
+	sub {
+		my ($db) = @_;
+		$db->query(
+			qq{
+				create table related_stations (
+					eva integer not null,
+					meta integer not null,
+					unique (eva, meta)
+				);
+				create index rel_eva on related_stations (eva);
+				update schema_version set version = 51;
+			}
+		);
+	},
 );
 
 sub sync_stations {
diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm
index b83eea5..12bfd0f 100755
--- a/lib/Travelynx/Controller/Traveling.pm
+++ b/lib/Travelynx/Controller/Traveling.pm
@@ -960,6 +960,10 @@ sub station {
 					( $_->datetime // $_->sched_datetime )->epoch
 					  < $now + 30 * 60
 				  } $status->results;
+				$self->stations->add_meta(
+					eva  => $status->station->{eva},
+					meta => $status->station->{evas}
+				);
 				$status = {
 					station_eva  => $status->station->{eva},
 					station_name => (
diff --git a/lib/Travelynx/Model/Journeys.pm b/lib/Travelynx/Model/Journeys.pm
index 3fa2d96..3f2c0dd 100755
--- a/lib/Travelynx/Model/Journeys.pm
+++ b/lib/Travelynx/Model/Journeys.pm
@@ -1735,21 +1735,20 @@ sub get_connection_targets {
 		return;
 	}
 
-	my $res = $db->query(
-		qq{
-			select
-			count(checkout_station_id) as count,
-			checkout_station_id as dest
-			from journeys
-			where user_id = ?
-			and checkin_station_id = ?
-			and real_departure > ?
-			group by checkout_station_id
-			order by count desc;
+	my $dest_ids = [ $dest_id, $self->{stations}->get_meta( eva => $dest_id ) ];
+
+	my $res = $db->select(
+		'journeys',
+		'count(checkout_station_id) as count, checkout_station_id as dest',
+		{
+			user_id            => $uid,
+			checkin_station_id => $dest_ids,
+			real_departure     => { '>', $threshold }
 		},
-		$uid,
-		$dest_id,
-		$threshold
+		{
+			group_by => ['checkout_station_id'],
+			order_by => { -desc => 'count' }
+		}
 	);
 	my @destinations
 	  = $res->hashes->grep( sub { shift->{count} >= $min_count } )
diff --git a/lib/Travelynx/Model/Stations.pm b/lib/Travelynx/Model/Stations.pm
index ecd8adb..147219f 100644
--- a/lib/Travelynx/Model/Stations.pm
+++ b/lib/Travelynx/Model/Stations.pm
@@ -50,6 +50,41 @@ sub add_or_update {
 	);
 }
 
+sub add_meta {
+	my ( $self, %opt ) = @_;
+	my $db   = $opt{db} // $self->{pg}->db;
+	my $eva  = $opt{eva};
+	my @meta = @{ $opt{meta} };
+
+	for my $meta (@meta) {
+		if ( $meta != $eva ) {
+			$db->insert(
+				'related_stations',
+				{
+					eva  => $eva,
+					meta => $meta
+				},
+				{ on_conflict => undef }
+			);
+		}
+	}
+}
+
+sub get_meta {
+	my ( $self, %opt ) = @_;
+	my $db  = $opt{db} // $self->{pg}->db;
+	my $eva = $opt{eva};
+
+	my $res = $db->select( 'related_stations', ['meta'], { eva => $eva } );
+	my @ret;
+
+	while ( my $row = $res->hash ) {
+		push( @ret, $row->{meta} );
+	}
+
+	return @ret;
+}
+
 sub get_for_autocomplete {
 	my ($self) = @_;