[Dbix-class] establishing relationship across databases

[Sungsam Gong]

On 20 September 2012 13:31, Sungsam Gong <gong.sungsam at gmail.com> wrote:
> I decided to using an arbitrary sql via a custom resultsource rather
> than explicitly (and manually) changing table name (e.g
> __PACKAGE__->table("CARDIODB_DEVEL.2UniProts"))
> http://search.cpan.org/~frew/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary_SQL_through_a_custom_ResultSource
> It worked just fine.
>
> On 20 September 2012 12:27, Sungsam Gong <gong.sungsam at gmail.com> wrote:
>> What do you mean 'top level class'?
>> Do you mean models? (e.g. Model::CARDIODB and Model::UNIPROT)
>>
>> On 20 September 2012 12:13, Neil Lunn <neil at mylunn.id.au> wrote:
>>> Of course it's complaining. You have two models and two conbections. You
>>> need to move your top level class to Schema and drop database from your dsn.
>>>
>>> Provided the user has access to both namespaces you will be fine.
>>>
>>> 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