That second query looks OK to me - can you say how it fails to deploy?
Another approach might be:
SELECT DISTINCT OBJECT(t)
FROM ejbPerson p, ejbTask t
WHERE p.name LIKE ?1
AND (t.owner = p OR p MEMBER OF t.mandatories OR p MEMBER OF t.optionals)
AND ((t.startDate BETWEEN ?2 AND ?3 OR t.endDate BETWEEN ?2 AND ?3)
OR (t.endDate <= ?2 AND t.endDate >= ?3))
ORDER BY t.startDate
Cheers
Jeremy
/*************************
* Jeremy Boynes
* Partner
* Core Developers Network
*************************/
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Ionel
> Gardais
> Sent: Friday, June 06, 2003 7:00 AM
> To: [EMAIL PROTECTED]
> Subject: [JBoss-user] [EJB-QL] dealing with collection relationship even
> if they are empty
>
>
> Hi,
>
> I am trying to create a JBOSS-QL query that need to use collection
> condition.
>
> the query is :
>
> SELECT DISTINCT OBJECT(t)
> FROM ejbTask AS t, IN(t.mandatories) m, IN(t.optionals) o
> WHERE ((t.owner.name LIKE ?1) OR (m.name LIKE ?1) OR (o.name LIKE ?1)) AND
> ((t.startDate BETWEEN ?2 AND ?3 OR t.endDate BETWEEN ?2 AND ?3) OR
> (t.startDate <= ?2 AND t.endDate >= ?3))
> ORDER BY t.startDate
>
> it retrieves all tasks associated with a user, no matter if the user is
> the owner of the task nor if he appears in the mandatory-persons list
> nor in the optionals-persons list.
>
> The relation-tables are correctly set when I create a task.
> The problem has been tightened to the query.
>
> I found in a book that when using "IN(bean.collection) var" in the FROM
> clause, then it returns values ONLY if there are one or more results in
> the collection (Actually the book says "Note that declaring variables
> will restrict your queries even if you don't use the variables")
>
> For my stuff, the mandatory and optional collection can be empty both or
> one-at-a-time so it seems to be why the query returns an empty set.
>
> My question is : how to archieve the same query but without being stop
> by this restiction ?
>
> I tried :
>
> SELECT DISTINCT OBJECT(t)
> FROM ejbTask AS t, ejbPerson AS m, ejbPerson AS o
> WHERE (t.owner.name LIKE ?1 OR m MEMBER OF t.mandatories OR o MEMBER OF
> t.optionals AND m.name LIKE ?1 AND o.name LIKE ?1) AND
> ((t.startDate BETWEEN ?2 AND ?3 OR t.endDate BETWEEN ?2 AND ?3) OR
> (t.endDate <= ?2 AND t.endDate >= ?3))
> ORDER BY t.startDate
>
> but it fails at deploy time.
>
> Many thanks for any tips !
> Ionel
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Etnus, makers of TotalView, The best
> thread debugger on the planet. Designed with thread debugging features
> you've never dreamed of, try TotalView 6 free at www.etnus.com.
> _______________________________________________
> JBoss-user mailing list
> [EMAIL PROTECTED]
> https://lists.sourceforge.net/lists/listinfo/jboss-user
-------------------------------------------------------
This SF.net email is sponsored by: Etnus, makers of TotalView, The best
thread debugger on the planet. Designed with thread debugging features
you've never dreamed of, try TotalView 6 free at www.etnus.com.
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
