#1675: DQL: In and Notin expression with two or more operand
-----------------------------------+----------------------------------------
Reporter: samueleorso | Owner: romanb
Type: enhancement | Status: new
Priority: major | Milestone:
Component: Query/Hydration | Version: 0.11.0
Keywords: | Has_test: 0
Mystatus: Pending Core Response | Has_patch: 0
-----------------------------------+----------------------------------------
Hi all,
at this moment, the "In/Notin expression" works in this manner (comments
from postgresql 8.3 documentation):
<operand> IN/NOT IN (<subquery>|<value list>)
The right-hand side is a parenthesized subquery, which must return exactly
one column. The left-hand
expression is evaluated and compared to each row of the subquery result.
The result of IN is “true” if
any equal subquery row is found. The result is “false” if no equal row is
found (including the special
case where the subquery returns no rows).
Note that if the left-hand expression yields null, or if there are no
equal right-hand values and at
least one right-hand row yields null, the result of the IN construct will
be null, not false. This is in
accordance with SQL’s normal rules for Boolean combinations of null
values.
As with EXISTS, it’s unwise to assume that the subquery will be evaluated
completely.
I'd propose an enhancement like this:
row_constructor IN (subquery)
The left-hand side of this form of IN is a row constructor, as described
in Section 4.2.11. The righthand
side is a parenthesized subquery, which must return exactly as many
columns as there are expressions
in the left-hand row. The left-hand expressions are evaluated and compared
row-wise to
each row of the subquery result. The result of IN is “true” if any equal
subquery row is found. The
result is “false” if no equal row is found (including the special case
where the subquery returns no
rows). As usual, null values in the rows are combined per the normal rules
of SQL Boolean expressions.
Two rows are considered equal if all their corresponding members are non-
null and equal; the rows
are unequal if any corresponding members are non-null and unequal;
otherwise the result of that row
comparison is unknown (null). If all the per-row results are either
unequal or null, with at least one
null, then the result of IN is null.
Many databases support this feature (Oracle,Postgresql,..) and I used it
in many cases...
Example:
table groups (id,...) //list of groups
table objects (id,..) //list of object
table users_objects (user_id,group_id,object_id) //grant objects/groups
to users
table messages (id,group_id,object_id,attributes) //messages
SELECT * FROM messages WHERE (group_id,object_id) IN (SELECT
group_id,object_id FROM users_objects WHERE user_id = ?)
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1675>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
