Code snippets for symfony 1.x


Refine Tags

Snippets tagged "propel join"

performing custom joins and hydrations with Propel

People using Propel who want to perform custom joins and especially hydrations probably all faced the limited functionality that Propel offers for this: the probably well known doSelectJoinAll and doSelectJoinXXX methods (and similar for the count functionality)

Whenever you want to join only two related tables (or more, but not all), a related-related table (or even deeper) or a table that is related by multiple foreign-keys (like created_by and updated_by both refering to a user) you will find it gets hard to do this with the default functionality offered by Propel.

I have written a Helper/Plugin that solves this limitation, that can be downloaded from the plugin-pages:

The plugin contains:

The plugin does not change the way how to work with Propel, it will only expand the possibilities while remaining completely backwards compatible.

An example of how to use this helper:

Imagine you have a class of albums, and that every album can contain photos as well as other albums. You request all photo's sorted reversed on album name and join the related table to get the table-name in one go.

    name:     varchar(50)
    name:     varchar(50)

php code:

$criteria = new Criteria();
$sortColumn = AlbumPeer::alias(str_replace('.', '_', 'Photo.Album') , AlbumPeer::NAME); // this should be done nicer some day
$objectPaths = array('Photo', 'Photo.Album'); // this can be reduced (is similar) to array('Photo.Album');
$criteria = addJoinsAndSelectColumns($criteria, $objectPaths);
$photos = hydrate($criteria, $objectPaths, $connection = null); // I haven't decided if I want hydrate to perform the joining as well
foreach ($photos as $photo)
  echo $photo->getAlbum()->getName()." -> ".$photo->getName()."<br>\n";

The resulting sql is:

SELECT Photo.ID, Photo.ALBUM_ID, Photo.NAME, Photo_Album.ID, Photo_Album.ALBUM_ID, Photo_Album.NAME FROM `photo` `Photo` LEFT JOIN album Photo_Album ON (Photo.ALBUM_ID=Photo_Album.ID) ORDER BY Photo_Album.NAME DESC

I designed so called ObjectPaths and PropertyPaths together with a colleague of mine (Frans van der Lek), to make it easier to define classes and respectively properties of the (related) classes. The Object Paths contain the name of the base-class, followed by the relation-names defined in the peer-classes. These relation-names are defined by the builder, but can be extended/modified by extending the basePeer class (getRelations)

You can extend your Classes with custom Get-methods that can be accessed with the property-paths. I will show how this all comes to use, in my next article about the sfDataSourcePlugin, that I will release soon. (A plugin that provides a generic interface to select, sort, filter and iterate-over rows, no matter if they are provided by Propel, Doctrine, an Array, an Imap connection, etc. The Datasource interface is very light (in contradiction to dbFinder (no offense)) which makes it easy to write your own implementation)

Please provide feedback, questions and if desired I can add more examples/info.

by Leon van der Ree on 2009-06-07, tagged criteria  custom  database  hydrate  join  model  propel  sfpropelhelperplugin 
(1 comment)