Re: [Dbix-class] Re: Result, ResultSet and load_namespaces
I'd second this: the loader is great to get started but after a while it becomes a bit of a drag and you can make changes and additions to your schema much more quickly and efficiently if you can edit the core of the Result classes rather than having to add stuff to the end. Moose Roles are great for adding shared functionality between Result classes. I also use them for that. Check out this post for some how-to for Mosse roles in Result and ResultSet classes: http://www.perlmonks.org/?node_id=968402 I think a bit of the confusion could be avoided by making it clearer perhaps in the docs that a Result class describes a single row of a table whereas the ResultSet class handles methods for handling sets of rows, e.g. for adding a shortcut method to a complex search on a table. Frank On 26/07/12 19:09, Bill Moseley wrote: On Thu, Jul 26, 2012 at 6:59 AM, Alejandro Imass aim...@yabarana.com mailto:aim...@yabarana.com wrote: So now I ask: The elegant way to extend row-level methods is by extending the code in the Result classes below the checksum created by Loader? We had the need to share row methods between somewhat different schemas and used Moose Roles to accomplish that. So, that might be an approach to consider. We also don't use Loader (other than initially). That's something to evaluate as your schema grows. -- Bill Moseley mose...@hank.org mailto:mose...@hank.org ___ 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 -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE. ___ 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] many_to_many data access
Given the purpose of the app you are writing, it seems to me that what
you really want is the ability to compare prices within a certain area,
whatever that may be, as in give me the lowest price for this item
within a 50mile radius from my home - is that a fair assumption?
If so, here is an alternative to re-building Google's map databases:
Use Google's APIs to build an query your database with geo coordinates.
Check out these resources:
https://developers.google.com/places/documentation/autocomplete
https://developers.google.com/maps/articles/phpsqlsearch_v3
you can use the Google API to let users enter the correct address (with
auto-completions to ensure normality) and your database just stores
the longitude and latitude of the place. Now you can do the range
queries against your local database as described in the developers guide.
Would that be an option for you?
On 12/07/12 19:23, Robyn Jonahs wrote:
On Thu, Jul 12, 2012 at 1:24 PM, Robyn Jonahs
learn.catal...@gmail.com mailto:learn.catal...@gmail.com wrote:
On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt fri...@gmail.com
mailto:fri...@gmail.com wrote:
On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl
patr...@pantheon.at mailto:patr...@pantheon.at wrote:
first, to get the cities associated to the store, use the
many_to_many
relationship you defined; if you have such a relationship,
you usually
never use the bridging table (store_cities in your
example) directly.
I disagree. The use of data other than left_id and right_id
is what makes many_to_many jointables so awesome. The obvious
data to put in the join table is the date the intermediate was
created, or often which user added it. Once I created a DB
that was supposed to represent judges in the US. It had
tables for Military Rank and Military Service and then a join
table that joined the judge to those two and it had start
dates and end dates etc. That db had a total of 14~ join
tables, nearly all of which had intermediate data. It's a
very handy and powerful pattern.
--
fREW Schmidt
http://blog.afoolishmanifesto.com
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class http://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
Conceptually I have worked myself into a confused state.
Originally I thought that the many_to_many relationships were the
proper way to go. But the problem is that the locations have to be
unique. I did not generate that. My problem is that I am not
making unique addresses. I think that I need a way to join the
data record of interest (FOO) to a combination of store, city,
state and country. So it seems that I need a join table between
FOO_id and the set of (store_id, city_id, state_id, country_id).
This seems wrong to me.
The alternate if I insist on many_to_many tables is to have a
Location table that has records for unique combinations of those
four things.
I was working under the concept that if any data entries were
repeated in database, you should put them into a separate table.
It may be that I need to use a simple has_one and has_many
relationship to have a unique address record. I can't see how to
make the many_to_many work at the moment in my mind.
I think that I have screwed up the database logic but I have
confused myself so much now that I could use help. Have I made it
too complicated? I guess a pitch back to the Best practice for
dealing with addresses.
CONCLUSION to original issue:
Thanks to everyone for the help.
1. I did screw up by using the many_to_many relationships. In my html
and data flow, I restricted it as if it were a has_one and a has_many
relationship to cities, countries and states. Thus Each store is
forced to be a unique row in my database. I will fix this to the
proper relationships and follow it through my application to do it as
I intended. I will keep in mind the maintenance issues if this were a
multi-user application and people could screw it up.
2. Since each store has one associated city... the many to many only
has one result. My temporary patch so I can enter data is as follows.
#
# Row-level helper methods
#
sub full_address {
my ($self) = @_;
my $result = $self-store;
my @cities = $self-cities;
foreach my $city (@cities) {
$result .= ' :|: ' . $city-city;
}
# Just take the first element of the array?
my @states = $self-states;
#$result .= ', ' . @states[0]-state; # Use the short form
$result .= ', ' . @states[0]-abb;
Re: [Dbix-class] Need help with a search query
try this:
my $rs = $schema-resultset('Person')-search(
{
'people_companies.code' = $code,
},
{
join = 'people_companies',
}
);
your relationship is called people_companies, so your first attempt
was correct but the where clause referred to relationship company,
which doesn't exist. If you want to search for something in company
across the PeopleCompany bridge model, follow Hailin's advice and set up
a many-to-many relationship from Person to Company.
Hope that helps
Frank
On 15/06/12 03:42, Dennis Daupert wrote:
For some reason the right search query syntax has been elusive.
I'm trying to get a list of people belonging to a particular company
where the company has a particular 'code' value.
==
Sample query code attempt:
--
sub get_ppl_by_org_code {
my ( $schema, $code ) = @_;
my $rs = $schema-resultset('Person')-search(
{
'company.code' = $code,
},
{
join = [qw/ company /], # also tried people_companies
}
);
return( $rs );
}
Gives error: No such relationship company on Person
==
Schemas: (produced by DBIx::Class::Schema::Loader)
--
__PACKAGE__-table(people);
__PACKAGE__-add_columns(
agent_id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0 },
first_name,
{ data_type = varchar, is_nullable = 1, size = 40 },
last_name,
{ data_type = varchar, is_nullable = 1, size = 40 },
snip
__PACKAGE__-has_many(
people_companies,
DB::Schema::Result::PeopleCompany,
{ foreign.agent_id = self.agent_id },
{ cascade_copy = 0, cascade_delete = 0 },
);
==
__PACKAGE__-table(group_company_xl);
__PACKAGE__-add_columns(
company_id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0 },
company,
{ data_type = varchar, is_nullable = 0, size = 50 },
service_center,
{ data_type = varchar, is_nullable = 1, size = 20 },
code,
{ data_type = varchar, is_nullable = 1, size = 5 },
snip
__PACKAGE__-has_many(
people_companies,
DB::Schema::Result::PeopleCompany,
{ foreign.company_id = self.company_id },
{ cascade_copy = 0, cascade_delete = 0 },
);
Seems odd, DBIx::Class::Schema::Loader produced accessor
with same name as the one for people table. Is that kosher?
==
__PACKAGE__-table(people_companies);
__PACKAGE__-add_columns(
agent_id,
{ data_type = integer, is_foreign_key = 1, is_nullable = 0 },
company_id,
{ data_type = integer, is_foreign_key = 1, is_nullable = 0 },
);
__PACKAGE__-belongs_to(
agent,
DB::Schema::Result::Person,
{ agent_id = agent_id },
{ is_deferrable = 1, on_delete = CASCADE, on_update = CASCADE },
);
__PACKAGE__-belongs_to(
company,
DB::Schema::Result::GroupCompanyXl,
{ company_id = company_id },
{ is_deferrable = 1, on_delete = CASCADE, on_update = CASCADE },
);
==
Can someone help me learn the secret handshake?
/dennis
___
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
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
___
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] Query translation
just to add to that: I got a suspicion that you may have used a Catalyst helper to create the database model with DBIC Schema Loader in *dynamic mode*, which is probably what you mean with porting this to Catalyst(?) If so: you need to change that to static mode and let the Schema::Loader actually write the Schema files and work with those. It's better to work with a static schema anyway. Once you have your Schema files you can add ResultSet classes and use this new helper as described. You can still re-generate the Schema classes as and when you make changes to the schema. Hope this helps a little bit Frank On 10/05/12 23:25, fREW Schmidt wrote: Ok, I understand how that works I think, but how/where do I integrate it into my code as I am using Catalyst models and I'm very confused on this one. I don't completely have enough information to answer, but you need to make a base resultset and use that in your schema, and put the correlate helper in the base resultset. Catalyst models are just DBIx::Class results. -- fREW Schmidt http://blog.afoolishmanifesto.com ___ 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 -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE. ___ 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] Dynamic defaults
DBIx::Class is not using Moose
On Tue, 2011-11-29 at 14:31 +, Benjamin Martin wrote:
hello,
I am not sure if I am doing the right thing so I ask here for advice
please :)
I have 2 tables, chart and sections. A chart is made of multiple
sections (chart has_many sections)
When I add a new section to a chart I want to give it a default name of
'Section 1' or 'Section 2' and so on.
The DBIx::Class docs says that to provide defaults one should have a
'new' method.
The Moose docs state to never override new
The above makes me think I am doing the wrong thing. In my Section class
I have this:
around new = sub {
my ($orig, $self) = (shift, shift);
my ( $attrs ) = @_;
if ( $attrs-{chart_id} ) {
my $schema = $attrs-{-result_source}-schema;
my $chart = $schema-resultset('Chart')-find(
$attrs-{chart_id} );
unless( defined $attrs-{name} ) {
my $number_of_sections = $chart-sections-count;
$attrs-{name} = 'Section ' . ($number_of_sections + 1);
}
}
my $new = $self-$orig($attrs);
return $new;
};
Is there a better way to achieve this?
It seems some what hacky to use '-result_source' ... is there a better way?
Thanks for any advice you can give.
tar,
-b
___
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
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
___
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] schema load/deploy round-trip problem with PostgreSQL
ok, it seems to have been a bug but it seems to have been fixed in the
latest version of
DBIx::Class::Schema::Loader.
I just updated to version 0.07014 and the generated scheme for table
names now looks like this:
__PACKAGE__-add_columns(
id,
{
data_type = integer,
is_auto_increment = 1,
is_nullable = 0,
sequence = names_id_seq,
},
);
which re-recreates the original table correctly when deployed.
On 22/11/11 18:13, Frank Schwach wrote:
I have a DDL file that creates a database on a PostgreSQL (8.3)
instance, which has something like the following statement to create a
table:
CREATE TABLE names (
id SERIAL NOT NULL,
PRIMARY KEY (id)
);
When this is executed, I get a table and a sequence, shown here in a
psql shell:
# \d
List of relations
Schema | Name | Type | Owner
+--+--+---
public | names| table| pbr_owner
public | names_id_seq | sequence | pbr_owner
and the table names looks like this:
# \d names;
Table public.names
Column | Type | Modifiers
+-+
id | integer | not null default nextval('names_id_seq'::regclass)
Indexes:
names_pkey PRIMARY KEY, btree (id)
using the Schema::Loader via dbicdump gives me a Result class for this
table that contains this:
## lib/My/Schema/Result/Name.pm
package My::Schema::Result::Name;
use base 'DBIx::Class::Core';
__PACKAGE__-add_columns(
id,
{
data_type = integer,
default_value = \nextval('names_id_seq'::regclass),
is_auto_increment = 1,
is_nullable = 0,
},
);
__PACKAGE__-set_primary_key(id);
###
So far so good, but when I now try to deploy that generated schema to
another PostgreSQL database, I get an error:
$ perl -MMy::Schema -e 'My::Schema-connect(## SOME CONNECT STRING
###)-deploy ; '
NOTICE: CREATE TABLE will create implicit sequence names_id_seq1
for serial column names.id
DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI
Exception: DBD::Pg::db do failed: ERROR: multiple default values
specified for column id of table names at -e line 1
(running CREATE TABLE names (
id serial DEFAULT nextval('names_id_seq'::regclass) NOT NULL,
PRIMARY KEY (id)
)) at -e line 1
Looking at the generated SQL statement, the error is in the id
column which is defined as type serial AND has an explicit default
nextval('names_id_seq'::regclass), but it should have been simply:
id serial NOT NULL
or
id integer DEFAULT nextval('names_id_seq'::regclass)
but not a combination of both.
The culprit is the line
default_value = \nextval('names_id_seq'::regclass),
in the id column definition of the My::Schema Result class. Indeed,
removing this line fixes the problem and generates a serial NOT NULL
definition for the id column, as it should be.
Is this a bug or is there something I am doing wrong here (is there an
option that I have overlooked that controls this behaviour)? Or do I
simply have to delete all the default definitions manually at the
cost of loosing the ability to auto-update the dumped schema classes
with dbicdump, having changed code above the do not modify line?
Thanks for your help!
Frank
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
___
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] schema load/deploy round-trip problem with PostgreSQL
I have a DDL file that creates a database on a PostgreSQL (8.3)
instance, which has something like the following statement to create a
table:
CREATE TABLE names (
id SERIAL NOT NULL,
PRIMARY KEY (id)
);
When this is executed, I get a table and a sequence, shown here in a
psql shell:
# \d
List of relations
Schema | Name | Type | Owner
+--+--+---
public | names| table| pbr_owner
public | names_id_seq | sequence | pbr_owner
and the table names looks like this:
# \d names;
Table public.names
Column | Type | Modifiers
+-+
id | integer | not null default nextval('names_id_seq'::regclass)
Indexes:
names_pkey PRIMARY KEY, btree (id)
using the Schema::Loader via dbicdump gives me a Result class for this
table that contains this:
## lib/My/Schema/Result/Name.pm
package My::Schema::Result::Name;
use base 'DBIx::Class::Core';
__PACKAGE__-add_columns(
id,
{
data_type = integer,
default_value = \nextval('names_id_seq'::regclass),
is_auto_increment = 1,
is_nullable = 0,
},
);
__PACKAGE__-set_primary_key(id);
###
So far so good, but when I now try to deploy that generated schema to
another PostgreSQL database, I get an error:
$ perl -MMy::Schema -e 'My::Schema-connect(## SOME CONNECT STRING
###)-deploy ; '
NOTICE: CREATE TABLE will create implicit sequence names_id_seq1 for
serial column names.id
DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): DBI
Exception: DBD::Pg::db do failed: ERROR: multiple default values
specified for column id of table names at -e line 1
(running CREATE TABLE names (
id serial DEFAULT nextval('names_id_seq'::regclass) NOT NULL,
PRIMARY KEY (id)
)) at -e line 1
Looking at the generated SQL statement, the error is in the id column
which is defined as type serial AND has an explicit default
nextval('names_id_seq'::regclass), but it should have been simply:
id serial NOT NULL
or
id integer DEFAULT nextval('names_id_seq'::regclass)
but not a combination of both.
The culprit is the line
default_value = \nextval('names_id_seq'::regclass),
in the id column definition of the My::Schema Result class. Indeed,
removing this line fixes the problem and generates a serial NOT NULL
definition for the id column, as it should be.
Is this a bug or is there something I am doing wrong here (is there an
option that I have overlooked that controls this behaviour)? Or do I
simply have to delete all the default definitions manually at the cost
of loosing the ability to auto-update the dumped schema classes with
dbicdump, having changed code above the do not modify line?
Thanks for your help!
Frank
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
___
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
