VehicleJourney: Add #exclude_journeys_without_time_tables

A new class method that allows us to filter out vehicle journeys that
aren't associated with `TimeTable`s.

This is what back the filter toggle on VehicleJourneys#index for
"Afficher les courses sans calendrier".

Using a subquery to allow us to filter out duplicates, so we don't end
up with duplicate vehicle journeys in the final result set. Then we just
remove the vehicle journeys that have no entries in the
`time_tables_vehicle_journeys` through table, leaving us with only the
vehicle journeys that have associated calendars.

Refs #3427

1 files changed, 21 insertions, 0 deletions

diff --git a/app/models/chouette/vehicle_journey.rb b/app/models/chouette/vehicle_journey.rb
index 852139625..514e89fb1 100644
--- a/app/models/chouette/vehicle_journey.rb
+++ b/app/models/chouette/vehicle_journey.rb
@@ -252,5 +252,26 @@ module Chouette
         )
     end
 
+    def self.exclude_journeys_without_time_tables
+      # Joins the VehicleJourney–TimeTable through table to remove
+      # VehicleJourneys that don't have an associated TimeTable. The subquery
+      # allows us to select unique VehicleJourney IDs from the through table,
+      # which prevents VehicleJourneys from appearing multiple times in the
+      # result set due to the join. Using a DISTINCT in a subquery avoids messy
+      # DISTINCT and GROUP BY clauses in the main query.
+      self
+        .joins('
+          LEFT JOIN (
+            SELECT DISTINCT
+              "time_tables_vehicle_journeys"."vehicle_journey_id"
+            FROM "time_tables_vehicle_journeys"
+          ) AS "distinct_time_tables_vehicle_journeys"
+          ON "distinct_time_tables_vehicle_journeys"."vehicle_journey_id" =
+            "vehicle_journeys"."id"
+        ')
+        .where('"distinct_time_tables_vehicle_journeys"."vehicle_journey_id"
+          IS NOT NULL')
+    end
+
   end
 end