[Dbix-class] establishing relationship across databases

[Sungsam Gong]

There are two schema classes:
lib/Nectar/Schema/UNIPROT.pm (package Nectar::Schema::UNIPROT;)
lib/Nectar/Schema/CARDIODB.pm (package Nectar::Schema::CARDIODB;)

Maybe, Nectar::Schema::CARDIODB::Result::2UniProt cannot be found from
Nectar::Schema::UNIPROT::Result::SwissVariant ?

On 20 September 2012 11:56, Sungsam Gong <gong.sungsam at gmail.com> wrote:
> 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