[Dbix-class] establishing relationship across databases

[Sungsam Gong]

The connection arguments are:
<Model::CARDIODB>
    schema_class Nectar::Schema::CARDIODB
    <connect_info>
        dsn dbi:mysql:database=CARDIODB_DEVEL;host=fs01
    </connect_info>
</Model::CARDIODB>
<Model::UNIPROT>
    schema_class Nectar::Schema::UNIPROT
    #traits QueryLog::AdoptPlack
    #for caching with memcached
    traits          Caching
    <connect_info>
        dsn dbi:mysql:database=UNIPROT;host=fs01
    </connect_info>
</Model::UNIPROT>

I did:
__PACKAGE__->table("CARDIODB_DEVEL.2UniProts");
within package Nectar::Schema::CARDIODB::Result::2UniProt;
__PACKAGE__->table("UNIPROT.SwissVariants");
__PACKAGE__->has_many('CARDIODB_DEVEL.2UniProts',
'Nectar::Schema::CARDIODB::Result::2UniProt',
{'foreign.uniprot'=>'self.sp_acc',
'foreign.res_num'=>'self.res_num'});
within package Nectar::Schema::UNIPROT::Result::SwissVariant;

Within the controller:
    $c->stash->{humsavars}=[$c->model("UNIPROT::SwissVariant")->search(
        {gene=>$hgnc},
        {
            join=>[qw/CARDIODB_DEVEL.2UniProts/],
            '+select'=>[qw/2UniProts.uid/],
            '+as'=>[qw/uid/],
            distinct=>1,
        }
    )];

But still complaining:
Can't find source for Nectar::Schema::CARDIODB::Result::2UniProt at
/home/sung/perl5/lib/perl5/DBIx/Class/Schema.pm line 1088

What I like to accomplish is:
SELECT DISTINCT sv.*, u.uid
FROM UNIPROT.SwissVariants sv
LEFT JOIN CARDIODB_DEVEL.`2UniProts` u ON sv.sp_acc=u.uniprot AND
sv.uniprot_res_num=u.res_num
WHERE sv.gene='my_gene'


On 20 September 2012 11:40, Neil Lunn <neil at mylunn.id.au> wrote:
> What is your connection arguments? Are you using database= in your dsn? What
> is the top level class in your schema with the connection?
>
> If you look at these things and fully qualify your table names in classes,
> then what you appear to be trying should be possible.
>
> Sungsam Gong <gong.sungsam at gmail.com> wrote:
> Hi,
>
> I have been creating a manual relationship between tables (mysql
> MyISAM) within the same database, which worked very well for my
> purpose.
> Just wondering whether it's possible to make a relationship between
> two tables each of which residues in different database.
>
> For example,
> package Nectar::Schema::UNIPROT::Result::SwissVariant;
> ...
> __PACKAGE__->has_many('2UniProts',
> 'Nectar::Schema::CARDIODB::Result::2UniProt',
> {'foreign.uniprot'=>'self.sp_acc',
> 'foreign.res_num'=>'self.res_num'});
>
> Then, within the controller:
>     $c->stash->{humsavars}=[$c->model("UNIPROT::SwissVariant")->search(
>         {gene=>$hgnc},
>         {
>             join=>[qw/2UniProts/],
>             '+select'=>[qw/2UniProts.uid/],
>             '+as'=>[qw/uid/],
>             distinct=>1,
>         }
>     )];
>
> I tried this, but with no luck.
> It complains:
> Can't find source for Nectar::Schema::CARDIODB::Result::2UniProt at
> /home/sung/perl5/lib/perl5/DBIx/Class/Schema.pm line 1088
> DBIx::Class::Schema::throw_exception('Nectar::Schema::UNIPROT=HASH(0x1fa53990)',
> 'Can\'t find source for Nectar::Schema::CARDIODB::Result::2Uni...')
> called at /home/sung/perl5/lib/perl5/DBIx/Class/Schema.pm line 601
> DBIx::Class::Schema::source('Nectar::Schema::UNIPROT=HASH(0x1fa53990)',
> 'Nectar::Schema::CARDIODB::Result::2UniProt') called at
> /home/sung/perl5/lib/perl5/DBIx/Class/ResultSource.pm line 1853
> DBIx::Class::ResultSource::related_source('DBIx::Class::ResultSource::Table=HASH(0x1f9fc490)',
> '2UniProts') called at
> /home/sung/perl5/lib/perl5/DBIx/Class/ResultSource.pm line 1511
> DBIx::Class::ResultSource::_resolve_join('DBIx::Class::ResultSource::Table=HASH(0x1f9fc490)',
> '2UniProts', 'me', 'HASH(0x21388770)', 'ARRAY(0x22151f30)', 'undef')
> called at /home/sung/perl5/lib/perl5/DBIx/Class/ResultSource.pm line
> 1469
> DBIx::Class::ResultSource::_resolve_join('DBIx::Class::ResultSource::Table=HASH(0x1f9fc490)',
> 'ARRAY(0x22142e90)', 'me', 'HASH(0x21388770)', 'ARRAY(0x20799980)')
> called at /home/sung/perl5/lib/perl5/DBIx/Class/ResultSet.pm line 3251
> ...
>
> If cross-database joining is not possible, any workaround for this?
>
> Cheers,
> Sung
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk