Michael,
Cayenne query translator indeed creates joins before the rest of the
qualifier parts. I still think that this is the correct behavior and the
fact that your other query worked is merely a coincedence.
The the first part in your where clause, "(t0.GLOBAL = 'J')", would
still do a join internally, but thanks to DISTINCT it is not visible.
To see what I mean by "coincedence", try deleting ALL rows from
tb_offer_usergroup_assoc table, and re-run your SQL.
A solution to your problem would be to run two queries instead of one
(since Cayenne currently does not support UNION), first for global
offers, then for the rest of them.
Andrus
Michael Schuldt wrote:
>
> The resulting sql-statement looks like this:
> SELECT DISTINCT t0.* FROM tb_offers t0,
> tb_offer_usergroup_assoc t1,
> tb_usergroup t2,
> tb_usergroup_firm_assoc t3,
> tb_firm t4
> WHERE t0.ID = t1.OFFER_ID AND
> t1.USERGROUP_ID = t2.ID AND
> t2.ID = t3.USERGROUP_ID AND
> t3.FIRM_ID = t4.ID AND
> (((t0.GLOBAL = 'J') OR (t3.FIRM_ID = 820)) AND (t0.STATUS = 'on'))
>
> (rem: I shortened the listing of all t0-attributes to t0.*)
>
> But this query doesn't do the right thing for me. I need the following
> statement:
> SELECT DISTINCT t0.* FROM tb_offers t0,
> tb_offer_usergroup_assoc t1,
> tb_usergroup t2,
> tb_usergroup_firm_assoc t3,
> tb_firm t4
> WHERE (((t0.GLOBAL = 'J') OR
> (t0.ID = t1.OFFER_ID AND t1.USERGROUP_ID = t2.ID AND
> t2.ID = t3.USERGROUP_ID AND
> t3.FIRM_ID = t4.ID AND t3.FIRM_ID = 820)) AND
> (t0.STATUS = 'on'))
>
This archive was generated by hypermail 2.0.0 : Sun Feb 16 2003 - 12:58:40 EST