Hello.
The query-building logic for Rose::DB::Object::Manager->get_objects()
makes an assumption that I would like to politely question. :)
Suppose we have tables a, b, and c. Table b has an FK to table a.
Table b also has an FK to table c. These FK columns are all NOT NULL
constrained. However, table b is not a "mapping" table for an n-to-n
relationship; it is a construct with its own meaning. As one might
guess from these FKs, table a "has many" table b's, such that we would
register a one-to-many relationship from a to b.
Where I to use get_objects for table a, it would be reasonable to use a
with_objects setting of:
[qw( b b.c )]
However, this will fail to return any records for which table c cannot
be found, despite the fact that table c is requested as a member of
with_objects rather than require_objects.
Lines 901-906 of the Manager module reveal this to be explicitly by
design, with a comment indicating that "...Foreign keys that have all
non-null columns are never outer-joined...". And the following
condition is pretty clear:
if (!($rel_type eq 'foreign_key' && $rel->is_required &&
$rel->referential_integrity) &&
($outer_joins_only || $with_objects{$arg}))
{
... use LEFT OUTER JOIN
}
else
{
.... use either an explicit JOIN (inner) or an implicit join
}
So, the rules surrounding the foreign key essentially mean that records
from table b are only meaningful if they reference table c, and as such,
any table b record requires a corresponding table c record. Given the
NOT NULL constraints on the FKs in table b, this is entirely valid.
However, I believe the assumption is being misapplied in this particular
case, due to the fact that we're using table b in a with_objects list
rather than a require_objects list; this is basically the same as
saying "give me table a stuff, and give me tables b and c if you got
'em." In such a case, my table a records should come through whether or
not they have relations all the way out to table c via table b.
However, due to the behavior mentioned above, table c is included with a
simple join (meaning an inner join). That certainly enforces the rule
that you only get table b records that are valid within our set of
assumptions, but it also means that we don't get any table a records
that do not have such table b records.
Would it not be better in this case for each relation in a join to
consider the other relation in that join and adjust the logic
appropriately? In this example, table b has been added as a left outer
join because its in with_objects. When looking at table c, we may
determine that the FK involved means that table c is effectively
"required" in order for table b's data to be valid; however, we should
consider that table b itself isn't required for this particularly query,
and therefore still use a left outer join for table c rather than an
inner join.
The data validity rules enforced in the current logic could still be
enforced through appropriate application of additional where clauses,
such that we only get records included table b data if such records also
include table c data:
SELECT
...
FROM a t1
LEFT OUTER JOIN b t2
ON t2.a_id = t1.id
LEFT OUTER JOIN c t3
ON t3.id = t2.c_id
WHERE
(t2.id IS NULL OR t3.id IS NOT NULL)
...
We can work around this issue at the present through use of a "soft"
foreign key. It seems preferable to me, however, that the query builder
construct the query in a manner more consistent with the meaning
expressed through with_objects.
I was about to dive into this and see if I could could up with a patch
that would do this, but I figured it would be better to correspond with
the list first and make sure I'm not missing something. Am I? :)
Thanks in advance for any input, and for patiently wading through this
entire message.
- Ethan
--
Ethan Rowe
End Point Corporation
[EMAIL PROTECTED]
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
