Re: [RDBO] OUTER JOIN and ISNULL

[John Siracusa]

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
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object