Re: [Dbix-class] The Definitive Guide to Catalyst ... p. 165 listing all users and their roles
On Wed, Jul 11 2012, Robyn Jonahs learn.catal...@gmail.com wrote: ul [% FOREACH role = user.user_roles %] li[% role.role_id.role %]/li [% END %] /ul try this: ul [% FOREACH role = user.roles %] li[% role.id %] [% role.role %]/li [% END %] /ul explanation: in your User Result class, you have a many_to_many called 'roles' which returns the Role's associated with the user (joining via the user_role table), then you can call the accessors on the Role object to display the data you need. HTH patrick -- Patrick Meidl patr...@pantheon.at Vienna, Austria .. http://gplus.to/pmeidl ___ 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
On Wed, Jul 11 2012, Robyn Jonahs learn.catal...@gmail.com wrote:
I was trying to get some many_to_many data into a FormFU select
element but I am having trouble. I think that it is time to ask the
pros for help.
I was staying similar to the online tutorial. In my add and edit sub
routines, I have the following code (similar to the online tutorial)
# Stores
my @store_objs = $c-model(DB::Store)-all();
my @stores;
foreach (sort {$a-store cmp $b-store} @store_objs) {
# push(@stores, [$_-id, $_-store]);
push(@stores, [$_-id, $_-full_address, $_-store]);
}
my $select3 = $form-get_element({name = 'stores'});
$select3-options(\@stores);
So I want to get a list of all the values in a table called stores and
stick them into a select element in FormFU. I can do this fine. What I
wanted to do was to give it the full address of the store bringing in
the state, city and country from many_to_many relationships with these
tables. I was able to do this in another test application but the
relationships were has_many not many_to_many. I am stumped on how to
do this.
The store model is simple
__PACKAGE__-add_columns(
id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0 },
store,
{ data_type = varchar, is_nullable = 0, size = 255 },
notes,
{ data_type = varchar, is_nullable = 1, size = 255 },
);
and it contains
__PACKAGE__-has_many(
store_cities,
ProductTracker::Schema::Result::StoreCity,
{ foreign.store_id = self.id },
{ cascade_copy = 0, cascade_delete = 0 },
);
...
__PACKAGE__-many_to_many(cities, store_cities, city);
And I tried to do this...
#
# Row-level helper methods
#
=head2 full_address experimental
Get the city, state and country that correspond to a specific Store ID
=cut
sub full_address {
my ($self) = @_;
my $cities = $self-store_cities;
return $self-store . ' ' . $cities-city-city;
#return $self-store . ' ' . $self-city-city . ', ' .
$self-states-abb . ' ' . $self-countries-abb;
}
there are several errors in this method.
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.
second, since you are dealing with a many_to_many, there might be
multiple cities associated to a store, you have have to deal with this.
so your code should look something like this:
--8---
sub full_address {
my ($self) = @_;
my $result = $self-store;
my @cities = $self-cities;
foreach my $city (@cities) {
$result .= sprintf(' | %s, %s %s', $city-city,
$self-states-abb, $self-countries-abb);
}
return $result;
}
--8---
not sure about the calls to states and countries, you didn't include
the code what these methods return; look like many_to_many to me too, or
rather belongs_to relations on City? so I'm speculating that your code
will look like
$result .= sprintf(' | %s, %s %s', $city-city,
$city-state-abb, $city-country-abb);
HTH
patrick
--
Patrick Meidl patr...@pantheon.at
Vienna, Austria .. http://gplus.to/pmeidl
___
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
Hi thanks. I will try it.
The cities, states and country relationships are all many_to_many so once I
have one, I can do the others.
RJ
On Thu, Jul 12, 2012 at 3:48 AM, Patrick Meidl patr...@pantheon.at wrote:
On Wed, Jul 11 2012, Robyn Jonahs learn.catal...@gmail.com wrote:
I was trying to get some many_to_many data into a FormFU select
element but I am having trouble. I think that it is time to ask the
pros for help.
I was staying similar to the online tutorial. In my add and edit sub
routines, I have the following code (similar to the online tutorial)
# Stores
my @store_objs = $c-model(DB::Store)-all();
my @stores;
foreach (sort {$a-store cmp $b-store} @store_objs) {
# push(@stores, [$_-id, $_-store]);
push(@stores, [$_-id, $_-full_address, $_-store]);
}
my $select3 = $form-get_element({name = 'stores'});
$select3-options(\@stores);
So I want to get a list of all the values in a table called stores and
stick them into a select element in FormFU. I can do this fine. What I
wanted to do was to give it the full address of the store bringing in
the state, city and country from many_to_many relationships with these
tables. I was able to do this in another test application but the
relationships were has_many not many_to_many. I am stumped on how to
do this.
The store model is simple
__PACKAGE__-add_columns(
id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0 },
store,
{ data_type = varchar, is_nullable = 0, size = 255 },
notes,
{ data_type = varchar, is_nullable = 1, size = 255 },
);
and it contains
__PACKAGE__-has_many(
store_cities,
ProductTracker::Schema::Result::StoreCity,
{ foreign.store_id = self.id },
{ cascade_copy = 0, cascade_delete = 0 },
);
...
__PACKAGE__-many_to_many(cities, store_cities, city);
And I tried to do this...
#
# Row-level helper methods
#
=head2 full_address experimental
Get the city, state and country that correspond to a specific Store ID
=cut
sub full_address {
my ($self) = @_;
my $cities = $self-store_cities;
return $self-store . ' ' . $cities-city-city;
#return $self-store . ' ' . $self-city-city . ', ' .
$self-states-abb . ' ' . $self-countries-abb;
}
there are several errors in this method.
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.
second, since you are dealing with a many_to_many, there might be
multiple cities associated to a store, you have have to deal with this.
so your code should look something like this:
--8---
sub full_address {
my ($self) = @_;
my $result = $self-store;
my @cities = $self-cities;
foreach my $city (@cities) {
$result .= sprintf(' | %s, %s %s', $city-city,
$self-states-abb, $self-countries-abb);
}
return $result;
}
--8---
not sure about the calls to states and countries, you didn't include
the code what these methods return; look like many_to_many to me too, or
rather belongs_to relations on City? so I'm speculating that your code
will look like
$result .= sprintf(' | %s, %s %s', $city-city,
$city-state-abb, $city-country-abb);
HTH
patrick
--
Patrick Meidl patr...@pantheon.at
Vienna, Austria .. http://gplus.to/pmeidl
___
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
Re: [Dbix-class] many_to_many data access
On Thu, Jul 12 2012, Robyn Jonahs learn.catal...@gmail.com wrote:
The cities, states and country relationships are all many_to_many so
once I have one, I can do the others.
as I mentioned before, in terms of a clean database design, shouln't
your relationships look like this: City belongs_to a State, which in
turn belongs_to a Country. having city, state and country as independent
many_to_many on the store violates the true relationships between these
entities.
if you implement it like this, you would end up with code like this:
foreach my $city ($store-cities) {
print City: . $city-city . \n;
print State: . $city-state-name . \n;
print Country: . $city-state-country-name . \n;
}
you could also use the proxy attribute on your belongs_to relations to
shortcut these calls to something like $city-state_name and
$city-country_name.
HTH
patrick
--
Patrick Meidl patr...@pantheon.at
Vienna, Austria .. http://gplus.to/pmeidl
___
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
On Thu, Jul 12, 2012 at 09:53:30AM +0100, Frank Schwach wrote: except if you are expecting international customers: in many countries there are no states, so a city really only might_have a state and the city itself belongs_to a country, The (optional) state also belongs_to a country And not all addresses have a city, not all addresses (even within a single country) have the same number of elements, and some places are in multiple countries. Oh what fun! Stupid real world being hard to model. -- David Cantrell | Nth greatest programmer in the world We found no search results for crotchet. Did you mean crotch? ___ 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] Re: as_subselect_rs-delete bug?
This also leads to the idea that you should be able to introspect the $rs for various conditions, like $rs-is_read_only and $rs-has_limit and such. Currently, these parameters are hard (and in some cases, impossible) to introspect. While the idea that all resultsets are created equal has been very good, I don't think it can continue to stand up in the real world. Rob On Wed, Jul 11, 2012 at 6:47 PM, fREW Schmidt fri...@gmail.com wrote: On Wed, Jul 11, 2012 at 8:14 PM, Toby Corkindale t...@dryft.net wrote: On 11 July 2012 23:35, Rob Kinyon rob.kin...@gmail.com wrote: On Wed, Jul 11, 2012 at 6:11 AM, fREW Schmidt fri...@gmail.com wrote: I wrote a test that confirms it, but it actually has no where clause at all when I test it, which is even scarier. Before we go ahead confirming failures and making fixes, let's step back and discuss exactly what $rs-search(...)-as_subselect_rs-delete should actually do and if it should be something that's allowable. The point behind as_subselect_rs was to allow a resultset to be used as the RHS for a column in a search clause. If $rs-search() could detect that a $rs is the RHS, then -as_subselect_rs() would never be called by a user. More to the point, the resultset returned by as_subselect_rs, when I designed the subquery thing, was never meant to be used as a general-purpose $rs, but only in specific contexts. So, it makes perfect sense that calling -delete() on this special-purpose $rs wouldn't do the right thing. I am really curious as to the chain of reasoning that led to the use of -as_subselect_rs(). Toby, as a workaround in the meantime if you remove the as_subselect_rs it seems to do the right thing. Of course it does the right thing. The as_subselect_rs is an error in this case. It looks clearly wrong in the example I gave, but inside a working program you tend to be passing around variables. You can confirm that they contain a ResultSet object, sure, but you don't know much more than that about them. But if you have a resultset, you assume you can -search, -count, and even -delete it. What I'm saying it -- you don't always know that someone, somewhere else in the code, has started calling your methods with resultsets generated by -as_subselect_rs. But if you call -delete on that $rs variable, weird and unexpected things happen to the data inside your database. So.. while it is easy to say that I should just remove as_subselect_rs from the query, please consider that DBIC should throw an error if this condition occurs. Agreed, the options are either fix it or error; the current action is completely wrong. -- 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 -- Thanks, Rob Kinyon ___ 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
On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt fri...@gmail.com wrote: On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl patr...@pantheon.atwrote: 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. Wow, you guys are great. I was trying to build an app to help my real life and at the same time learn Catalyst better. What I was trying to build was a helper to track the prices of things I buy regularly. I used to keep a MS Excel (Ack) file with a table of prices and the low price that I saw. This was great but after keeping this for years all I had was the lowest price I saw in N years which was getting on to be 5. So I figured time to dust off the little I learned about Catalyst and make a more powerful data tracker. I also move around a lot so being able to keep things separate seemed to make sense since prices of things I may buy vary with country, state/province, city... (The main failure of my app is data entry which I have to do when I look at prices of things each week it would be nice to somehow get the prices automagically for the things I tend to shop for.) In any case I track prices among other data, each price is associated with a store and the store needs a location to identify it. Instead of having just an address associated with the store, I figured I would be reusing country, state/province and city (proxy for address as I did not think I needed that much info). To maximize reuse of the state/province, city and country tables, I thought that many to many was best at the time I designed my Database. This I figured allowed the most flexibility but I am doubting if that was the best practice. I probably have not made it all best practice. For the addresses, I have Stores many_to_many with cities, states, and countries I thought that way the many_to_many would let me have concise tables for each of these and then the join tables would be able to keep it all straight. This way, there would not be a lot of repeated data, e.g. London would not be typed into a field each time but once and then the ids would make the proper association. I agree with the maintainability issue if it were open to others and they added wrong data. I did not want to populate a table with all the cities in the world and the countries and the state/provinces, I was letting that get generated as time went on, it seemed simpler. I have a select list for the store (hopefully with store city, state/province country) in a select list and have a separate controller to add a new city, state/province or country. This was my attempt to keep it clean with respect to data entry. This is only my fourth or fifth Catalyst application that I am playing with to learn so I trying to figure out the best practice. That is the goal, if that helps guide the excellent comments, I would love to hear what works best for keeping track of addresses and letting them be global. Global in the sense that I want to get all records on a city or a country (that seems unlikely) or a province (which also seems unlikely). Am I opening up a huge can of worms by asking for a best practice on tracking addresses? ___ 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] The Definitive Guide to Catalyst ... p. 165 listing all users and their roles
On Thu, Jul 12, 2012 at 3:26 AM, Patrick Meidl patr...@pantheon.at wrote: On Wed, Jul 11 2012, Robyn Jonahs learn.catal...@gmail.com wrote: ul [% FOREACH role = user.user_roles %] li[% role.role_id.role %]/li [% END %] /ul try this: ul [% FOREACH role = user.roles %] li[% role.id %] [% role.role %]/li [% END %] /ul explanation: in your User Result class, you have a many_to_many called 'roles' which returns the Role's associated with the user (joining via the user_role table), then you can call the accessors on the Role object to display the data you need. HTH patrick -- Patrick Meidl patr...@pantheon.at Vienna, Austria .. http://gplus.to/pmeidl Thanks, this also worked. ___ 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
On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt fri...@gmail.com wrote: On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl patr...@pantheon.atwrote: 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 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. ___ 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
On Thu, Jul 12, 2012 at 1:24 PM, Robyn Jonahs learn.catal...@gmail.comwrote:
On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt fri...@gmail.com wrote:
On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl patr...@pantheon.atwrote:
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
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;
my @countries = $self-countries;
$result .= ' ' . @countries[0]-abb;
return $result;
}
thanks to everyone.
___
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
On Thu, Jul 12 2012, fREW Schmidt fri...@gmail.com wrote: On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl 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. [...] It's a very handy and powerful pattern. agreed, that's very useful, and I use often myself. but in the OP's use case, there was a plain join table, and I think the cool thing about many_to_many in DBIc is that you can treat your object relationships in a much more natural way, i.e. ignore the fact that your underlying database needs a join table to represent the relationship, and focus on the domain model point-of-view. but as I said, I didn't want to say that you shouldn't use join tables to hold additional data if it's handy for modelling your data. patrick -- Patrick Meidl patr...@pantheon.at Vienna, Austria .. http://gplus.to/pmeidl ___ 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
