On 20 September 2012 13:31, Sungsam Gong <gong.sung...@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.sung...@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 <n...@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.sung...@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 <n...@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.sung...@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
_______________________________________________ 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