Using joins on tables with combined join clause
Sven Kretschmann <Sven.Kretschmann <at> exozet.com>
2008-05-06 14:22:37 GMT
Hi Hans,
just to reroll the case again...
I just found some time to test the patch against the existing unit tests. The CriteriaTest package breaks in
nearly every test, as the BasePeer takes the column type into account, asking the DBMap. Latter throws an
exception as TABLE_A, etc. is not defined. In addition to that the testJoinObject throws an error, as the
constructor API has changed and no longer accepts a string as first parameter, but a Criterion object.
I'm not sure what to do next (at least in this case ;) )...
Sven
-----Ursprüngliche Nachricht-----
Von: Hans Lellelid [mailto:hans <at> velum.net]
Gesendet: Donnerstag, 21. Februar 2008 12:12
An: users <at> propel.tigris.org
Betreff: Re: AW: [propel] Using joins on tables with combined join clause
Hi Sven,
This patch looks good to me (though I have not applied it to my working copy). It definitely is a big change,
but as long as it doesn't break any existing Criteria usage (or unit tests), I'm fine with this being
applied. I think supporting more complex joins is a great feature.
Hans
Sven Kretschmann wrote:
>
> Hi Hans,
>
> I wrote the patch from scratch, following another concept to handle joins as the existing code.
>
> Joins do hold a criterion object containing all clauses needed. The old API is still supported, so there is
no need to refactor existing code.
>
> I would have liked to answer to the ticket mentioned in your previous
> answer, as it tells just the problem I had, but for spam prevention
> reasons I was neither able to answer nor attach the patch file. I
> would appreciate you to give me a trac account for preventing this ;)
>
> The usage of the joins after patching is like the following example shows:
>
> $criteria = new Criteria();
> $criterion = $criteria->getJoinCriterion(MyPeer::COLUMN,
> MyReferencePeer::FOREIGN_COLUM, Criteria::EQUAL);
> $criterion->addAnd($criteria->getNewCriterion(MyReferencePeer::ANOTHER
> _COLUMN, $value, Criteria::EQUAL));
>
> $criteria->addJoin($criterion, Criteria::LEFT_JOIN);
>
> The difference between join criterions and normal criterions is the way the right column or the value
respectively are put into the query. The join criterions do put the value plain as is into the query,
whereas the normal criterions handle it the way the way they used to.
>
> I will be testing this patch over the next time, as it is a bit more complex there may still be some traps to
fall into.
>
> Regards
>
> Sven
>
> Sven Kretschmann
> IT Developer
>
> E-Mail: sven.kretschmann <at> exozet.com
> URL: http://www.exozet.com/
> Tel.: +49 (30) 24 65 60 - 0
> Fax: +49 (30) 24 65 60 - 29
>
> Exozet Berlin GmbH
> Oberbaum City
> Rotherstr. 20
> 10245 Berlin
>
> Geschäftsführer: Frank Alexander Zahn
> Handelsregisternummer: HRB 58797 Amtsgericht Charlottenburg
>
> --- NEWS --------------------------------------------------------
>
> exozet interaktiviert die ZDF Mediathek
>
> exozet interact hat die ZDF Mediathek mit einer neuen Video-On-Demand-Anwendung ausgestattet und die
beliebte Plattform damit um neue Rückkanalfunktionen erweitert. Der auf Basis der preisgekrönten
IPTV-Komplettlösung TVNEXT entwickelte Video-Player ermöglicht den Nutzern der Mediathek,
einzelne Videobeiträge zu kommentieren und zu bewerten. Für die Online-Redaktion des ZDF schafft die
Anwendung ein hohes Maß an Flexibilität in der Präsentation interaktiver Bewegtbildinhalte.
>
> http://www.zdf.de/ZDFmediathek
> http://www.exozet.com/tvnext
> -----Ursprüngliche Nachricht-----
> Von: Hans Lellelid [mailto:hans <at> velum.net]
> Gesendet: Mittwoch, 20. Februar 2008 02:32
> An: users <at> propel.tigris.org
> Betreff: Re: [propel] Using joins on tables with combined join clause
>
> Hi Sven,
>
> Sven Kretschmann wrote:
>
>>
>> Hi there,
>>
>> I have to develop an application using reference tables with a combined join clause of source id and
fieldname. As the referenced entries may be empty I'll use a left join, but this is nonrelevant for this case.
>> When using the normal Criteria::adJoin method I can only give one column per table, instead of giving
both keys I'd like to use or better give a criterion containing all clauses to use for the join.
>>
>> The query I'd like to build looks like this:
>>
>> SELECT my_table.ID FROM my_table LEFT JOIN my_reference_table ON
>> my_reference_table.source_id = my_table.id AND
>> my_reference_table.fieldname = 'reference_field';
>>
>> I do not want to use the workaround $criteria->add('my_reference_table.fieldname',
"IFNULL(my_reference_table.fieldname, 'reference_field') = 'reference_field'",
Criteria::CUSTOM); for performance reasons.
>>
>>
>
> Does this ticket look like it would address your need?
> http://propel.phpdb.org/trac/ticket/167
>
> While there's not a guarantee that we'll get this added for initial 1.3, I would like to see this supported.
The patch is not up-to-date with the way we're building the SELECT SQL, though, so there's some work needed
there. If you want to help implement this, it would be very welcome.
>
> Cheers,
> Hans
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe <at> propel.tigris.org
> For additional commands, e-mail: users-help <at> propel.tigris.org
>
>
> ----------------------------------------------------------------------
> --
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe <at> propel.tigris.org
> For additional commands, e-mail: users-help <at> propel.tigris.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe <at> propel.tigris.org
For additional commands, e-mail: users-help <at> propel.tigris.org
RSS Feed