A better Propel query

I have 4 tables:

branches (contains branch info)
offered_class_types (each branch has a set of classes it has available)
class_sessions (each class type has its session, like day, evening, and night)
class_dates (each date corresponds to a session)

So my flow: each date corresponds to a session (by a code that links the two directly) which corresponds to a type by two things 1. a branch id and 2. a class name (branches can offer the same class types which can have the same sessions). Each class type corresponds to the branches table using that same branch id it used to correspond with the sessions table.

I would like to turn this code into a one query, instead of the two it is now. Prior to switching to Propel it was in a single query.

$class = ClassDatesQuery::create()->findPk(160);
$classSession = $class->getOfferedSessionsRelatedByCode();
$classType = OfferedClassTypesQuery::create()
   ->filterByType($classSession->getClassname())
   ->filterByBranchid($classSession->getBranchid())
   ->find();

Solution

A simple query, similar to the one below would have surficed, if only you had a simple relationship between the two tables:

$classType = OfferedClassTypesQuery::create()
  ->useClassDatesQuery()
    ->filterByPk(160)
  ->endUse()
  ->filterByXXX(YYY)
  ...
  ->find();

The complication is that you have more than one join condition, and those conditions are unknown before the query.

There are at least two(2) alternative approaches to this.

Option One:

Write your raw SQL query, and run it against your DB connection, and then manually hydrate the results set with the appropriate object/class wrapper like below:

$db = Propel::getConnection();
$sql = 'SELECT OfferedClassTypes.* FROM ...';
$query = $db->prepare($sql);
$formatter = new PropelObjectFormatter();

// Here you specify the class that matches
// the expected results set
$formatter->setClass('OfferedClassTypes');
$classType = $formatter->format($query);

Notice the OfferedClassTypes.*, because you only need columns from that table, so that you can hydrate by the matching Object

Option Two:

Put your raw SQL query into a view, quote:

Propel custom sql for view tables

So you'd maybe define your view like this:

CREATE VIEW ViewOfferedClassTypes AS
  SELECT
    ClassDates.ID as ClassDateID,
    OfferedClassTypes.*
    ...

Then in your schema you'd have:

<table
  name="ViewOfferedClassTypes"
  phpName="ViewOfferedClassTypes"
  readOnly="true"
  skipSql="true">
  <!-- More columns from the OfferedClassTypes table -->
  <column name="ClassDateID" ... /><!-- From ClassDates table -->
</table>

Note the ClassDateID column, so you can use in your filter:

$classType = ViewOfferedClassTypesQuery::create()
  ->filterByClassDateID(160)
  ->find();

Option 1 is quick and dirty, while option 2 is more elaborate yet cleaner/more organised.

I hope this helps, and good luck.