Re: [Dbix-class] Fwd: Creating a relationship
On 16 Sep 2009, at 20:43, Sungsam Gong wrote: My email below does not seem to be sent properly. Please don't do that. You are using gmail - its quite normal for gmail to hide your own postings to a mailing list from you. Nigel. -- [ Nigel Metheringham nigel.methering...@intechnology.com ] [ - Comments in this message are my own and not ITO opinion/policy - ] ___ 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
[Dbix-class] Fwd: Creating a relationship
My email below does not seem to be sent properly.
Am trying again...
-- Forwarded message --
From: Sungsam Gong
Date: 2009/9/16
Subject: Creating a relationship
To: "DBIx::Class user and developer list"
Hi,
I'm using DBIx::Class::Schema::Loader for a schema generation from a
mysql backend.
package DB::UNIPROT;
use strict;
use warnings;
use base qw/DBIx::Class::Schema::Loader/;
__PACKAGE__->loader_options(
debug => $ENV{DBIC_TRACE} || 0,
);
1;
A relationship was normally defined in another main perl script as
shown in the cookbook
(http://search.cpan.org/~ribasushi/DBIx-Class-0.08111/lib/DBIx/Class/Manual/Cookbook.pod#JOINS_AND_PREFETCHING).
For example, in the main script;
DB::UNIPROT::Featureclass->belongs_to(rel_name,
'DB::UNIPROT::Featureclass', {'foreign.id'=>'self.featureClass'});
'join' runs smoothly with no problems and everything was fine until I
recently updated DBIx::Class (0.081) of my local linux (2.6.18) box.
my $rs=$schema->resultset('Feature')->search_rs(
{'featureClass.val'=>'ACT_SITE'},
{
join=>'rel_name',
}
)->slice(0,5);
However, after updating DBIx, I got an error message as shown below;
No such relationship relname at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 1027
DBIx::Class::Schema::throw_exception('DB::UNIPROT=HASH(0xbadbb90)',
'No such relationship relname') called at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSource.pm line 1578
blablabla.
I solved the problem by dumping database schema into a file using
'make_schema_at' and 'dump_to_dir' functions and then edited the
schema file by inserting a line which defines a relation;
__PACKAGE__->belongs_to(rel_name, 'DB::UNIPROT::Featureclass',
{'foreign.id'=>'self.featureClass'});
Of course, I deleted the relationship definition in the main script.
Then, it worked!
But, I'm not happy as I have to modify my applications which had no
problems with the previous version of DBIx.
The only difference is where I defined the relations (plus schema dumping) .
Can anybody please advise me what has changed in the newer version?
Or did I just miss something?
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
Re: [Dbix-class] SQL server 2000 giving error about System Tables
Matt Whipple wrote: If you're not going to use them then just remove the classes for them. Thank you both for the help. I found a work-around at the time, but now I want to set it up properly. I have learned a little bit, so I think I am better able to describe my problem. I get the following errors when I try to automatically load a schema from a MSSQL 2000 server. DBIx::Class::Row::get_column(): Can't fetch data as class method at (eval 1413) line 6 Compilation failed in require at C:/Perl/site/lib/Class/C3/Componentised.pm line 128. at C:/Perl/site/lib/DBIx/Class/Schema/Loader.pm line 137 It works from MSSQL 2005 or 2008 servers, though. I also get a whole slew of warnings as it tries to read the system tables. Those are the warnings that I posted previously in this thread. I have the most up-to-date DBIx::Class and DBIx::Class::Schema::Loader. However, when installing the Loader, I get an error from the DBD::Sybase module. It wants to know where my Sybase installation is. Since I need sybase for this, do I have to install it? It sounds like a dumb question, but I'm not actually sure. I am installing this on a Windows XP system, with the server located offsite and I have an ODBC connection to it. Also, if I copy the tables to a MSSQL 2005 or 2008 dtaabase, it all works fine. Then I just change my ODBC to point back to the original database and everything runs. This is a pain, though. ___ 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
[Dbix-class] ResultSet chaining help
A few days ago, I asked a question on #dbix_class about forcing a particular join to be an inner join (as was specified in the schema definition), instead of a left join (as happened in the generated query); my app had started to fail because of a change in DBIC, but apparently my technique was flawed in the first place. I got some helpful suggestions from ribasushi and mst, but I'd like to ask again more formally here, because in order to rewrite what is currently some very complicated code, I want to make sure I fully understand it going in. The main difficulty now is that I use a number of subroutines to build data structures from which my search is constructed. I've adopted various tricks to construct things properly, and while I'm sure that there are better tricks (or that these tricks aren't even necessary), I'm not sure what these are. In particular, constructing the join clause has proven very difficult. My basic table structure is like this (excuse the poor diagramming, and these are has-many's going down and out): Region >- Source -< Subject | ^ Title >- Part -< Author | ^ Quotation -< Quotation_lookup (from Sphinx) | ^ Region >- cwGroup -< Subject There are more details, but this will do. Most searches need to return an RS of cwGroups. Some searches need to return an RS of sources. A cwGroup RS will _always_ need to prefetch Part. A Source RS only needs Source. The general use case here is that I have a web form that has fields representing columns in various of these tables. Which tables are joined will depend on what's entered. In the most common and simplest case, someone will search based on a field in the cwGroup table, that table can be queried, joined through to the Part table (the results are also always sorted by a field in the Part table), and that's it. In a more complicated, but by no means uncommon, case, a search will be based on values in the Source table, perhaps even a source-subject thing, as well as things in the cwGroup table, and we'll have to join all the way through from source-subject down through cwGroup (even though there might not be anything being searched in Part or Quotation). The Quotation_lookup thing is a temporary table generated from the results of a query in the Sphinx search engine; if someone wants to do a full-text search of the Quotation table, I do it in Sphinx, return a list of Quotation ID's, and put these into Quotation_lookup. In this case, and in the case of Author, Title, Subject, or Region searches, these all need to be inner joins, not left joins--I want to get only those results where there _is_ a matching row in the quotation-lookup, author, etc. table--if it doesn't match, I don't want a result returned. Obviously. I had previously declared these with a join_type of 'INNER' in my schema declaration, but I now get the impression that I just have to create the query differently. What I've been doing is keeping track of what tables are used for a query, and then having a _get_cwGroup_join or _get_source_join routine that has a big if-else table going through various possibilities for which tables need to be joined in, and return a data structure for the join. Finally--the database is large enough that I do want to execute these as single joined queries, not multiple queries. Thanks for reading this far. Jesse Sheidlower ___ 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
[Dbix-class] Creating a relationship
Hi,
I'm using DBIx::Class::Schema::Loader for a schema generation from a
mysql backend.
package DB::UNIPROT;
use strict;
use warnings;
use base qw/DBIx::Class::Schema::Loader/;
__PACKAGE__->loader_options(
debug => $ENV{DBIC_TRACE} || 0,
);
1;
A relationship was normally defined in another main perl script as
shown in the cookbook
(http://search.cpan.org/~ribasushi/DBIx-Class-0.08111/lib/DBIx/Class/Manual/Cookbook.pod#JOINS_AND_PREFETCHING).
For example, in the main script;
DB::UNIPROT::Featureclass->belongs_to(rel_name,
'DB::UNIPROT::Featureclass', {'foreign.id'=>'self.featureClass'});
'join' runs smoothly with no problems and everything was fine until I
recently updated DBIx::Class (0.081) of my local linux (2.6.18) box.
my $rs=$schema->resultset('Feature')->search_rs(
{'featureClass.val'=>'ACT_SITE'},
{
join=>'rel_name',
}
)->slice(0,5);
However, after updating DBIx, I got an error message as shown below;
No such relationship relname at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Schema.pm line 1027
DBIx::Class::Schema::throw_exception('DB::UNIPROT=HASH(0xbadbb90)',
'No such relationship relname') called at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Class/ResultSource.pm line 1578
blablabla.
I solved the problem by dumping database schema into a file using
'make_schema_at' and 'dump_to_dir' functions and then edited the
schema file by inserting a line which defines a relation;
__PACKAGE__->belongs_to(rel_name, 'DB::UNIPROT::Featureclass',
{'foreign.id'=>'self.featureClass'});
Of course, I deleted the relationship definition in the main script.
Then, it worked!
But, I'm not happy as I have to modify my applications which had no
problems with the previous version of DBIx.
The only difference is where I defined the relations (plus schema dumping) .
Can anybody please advise me what has changed in the newer version?
Or did I just miss something?
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
RE: [Dbix-class] Using Database Functions
Ah, yes that's done the trick. Thanks very much for your help Eden.
> -Original Message-
> From: Eden Cardim [mailto:edencar...@gmail.com]
> Sent: 15 September 2009 05:05
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] Using Database Functions
>
> On Mon, Sep 14, 2009 at 9:41 AM, matthew couchman (JIC)
> wrote:
> > I'm using DBIx via Catalyst to try and create the underlying SQL
> query:
> > SELECT DISTINCT( YEAR( date ) ) FROM gps_location me;
> > Following the examples at DBIx::Class::Manual::Cookbook it looked
> like this
> > would do the trick:
> > $c->stash->{years} = $c->model('DB::GpsLocation')->search(
> > {},
> > {
> > select => [ { YEAR => 'date' } ],
> > as => [ 'year' ],
> > distinct => 1,
> > }
> > );
> > However when I run this COUNT() has been added to the query:
> > SELECT COUNT( DISTINCT( YEAR( date ) ) ) FROM gps_location me;
> > Where does the COUNT() come from and how do I get rid of it?
>
> You're attempting to stringify $c->stash->{years} somewhere,
> ResultSets stringify as ->count, so that's where you're getting the
> COUNT in your query from.
>
> You probably want to create and iterate through a ResultSetColumn
> instead:
>
> my $years = $rs->search({}, { select => [ { YEAR => 'date' }], as =>
> ['year'], distinct => 1 });
> my $year_cols = $years->get_column('year'); # returns a
> DBIx::Class::ResultSetColumn
> while(my $year = $year->next) { do_something($year) }
>
> --
>Eden Cardim Need help with your Catalyst or DBIx::Class
> project?
> Code Monkeyhttp://www.shadowcat.co.uk/catalyst/
> Shadowcat Systems Ltd. Want a managed development or deployment
> platform?
> http://edenc.vox.com/http://www.shadowcat.co.uk/servers/
>
> ___
> 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-
> cl...@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
