Hi David,
joins with the same table are possible but not very comfortable.
Below, i attached an example how we were doing a join Thread -> Entity
-> Watch -> Entity.
Note that the join Entity -> Watch is a complex join with an
additional criteria (retrieve only watches with matching user-ids).
--------------------------- SNIP Begin peer method-----------------------------
public static function doSelectJoinWatch(Criteria $c, $con = null)
{
/* This function does a query Thread ---> Entity ---> Watch -->
Entity with three JOINs in the query. After the query
* all objects are hydrated and glued together.
*/
$c = clone $c;
if ($c->getDbName() == Propel::getDefaultDB()) {
$c->setDbName(self::DATABASE_NAME);
}
/* Add all (normal) columns of each table to the query and
calculate positions in the result set.
* Since all colums are numered in the result set, we have to
calculate the offset for each table.
* Assuming Table1 has 2 columns, Table2 5 and Table3 4, the first
Table1 column is at result_column 0,
* the first Table2 column is located 2 columns later and the first
Table3 column is located
* at offset 2+5=7.
*/
/* First: Table Trade. Offset is not calculated because it is
always 0. There is also no need for a join since
* we start with this table.
*/
ThreadPeer::addSelectColumns($c);
/* Second: Table Entity */
EntityPeer::addSelectColumns($c);
$startcol_entity = (ThreadPeer::NUM_COLUMNS -
ThreadPeer::NUM_LAZY_LOAD_COLUMNS) + 1;
$c->addJoin(ThreadPeer::ENTITY_GID, EntityPeer::GID);
/* Third: Table Watch */
$startcol_watch = $startcol_entity + EntityPeer::NUM_COLUMNS ;
WatchPeer::addSelectColumns($c);
$c->addJoin(EntityPeer::GID,WatchPeer::DESTINATION_GID,Criteria::LEFT_JOIN);
/* Fourth: Table Entity / WEntity */
//see http://www.symfony-project.com/forum/index.php/m/10570/ for
addAlias examples
$c->addAlias("WEntity",EntityPeer::TABLE_NAME);
$startcol_wentity = $startcol_watch + WatchPeer::NUM_COLUMNS ;
EntityPeer::addAliasSelectColumns('WEntity',$c);
$c->addJoin(WatchPeer::ENTITY_GID,
EntityPeer::alias("WEntity",EntityPeer::GID).' AND
'.EntityPeer::alias("WEntity",EntityPeer::USER_ID).'='.sfContext::getInstance()->getUser()->getId(),
Criteria::LEFT_JOIN);
/* And a condition to prevent a successful join on Watch but an
unsuccessful on WEntity */
$cton1 = $c->getNewCriterion(WatchPeer::ID, null,Criteria::ISNULL);
$cton2 =
$c->getNewCriterion(EntityPeer::alias("WEntity",EntityPeer::GID),null,
Criteria::ISNOTNULL);
$cton1->addOr($cton2);
$c->addAnd($cton1);
/* Execute query */
$rs = BasePeer::doSelect($c, $con);
/* Prepare an empty array for results */
$results = array();
/* Also prepare Arrays for quick-lookup of previously fetched
objects. The Arrays are indiced
* by the primary key of the object stored in the field.
*/
$entities = array();
$watches = array();
$wentities = array();
/* Iterate through all result rows (which contain results from all
tables) */
while($rs->next()) {
/* Get Object-Mapping for Thread class and hydrate the data in $obj1 */
$omClass = ThreadPeer::getOMClass();
$cls = Propel::import($omClass);
$obj1 = new $cls();
$obj1->hydrate($rs);
/* Get Object-Mapping for Entity class and hydrate everything in $obj2 */
$omClass = EntityPeer::getOMClass();
$cls = Propel::import($omClass);
$obj2 = new $cls();
$obj2->hydrate($rs, $startcol_entity);
/* Get Object-Mapping for Watch class and hydrate everything in $obj3 */
$omClass = WatchPeer::getOMClass();
$cls = Propel::import($omClass);
$obj3 = new $cls();
$obj3->hydrate($rs, $startcol_watch);
/* Get Object-Mapping for WEntity class and hydrate everything in $obj4 */
$omClass = EntityPeer::getOMClass();
$cls = Propel::import($omClass);
$obj4 = new $cls();
$obj4->hydrate($rs, $startcol_wentity);
/* $obj2 (Entity) is not really new but in this query the join
is used with a filter (userid=currentuser).
* Setting the flag new prevents symfony to do a unlimited query
when calling
* $thread->getEntity()->getWatchsRelatedByDestinationGid().
*/
$obj2->setNew(true);
/* Now we move from the first table behind the originating table
(which is entity)
* along all joins to the last table (user). If the primary key
of the table is in the specific
* lookup-array, the object has already been instanciated. All
following objects in the join-sequence
* have also been instanciated (and referenced by the found object).
*/
/* Check if we found the current entity in one of the previous rows */
if(array_key_exists($obj2->getPrimaryKey(),$entities))
{
// Reference from new Trade to existing Entity
$entity = $entities[$obj2->getPrimaryKey()]; //use reference
to prevent cloning of the entity (which will end in a mess)
$entity->addThread($obj1);
} else {
// Reference from new Trade to new Entity and add new Entity
to lookup-table
$obj2->initThreads();
$obj2->addThread($obj1);
$entities[$obj2->getPrimaryKey()]= $obj2;
/* Check if we found the current entity in one of the previous rows */
if(array_key_exists($obj3->getPrimaryKey(),$watches))
{
// Reference from new Entity to existing User
$watch = $watches[$obj3->getPrimaryKey()]; //use reference
to prevent cloning of the entity (which will end in a mess)
$watch->addEntity($obj2);
} else {
// Reference from new Entity to new User and add new User to
lookup-table
//WARNING: SINCE WATCH HOLDS THE REFERENCE TO ENTITY, WE
HAVE TO INITIALIZE THE REFERENCE AT THE ENTITY OBJECT!
$obj2->initWatchsRelatedByDestinationGid();
$obj2->addWatchRelatedByDestinationGid($obj3);
$watches[$obj3->getPrimaryKey()]= $obj3;
/* Check if we found the current watch in one of the previous rows */
if(array_key_exists($obj4->getPrimaryKey(),$wentities))
{
// Reference from new Entity to existing User
$wentity = $wentities[$obj4->getPrimaryKey()]; //use
reference to prevent cloning of the entity (which will end in a mess)
$wentity->addEntity($obj3);
} else
/* No objects have been instanciated yet */
{
// Reference from new Entity to new User and add new User
to lookup-table
$obj4->initWatchsRelatedByEntityGid();
$obj4->addWatchRelatedByEntityGid($obj3);
$wentities[$obj4->getPrimaryKey()]= $obj4;
}
}
}
----------------------- End peer method ------------------------
----------------------- Begin entity peer ---------------------------
public static function addAliasSelectColumns($alias,Criteria $criteria)
{
foreach(self::getFieldNames(BasePeer::TYPE_COLNAME) as $fieldName)
{
$criteria->addSelectColumn(self::alias($alias,$fieldName));
}
}
----------------------- End entity peer ---------------------------
Carsten
On Nov 19, 2007 7:37 PM, Wang David <[EMAIL PROTECTED]> wrote:
>
> How does one write the criteria for this example:
>
> table (person)
> - id
> - mom_id
> - dad_id
> ---
>
> the sql:
> select *
> from person, person mom
> where mom on mom.id = person.mom_id
>
> thanks for the help!
>
> -d
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"symfony users" group.
To post to this group, send email to symfony-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/symfony-users?hl=en
-~----------~----~----~----~------~----~------~--~---
