On 10/14/05 7:35 AM, Uwe Voelker wrote:
> SELECT MD.* FROM MD LEFT OUTER JOIN MDV ON MD.ID=MDV.MD WHERE ISNULL(MDV.MD)
>
> How do I do this in Rose?
Step 1: sync from CVS :)
Step 2:
$mds = MD::Mgr->get_mds(distinct => 1,
with_objects => [ 'mdvs' ],
query => [ 'MD' => undef ]);
The query could also be [ 'mdvs.MD' => undef ] or [ 'MDV.MD' => undef ] or [
't2.MD' => undef ], of course.
Full example code follows. I found and fixed a bug as part of enabling this
feature, so thanks :)
-John
---
#!/usr/bin/perl
# DB setup and data:
#
# create table MD (ID INT);
# create table MDV (ID INT, MD INT);
#
# insert into MD values (1);
# insert into MD values (2);
# insert into MD values (3);
#
# insert into MDV values (1, 1);
# insert into MDV values (2, 1);
#
# SELECT MD.* FROM MD LEFT OUTER JOIN MDV ON MD.ID=MDV.MD
# WHERE ISNULL(MDV.MD);
# +------+
# | ID |
# +------+
# | 2 |
# | 3 |
# +------+
# 2 rows in set (0.08 sec)
use strict;
use Rose::DB;
use Rose::DB::Object;
Rose::DB->register_db(
domain => 'default',
type => 'default',
driver => 'mysql',
host => 'localhost',
username => 'mysql',
database => 'test');
package MD;
our @ISA = qw(Rose::DB::Object);
MD->meta->table('MD');
MD->meta->columns(ID => { primary_key => 1 });
MD->meta->relationships
(
'mdvs' =>
{
type => 'one to many',
class => 'MDV',
column_map => { ID => 'MD' },
}
);
MD->meta->initialize;
package MD::Mgr;
use Rose::DB::Object::Manager;
our @ISA = qw(Rose::DB::Object::Manager);
sub object_class { 'MD' }
Rose::DB::Object::Manager->make_manager_methods('mds');
package MDV;
our @ISA = qw(Rose::DB::Object);
MDV->meta->table('MDV');
MDV->meta->columns
(
ID => { primary_key => 1 },
MD => { type => 'int' },
);
MDV->meta->relationships
(
'md' =>
{
type => 'many to one',
class => 'MD',
column_map => { MD => 'ID' },
}
);
MDV->meta->initialize;
package main;
#$Rose::DB::Object::Manager::Debug = 1;
my $mds = MD::Mgr->get_mds(distinct => 1,
with_objects => [ 'mdvs' ],
query => [ 'MD' => undef ]);
print join("\n", map { $_->ID } @$mds), "\n\n";
$mds = MD::Mgr->get_mds(distinct => 1,
with_objects => [ 'mdvs' ],
query => [ 'mdvs.MD' => undef ]);
print join("\n", map { $_->ID } @$mds), "\n\n";
$mds = MD::Mgr->get_mds(distinct => 1,
with_objects => [ 'mdvs' ],
query => [ 't2.MD' => undef ]);
print join("\n", map { $_->ID } @$mds), "\n\n";
$mds = MD::Mgr->get_mds(distinct => 1,
with_objects => [ 'mdvs' ],
query => [ 'MDV.MD' => undef ]);
print join("\n", map { $_->ID } @$mds), "\n\n";
-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Rose-db-object mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/rose-db-object
