MEMBER OF should be generating a sub select or a left join. This is simmilar to the IS NULL bug we recently fixed.
-dain
On Friday, January 17, 2003, at 02:49 AM, Alexey Yudichev wrote:
The query you proposed makes a join using both relationships as well:
SELECT t0_c.id FROM storecategory t0_c, partner t2_p, partner t3_c_partners, partner_categories_stor_1cl2gdd t4_c_partners_RELATION_TABLE, partner t1_c_partnerOwner WHERE (t0_c.id = ? AND (t1_c_partnerOwner.id = ? OR (t2_p.id = ? AND (t3_c_partners.id = t2_p.id)))) AND (t0_c.id=t4_c_partners_RELATION_TABLE.StoreCategory AND t3_c_partners.id=t4_c_partners_RELATION_TABLE.Partner AND t0_c.partnerOwner=t1_c_partnerOwner.id);
you see the last clause is the same as in my original query: tables are joined both by "Partner-Strecategories" o2m relationship and "Partners-StoreCategories" m2m relationship.
I think in this case compiler should construct WHERE clause with OR condition at upper level something like:
SELECT sc.id FROM storecategory sc, partner p, partner_categories_stor_1cl2gdd rt
WHERE sc.id=?
AND (
(sc.partnerOwner=p.id AND p.id='mmscity')
OR
(rt.storecategory=sc.id AND rt.partner=p.id AND p.id='mmscity')
);
-----Original Message-----
From: Dain Sundstrom [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 19:04
To: [EMAIL PROTECTED]
Subject: Re: [JBoss-user] serious ejbql compiler problem
That is the way EJB-QL works. When you have an collection member
declaration (i.e. the IN clause in the FROM), you are requesting a
join. Anyway, the following should work:
SELECT OBJECT(c)
FROM StoreCategory c, Partner p
WHERE c.id=?1 AND (c.partnerOwner.id=?2 OR (p.id=?2 AND p MEMBER OF
c.partners)
You many want to do a SELECT DISTINCT, as it is possible to get
multiple results back from this query.
-dain
On Thursday, January 16, 2003, at 03:39 AM, Alexey Yudichev wrote:
> Jboss 3.0.4.
>
> 2 entities: StoreCategory and Partner.
> 2 relationships between them:
>
> 1)
> <ejb-relation>
> <ejb-relation-name>Partner - StoreCategory</ejb-relation-name>
> <ejb-relationship-role>
>
> <ejb-relationship-role-name>PartnerStoreCategoryRole</ejb-
> relationship-role-name>
> <multiplicity>One</multiplicity>
> <relationship-role-source>
> <ejb-name>Partner</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>storeCategories</cmr-field-name>
> <cmr-field-type>java.util.Collection</cmr-field-type>
> </cmr-field>
> </ejb-relationship-role>
> <ejb-relationship-role>
>
> <ejb-relationship-role-name>StoreCategoryPartnerRole</ejb-
> relationship-role-name>
> <multiplicity>Many</multiplicity>
> <relationship-role-source>
> <ejb-name>StoreCategory</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>partnerOwner</cmr-field-name>
> </cmr-field>
> </ejb-relationship-role>
> </ejb-relation>
>
> 2)
> <ejb-relation>
> <ejb-relation-name>Partners - StoreCategory</ejb-relation-name>
> <ejb-relationship-role>
>
> <ejb-relationship-role-name>PartnerStoreCategoryRole</ejb-
> relationship-role-name>
> <multiplicity>Many</multiplicity>
> <relationship-role-source>
> <ejb-name>Partner</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>categories</cmr-field-name>
> <cmr-field-type>java.util.Collection</cmr-field-type>
> </cmr-field>
> </ejb-relationship-role>
> <ejb-relationship-role>
>
> <ejb-relationship-role-name>StoreCategoryPartnerRole</ejb-
> relationship-role-name>
> <multiplicity>Many</multiplicity>
> <relationship-role-source>
> <ejb-name>StoreCategory</ejb-name>
> </relationship-role-source>
> <cmr-field>
> <cmr-field-name>partners</cmr-field-name>
> <cmr-field-type>java.util.Collection</cmr-field-type>
> </cmr-field>
> </ejb-relationship-role>
> </ejb-relation>
>
> ejbql:
>
> SELECT OBJECT(c) FROM StoreCategory c, IN (c.partners) assignedPartner
> WHERE c.id=?1 AND (assignedPartner.id=?2 OR
> c.partnerOwner.id=?2)
>
> which should find specified StoreCategory participating EITHER in
> first relationship with specified Partner OR in second one or both.
>
> sql generated:
>
> SELECT t0_c.id FROM storecategory t0_c, partner t1_assignedpartner,
> partner_categories_stor_1cl2gdd t3_c_partners_RELATION_TABLE, partner
> t2_c_partnerOwner WHERE (t0_c.id = ? AND (t1_assignedpartner.id = ? OR
> t2_c_partnerOwner.id = ?)) AND (t0_c.partnerOwner=t2_c_partnerOwner.id
> AND t0_c.id=t3_c_partners_RELATION_TABLE.StoreCategory AND
> t1_assignedpartner.id=t3_c_partners_RELATION_TABLE.Partner)
>
> which does inner join of all three tables (StoreCategory, Partner and
> M-2-M relation table). So it will by definition select StoreCategories
> participating ONLY in BOTH relationships at the same time.
>
> Or maybe I missed something...
>
-------------------------------------------------------
This SF.NET email is sponsored by: Thawte.com
Understand how to protect your customers personal information by implementing
SSL on your Apache Web Server. Click here to get our FREE Thawte Apache
Guide: http://ads.sourceforge.net/cgi-bin/redirect.pl?thaw0029en
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
------------------------------------------------------- This SF.NET email is sponsored by: Thawte.com - A 128-bit supercerts will allow you to extend the highest allowed 128 bit encryption to all your clients even if they use browsers that are limited to 40 bit encryption. Get a guide here:http://ads.sourceforge.net/cgi-bin/redirect.pl?thaw0030en _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
