On Jul 29, 2006, at 10:05 AM, John Siracusa wrote:
> Join conditions are determined by the relationship or fk metadata,
> and they
> do not currently support nested boolean logic. But it shouldn't
> matter
> whether those conditions are in the ON (...) clause or in the WHERE
> clause,
> should it? The results should be the same. What database are you
> using?
i'm using postgres. but the behaviour should be the same on postgres
and mysql and oracle.
the results aren't the same, and shouldn't be, because they're
querying for different things, and in different manners:
one query joins then limits, while the other one tests to see if it
can join
to illustrate: lets create a table of named items, then a table where
one item can be related to another with a description
create table link ( id serial primary key not null , name varchar(16)
not null );
create table link_2_link ( id integer not null references link(id) ,
id_related integer not null references link(id) , relation_name
varchar(16) );
insert into link values ( 1, 'first');
insert into link values ( 2, 'second');
insert into link values ( 3, 'third');
insert into link_2_link values ( 1 , 2 , 'test')
insert into link_2_link values ( 1 , 3 , 'test')
ok, so lets say that we want to get a list of items but we don't want
to get item #1
SELECT t1.* , t2.* FROM link t1 LEFT OUTER JOIN link_2_link t2 ON
( t1.id= t2. id ) WHERE t1.id <> 1 AND ( t2.id_related = 2 OR
t2.id_related IS NULL );
id | name | id | id_related | relation_name
----+--------+----+------------+---------------
2 | second | | |
3 | third | | |
ok, that looks good. i see the two other items. let me try another
search, for items excluding #2
SELECT t1.* , t2.* FROM link t1 LEFT OUTER JOIN link_2_link t2 ON
( t1.id= t2. id ) WHERE t1.id <> 2 AND ( t2.id_related = 1 OR
t2.id_related IS NULL );
id | name | id | id_related | relation_name
----+-------+----+------------+---------------
3 | third | | |
now thats weird... shouldn't we see that data for #1 in there? we
know we have items 1,2,3 -- so item 1 should be there , right?
no, because thats not what we asked the planner.
we asked it to do a join
SELECT t1.* , t2.* FROM link t1 LEFT OUTER JOIN link_2_link t2 ON
( t1.id= t2. id ) WHERE t1.id <> 2;
id | name | id | id_related | relation_name
----+-------+----+------------+---------------
1 | first | 1 | 3 | test
1 | first | 1 | 2 | test
3 | third | | |
them toss out items
( t2.id_related = 1 OR t2.id_related IS NULL )
what we should have asked is for the planner to conditionally join
the table -- so we get the null values if the condition isn't met
SELECT t1.* , t2.* FROM link t1 LEFT OUTER JOIN link_2_link t2 ON
( t1.id= t2. id AND ( t2.id_related = 1 OR t2.id_related IS NULL ) )
WHERE t1.id <> 2;
id | name | id | id_related | relation_name
----+-------+----+------------+---------------
1 | first | | |
3 | third | | |
insert into link_2_link values ( 3 , 1 , 'test')
insert into link_2_link values ( 3, 2, 'test');
SELECT t1.* , t2.* FROM link t1 LEFT OUTER JOIN link_2_link t2 ON
( t1.id= t2. id AND ( t2.id_related = 1 OR t2.id_related IS NULL ) )
WHERE t1.id <> 2;
id | name | id | id_related | relation_name
----+-------+----+------------+---------------
1 | first | | |
3 | third | 3 | 1 | test
on another project, i have a query that is a 14 table nested
conditional join like that, that pulls in different data and
overrides, etc
> If a were to add the ability to augment the join conditions in a
> Manager
> query, what would the syntax be like?
well, queries work like
get(
with_objects=> [ qw(items) ]
query=> [
]
)
so perhaps with_objects/ require objects checks to see if the item is
a scalar (current behavior) or an array/hashref (conditional behavior)
with_objects=> [
normal_join,
conditional_join=> [
]
]
query=> [
]
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/rose-db-object
