Peter Robins, his website

Setting up this site in Symfony

Phase 4: adding db-based pages; 2: flights

The next mini-app is the budget flights database. This is stored in an SQLite file, but here we hit another snag, in that it is an SQLite 3 file whereas Propel is behind the times and only manages SQLite 2 files. So we have to convert it - all rather unsatisfactory. (To be absolutely precise, Symfony currently uses Propel 1.2, which uses Creole, which is no longer actively developed. Propel 1.3 uses PDO, now standard in PHP, and is currently in beta; no doubt when it is released Symfony will change to it.) There are 3 tables for the actual flights: airlines, airports, and routes, a xref of which airlines fly between which airports. Airlines and airports are keyed on their appropriate code, and the routes table has fromport, toport and airline columns which link to these.

Besides the home/intro page, the main page displays a list of flights for a particular airport sorted by destination country and airport. Users can pick which airport they want from a list of airports by country, or on a map, currently in SVG format though I may convert this to Google Maps at some point. There is also a coverage page listing which airports/lines are covered (basically, those I'm interested in).

I later included the ferry crossings as well, which is analagous: ferrycos, keyed on a code called 'co', ports, keyed on name, and ferries, which links the two in the same way as routes. There is a list of which ferrycos ply a particular route.

As with ballads, the data is all maintained by me, so there's no need for CUD.

Prelims: routing, new db, new module, meta entries
  1. routing: the intro and map pages are standard html, so use the show action. The other options need appropriate entries in apps/main/config/routing.yml. As with the ballads, we'll cover the eventuality that someone omits the airport or from-to code, giving them the airportlist: 
    flight_coverage:
  url:   /flights/coverage
  param: { module: flights, action: coverage }

airport_list:
  url:   /flights/airportlist
  param: { module: flights, action: airportlist }

route_list:
  url:   /flights/routelist/:airport
  param: { module: flights, action: routelist }

routes_list:
  url:   /flights/routelist/
  param: { module: flights, action: airportlist }

ferry_list:
  url:   /flights/ferrylist/:from-to
  param: { module: flights, action: ferrylist }

ferrys_list:
  url:   /flights/ferrylist/
  param: { module: flights, action: airportlist }
  2. as with the ballads, we have to add the db to config/databases.yml, create a new module - flights - and create a view.yml for the module, something we should now be familiar with
Creating the model
Having converted the db, we can now generate config/flights-schema.yml. As before, we have to put the correct connection settings in config/propel.ini, build the schema, and rename it. Make sure all the primary keys are correctly defined in the database, so Propel knows how to set up the joins. As before, we have to insert the links in the routes and ferries tables before building the model. Adding the foreign references gives us the join methods in the peer classes, which we can use in the actions.
Setting up the actions
Now we need to set up the actions that correspond to the urls set up in routing.yml.
  1. introductory/home and map pages: these are simple static pages, so we can replace the default executeIndex() with the executeShow() we are now familiar with
  2. coverage: this does a simple list of the airports with 'main' = 'y' in name sequence, and a list of the airlines table, also in name sequence: 
      public function executeCoverage()
  {
// airports covered are indicated by 'y' in the main column
    $c = new Criteria();
    $c->add(AirportsPeer::MAIN, 'y');
    $c->addAscendingOrderByColumn(AirportsPeer::NAME);
    $this->airports = AirportsPeer::doSelect($c);

    $c = new Criteria();
    $c->addAscendingOrderByColumn(AirlinesPeer::NAME);
    $this->airlines = AirlinesPeer::doSelect($c);
  }
  3. airport list: this lists first the airports in GB, then those outside GB, then the ferry routes, which we 'group by' so we don't have to use 'distinct': 
      public function executeAirportlist()
  {
// get GB airports first
    $c = new Criteria();
    $c->add(AirportsPeer::COUNTRY, 'gb');
    $c->addAscendingOrderByColumn(AirportsPeer::NAME);
    $this->gbAirports = AirportsPeer::doSelect($c);
// then non-GB airports
    $c = new Criteria();
    $c->add(AirportsPeer::COUNTRY, 'gb', Criteria::NOT_EQUAL);
    $c->addAscendingOrderByColumn(AirportsPeer::COUNTRY);
    $c->addAscendingOrderByColumn(AirportsPeer::NAME);
    $this->nongbAirports = AirportsPeer::doSelect($c);
// finally ferry routes
    $c = new Criteria();
    $c->addAscendingOrderByColumn(FerriesPeer::FROMPORT);
    $c->addAscendingOrderByColumn(FerriesPeer::TOPORT);
    $c->addGroupByColumn(FerriesPeer::FROMPORT);
    $c->addGroupByColumn(FerriesPeer::TOPORT);
    $this->ferries = FerriesPeer::doSelect($c);
  }
  4. ferry list: the parameter for this is 'from-to' which first has to be split, then it's a simple read of the ferries table for that criteria. As this is parameter-driven, we have to allow for invalid ones, returning 'Error' if so: 
      public function executeFerrylist()
  {
    $this->bits = split('-', $this->getRequestParameter('from-to'), 2);
    $c = new Criteria();
    $c->add(FerriesPeer::FROMPORT, $this->bits[0]);
    $c->add(FerriesPeer::TOPORT, $this->bits[1]);
    if (!$this->routes = FerriesPeer::doSelect($c))
	return 'Error';
  }
  5. routes list: in principle, this is straightforward: read the routes table for the airport given in the parameter (again, we have to cater for erroneous codes), sorting by country and name of the destination airport. However, in practice it's not that simple. The routes table records all those flights going from my chosen airports, so the simple case is a query for one of those airports, for example, Manchester; this is a query "fromport='man'". The airport name/country columns I want to sort by are in the airport table, so for this case we can use the join doSelect() Propel sets up in RoutesPeer based on the foreign key definition: 
      public function executeRoutelist()
  {
    $c = new Criteria();
    $c->add(RoutesPeer::FROMPORT, $this->getRequestParameter('airport'));
    $c->addAscendingOrderByColumn(AirportsPeer::COUNTRY);
    $c->addAscendingOrderByColumn(AirportsPeer::NAME);
    if (!$this->routes = RoutesPeer::doSelectJoinAirportsRelatedByToport($c))
	return 'Error';
  }
    This presents the template with an array of the selected route objects sorted by the criteria in the joined table. The template needs columns from other tables, for example, the airline name. If these are not in the join, the get() methods have to access the database on every iteration, so it would be better to include these in the join as well, with the doSelectJoinAll() method. Unfortunately, there is a known problem with Propel that it cannot handle the case where the same table should be joined twice. So we have to put up with the increased database access (or create our own method, of course). You can see the database accesses made by using the debug bar in the development environment.
    That is the simple case. However, I also want to know which of my chosen airports I can fly to from other cities, for example Paris; this is a query "toport='cdg'", and fromport and toport are the other way round. It's easy enough to set up a 2nd query with TOPORT as the selection criteria, and joined RelatedByFromport, and then decide which to use based on whether 'main' is set for that airport or not. However, there would have to be similar logic in the template so it knew whether to use fromport or toport. In addition, there's the case of flights between 2 of my chosen airports; here the parameter airport might be in either fromport or toport. I could do both doSelect()s and then merge the 2 arrays, but then the airports will not be sorted in the correct sequence (it'll have the the 'from's first, then the 'to's) and the template will still not know which field to output. What I really want to do here is not a simple query on the routes table, but a query on the union between the 2 queries, in SQL terms, something like: 
    SELECT '$airport', city, airline, startdate FROM
(SELECT toport as city, airline, startdate FROM routes WHERE fromport = '$airport'
UNION SELECT fromport as city, airline, startdate FROM routes WHERE toport = '$airport'),
airports
WHERE city = airports.code
ORDER BY country, airports.name
    The model set up by Propel cannot manage this, so we have to bypass it, use the SQL to create the result set, and then use the populateObjects() method in the routes peer class to set up the route object array: 
        $airport = $this->getRequestParameter('airport');
    $con = Propel::getConnection('flights');
    $sql = "SELECT '$airport', city, airline, startdate FROM (SELECT toport as city, airline, startdate FROM routes WHERE fromport = '$airport' UNION SELECT fromport as city, airline, startdate FROM routes WHERE toport = '$airport'), airports WHERE city = airports.code ORDER BY country, airports.name";
    $stmt = $con->createStatement();
    $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
    if (!$this->routes = RoutesPeer::populateObjects($rs))
	return 'Error';
    I have put this in the action, as that's simplest, but really it belongs in with the model and should be made a function of lib/model/RoutesPeer.php. This would especially be the case if it were used in more than one action
Adapting the templates
  • the home page is simple text, and the map page is also a simple static file; the SVG file has to be changed to use the new urls and also has to be put in the assets directory web/ (I recreate this SVG file offline every time I make changes to the database)
  • the action for the ferry and route lists returns 'Error' if the parameter is not valid, so there needs to be a ...Error.php displaying a 'no such airport' message or whatever, as well as a Success.php. The other templates use the same sort of procedure as the ballads. Here's an example from routelistSuccess.php: 
    Flights from <?php echo $routes[0]->getAirportsRelatedByFromport()->getName() ?> (<?php echo $sf_params->get('airport') ?>)
Was it all worth it?

If you just want to set up a few quick programs for this mini-app, then, clearly, no. This is far too much work. But if your aim is to learn how to use Symfony and Propel, then, yes. Frameworks like this always sound easy to use in the manual, but it's only when you try and use them in real-life situations that you find out where the limitations are. Nothing is ever perfect :-(

February 2008