On 9/11/06 4:04 AM, Jonathan Vanasco wrote:
> i've spent the past 4hours trying to coerce some sql into Rose, and
> its not working. perhaps someone here can point me in the right
> direction.
>
> My SQL is such:
> SELECT
> account.* ,
> wants_item.*,
> item.*
> FROM
> account2item_has has_item
> LEFT JOIN
> account2item_wants wants_item ON has_item.item_id = wants_item.item_id
> LEFT JOIN
> account accountWanting ON wants_item.account_id = accountWanting.id
> LEFT JOIN
> item ON wants_item.item_id = item.id
> WHERE
> accountWanting.id = ?
>
> i have rose objects / managers created for all of the associated tables
> account
> item
> has_item
> wants_item
>
> i can't seem to figure out how to load this via ROSE though. i'm not
> sure on which class I should start the search on.
You should start the search on the class of the "parent" ("primary table")
objects that you expect to be returned. It's usually the first table in the
FROM clause if you were to write the SQL by hand. Above, it's the
account2item_has table. Let's assume classes like:
account2item_has - My::ItemHas
account2item_wants - My::ItemWants
account - My::Account
item - My::Item
Let's further assume relationship like this:
My::ItemHas:
relationships:
want_items =>
{
type => 'one to many',
class => 'My::ItemWants',
column_map => { item_id => 'item_id' },
}
My::ItemWants:
relationships:
account =>
{
type => 'many to one',
class => 'My::Account',
column_map => { account_id => 'id' },
},
item =>
{
type => 'many to one',
class => 'My::Item',
column_map => { item_id => 'id' },
},
Now assume your My::ItemHas::Manager version of the get_objects() method is
named get_item_has_records(). Here's the call:
$item_has_recs =
My::ItemHas::Manager->get_item_has_records(
with_objects => [ 'want_items.account', 'want_items.item' ],
query => [ 'want_items.account.id' => 123 ]);
You could make the same call directly on Rose::DB::Object::Manager's
get_objects() method if you pass an explicit object_class parameter:
$item_has_recs =
Rose::DB::Object::Manager->get_objects(
object_class => 'My::ItemHas',
with_objects => [ 'want_items.account', 'want_items.item' ],
query => [ 'want_items.account.id' => 123 ]);
Either one produces SQL that looks roughly like this:
SELECT
t1.id AS t1_id,
t1.item_id AS t1_item_id,
...
t2.account_id AS t2_account_id,
t2.id AS t2_id,
t2.item_id AS t2_item_id,
...
t3.aname AS t3_aname,
t3.id AS t3_id,
...
t4.id AS t4_id,
t4.iname AS t4_iname,
...
FROM
account2item_has t1
LEFT OUTER JOIN account2item_wants t2 ON (t1.item_id = t2.item_id)
LEFT OUTER JOIN account t3 ON (t2.account_id = t3.id)
LEFT OUTER JOIN item t4 ON (t2.item_id = t4.id)
WHERE
t3.id = ?
which is what you were asking for above, as far as I can tell. The objects
returned will be My::ItemHas objects with related "stuff" hanging off them.
All these left joins mean that the stuff hanging off may or may not be
there, so you'll have to do a lot of checking when you reach into each
My::ItemHas record. You can do that by calling each related object accessor
method and checking for a defined value, but that will cause a database
query if there were no related objects found during the manager query.
Example:
$item_has_recs =
My::ItemHas::Manager->get_item_has_records(...);
foreach my $hi_rec (@$item_has_recs)
{
# No db query sent *if* some related My::ItemWants objects were found
# for this My::ItemHas record during the Manager query. If not, then
# this will cause a query to be run to check for such items.
my $want_item_recs = $hi_rec->want_items;
...
If you want to avoid that second check and just accept the situation when no
related objects were found during the Manager query, use the
has_loaded_related() Helper method:
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Helpers.pm#has
_loaded_related
foreach my $hi_rec (@$item_has_recs)
{
if($hi_rec->has_loaded_related('want_items'))
{
# No database query sent; objects already fetched
my $want_item_recs = $hi_rec->want_items;
foreach my $wi_rec (@$want_item_recs)
{
next unless($wi_rec->has_loaded_related('account'));
# No database query sent; object already fetched
print $wi_rec->account->first_name; # or whatever
}
...
}
}
and so on, for each possibly-loaded related object hanging off each
My::ItemHas record.
> is something like this even reasonable in Rose ( i'm sure its possible.
> everything seems possible. it just seems like it could be a bit awkward)
It's only as awkward as your table structure, classes, and relationships may
make it :) I just guessed at the relationship names, types, and so on.
Your setup may be different. For example, in the many-to-one relationships
my code above could be foreign keys in your code. And the one-to-many
relationship could be many-to-one or one-to-one or a foreign key. All it'd
change is perhaps the name of the relationships (singular/plural). The
Manager query would be the same, using the relationship names chained in dot
notation.
The Manager interface is designed to allow people to think only in terms of
objects and relationships. If you think in SQL instead, you just need to
know what SQL is generated for each Manager param. In this case, you'd need
to know that with_objects => [ ... ] causes the table for the class of each
listed relationship to be LEFT JOINed, with the join conditions pulled from
the relationship definitions. After that, it's just a matter of defining
the right relationships and stringing them together with dots in the right
places in the Manager call.
-John
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
