many-to-many with extra data

[Bill Pemberton]

I've got a database where I need extra data in a many to many
relationship. This would be just like the colors example in the
tutorial, except now there is 1 extra field needed in the map table.
At least, that's how I envision it.

Here's a quick overview of what I'm trying to do. Instead of products
and colors, I have jobs and nodes. Along with the job/node pair, I'll
have 1 more field that is the number of cpus used on that node by the
job. So I'm expecting my map table to look like:

CREATE TABLE job_node_map
(
job_id INT NOT NULL REFERENCES jobs(id),
node_id INT NOT NULL REFERENCES nodes(id),
ncpu INT NOT NULL,

PRIMARY KEY (job_id, node_id)
);

Now the problem.... how do I access ncpu?

[John Siracusa]

On Tue, Feb 16, 2010 at 2:06 PM, Bill Pemberton <joew...@gmail.com> wrote:
> I've got a database where I need extra data in a many to many
> relationship.  This would be just like the colors example in the
> tutorial, except now there is 1 extra field needed in the map table.
> At least, that's how I envision it.

There have been a few threads about this in the past. Here's a post
that summarizes the situation:

http://www.mail-archive.com/rose-db...@lists.sourceforge.net/msg01792.html

Be sure to read the whole thread to get an idea of the context.
Here's a later thread on the same topic:

http://www.mail-archive.com/rose-db...@lists.sourceforge.net/msg01941.html

-John

[Bill Pemberton]

Aha! Thanks. I figured I couldn't be the first to try this, but I
didn't go through the sourceforge list history, just the google one.
Thanks.

[henq]

Hi, (rose)newbie here.  

I have a similar question (5 yrs later ;-) : I want to get extra column-data from the mapping table included in the result form an automatic many_to_many relationship.

In the products/colors example: suppose there is a 'extra_costs' column in the mapping table indicating that some color-product combinations cost extra.  Now I want all colors for 1 specific product, with the extra_costs column joined in. Can I do that with just the relationship $product->colors or is the way to go to setup a query with Color::Manager->get_colors ?   

thanks

henq

[henq]

Meanwhile, I have discovered sql-debugging and I can see that the $product->colors Relationship indeed generates a join with the mapping table as t1 and the colors table as t2. So extra_costs is mentioned in the sql query. But the resulting array contains objects of class Product, and those objects do not have the extra properties/columns from the mapping table....

[Cees Hek]

You need to look at the 'with_map_records' in Rose::DB::Object::Manager.  To use it in a relationship add it in the manager_args parameter like this:

        colours => {

            map_class => 'My::ColoursMap',

            manager_args => { with_map_records => 1 },

            map_from => 'product',

            map_to => 'colour',

            type   => 'many to many',

        },

This will add another method to the objects that you are fetching (in this case the colour objects) called 'map_record' which when called returns the mapping record.

my $colours = $product->colours;

foreach my $colour (@$colours) {

  my $extra_cost = $colour->map_record->extra_costs;

}

Cheers,

Cees

--
Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
---
You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-objec...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[henq]

Cees, thanks!

(Ik ga ermee aan de slag ;-)