[eluser]abbie[/eluser]
In the example:
parts - id, name
machines - id, name
part_machine - id, part_id, machine_id, setting_1, setting_2, setting_3
how do I insert data into: setting_1, _2, _3?
[eluser]warrennz[/eluser]
@abbie
You would use the set_join_field() method.
I cannot answer you question as I am having an issue which I am guessing you will run into.
Using Abbie's example, and assuming it's a M:M relationship.
I realize this example may not make alot of sense in the context of machines and parts but just bare with me 
If a machine had 2 identical parts, so in the database it had
Code: ------------------------------------------------
| id | machine_id | part_id | setting_1 | sett....
|-----|-----------------------|------------|------
| 1 | 1 | 2 | |
| 2 | 1 | 2 |
----------------------------------
If you were to attempt to set some data in setting 1, 2 or 3 you would do
Code: $m = new Machine();
$m->get_by_id(1);
$p = new Part()
$p->get_by_id(2)
$p->join_related_field($m,'setting_1','my custom setting');
This would essentially run a SQL Query along the lines of
Quote:UPDATE `machine_parts` SET `setting_1` = 'my custom setting' WHERE `machine_id` = 1 AND `part_id` = 2
As one mite have guessed, it will update BOTH the relation fields, which is not the desired effect.
Code: ------------------------------------------------
| id | machine_id | part_id | setting_1 | sett....
|-----|-----------------------|---------------------|------
| 1 | 1 | 2 | my custom setting |
| 2 | 1 | 2 | my custom setting |
------------------------------------------------------
Im at a total loss how to get around this one.. maybe I'm looking at this all wrong?
I was thinking the obvious fix was to use the id column to help single out the row I wanted EG
Code: $p->where('machine_parts.id','2')->join_related_field($m,'setting_1','my custom setting');
But then I found myself wondering how to get that id?
When you save the new relationship via $p->save($m), does one of those objects store the specific relations ID?
Argh >< lost and confused on this one
[eluser]OverZealous[/eluser]
@warrennz
There is no way to save more than one relationship between the same ids using the same relationship name. This is mentioned in the notes on the first post. (The id field is not actually used internally by DataMapper. It is simply recommended for good database design.)
If you want to save what you are showing, you'll need to specify the relationships more accurately. For example, if you are trying to save creator, editor, and assigned_to, all which point to a User, then you need to create multiple relationships, one for each type. As long as you are not creating a self-relationship, then these can all be stored in the same table (e.g.: posts_users), which will need to have a column for each type (e.g.: post_id, creator_id, editor_id, assigned_to_id), all of which need to allow NULLs. Alternatively, you can store the $has_one relationships on the posts table.
In other words, what are you trying to store there?
There is no way in your example for DM to ever know which row you want to save into. In fact, there is simply no way to even create multiple rows between the same two objects, as DM uses the two ids to perform lookups.
One possible way around it, in your example, is to have a dedicated intermediate model to manage the relationships. For example, you could have a model called MachinePart, and it would look like this:
Code: // ------------------------------
// model
// ------------------------------
class MachinePart extends DataMapper {
$has_one = array('machine', 'part');
// etc.
}
// ------------------------------
// table:
// ------------------------------
machineparts: id, machine_id, part_id, setting_1, setting_2, ...
// ------------------------------
// controller:
// ------------------------------
// add a part
$m = new Machine(); ...
$p = new Part(); ...
$mp = new MachinePart();
$mp->setting_1 = $setting_1_value;
$mp->save(array($m, $p));
// get all parts for the machine $m
$parts = new Part();
// simple trick to avoid making multiple queries
$parts->where_related_machinepart('machine_id', $m->id);
$parts->join_related('machinepart', array('setting_1', 'setting_2', ...));
$parts->get();
foreach($parts->all as $part) {
echo($part->name);
echo($part->machinepart_setting_1);
echo($part->machinepart_setting_2);
...
}
// remove all machine-part relationships
$m->machinepart->get()->delete_all();
// remove a specific machine-part relationship, by id
$mp = new MachinePart();
$mp->get_by_id($mp_id);
$mp->delete(); // also removes relationships
Does that make sense?
@abbie
warrennz is correct, in that you use set_join_field, which is explained in the first post, under the heading Update, View, and Query Extra Columns on Join Tables.
[eluser]warrennz[/eluser]
Basically what I have is an account M:M with items. Both tables are pre-populated. Items get added to an account, I need to be able to add multiples of the same item and also manage a few extra fields in the join table such as quantity, color etc.
Ta
[eluser]abbie[/eluser]
That makes perfect sense TYVM, and nice work on DMZ!
[eluser]OverZealous[/eluser]
@warrennz
I just remembered something: in my example, the trick to load all Parts won't work, because DataMapper will collapse all values with unique ids when it populates all.
Instead, you'd have to reverse the join_related, so that you looked up MachineParts, and joined in Parts:
Code: // get all parts for the machine $m
$mps = $m->machinepart;
$mps->join_related('part', array('id', 'name', ...));
$mps->get();
foreach($mps->all as $mp) {
echo($mp->setting_1);
echo($mp->setting_2);
echo($mp->part_id);
echo($mp->part_name);
...
}
It's a little less graceful, but it does work. (Also, Parts have many MachineParts, so they can't use join_related ... Doh!)
[eluser]warrennz[/eluser]
@OverZealous
Thanks so much for your time in working on this. At this stage I can honestly say Ive lost you.. I guess my skills aren't of that caliber..yet
From what a gather, this model centered between machine and parts, would create its own table, and thusly create 2 tables on either side?
Code: Table:Machine -> Joining table <---> Table:MachineParts <---> Joining table <- Table:Parts
ID 1 MID 1, MPID 5 MPMID 5, MPPID 4 MPID 4, PID 99 ID 99
Thanks
[eluser]OverZealous[/eluser]
Except, you don't need joining tables.
The MachineParts table can contain machine_id and part_id.
What you are effectively doing is converting the machines_parts joining table (which is merely used to represent a relationship) into a full model (MachineParts). This model can then be used to link the same part to the same machine multiple times.
Code: Table:Machine --> Table:MachineParts <-- Table:Parts
ID 1 ID 1, MID 1, PID 8 ID 8
ID 1 ID 2, MID 1, PID 8 ID 8
ID 1 ID 3, MID 1, PID 9 ID 9
ID 1 ID 4, MID 1, PID 9 ID 9
ID 2 ID 5, MID 2, PID 8 ID 8
ID 2 ID 6, MID 2, PID 9 ID 9
... ... ...
Then read over the code from a few posts ago (excluding the lookup, look to the last one I posted).
The biggest drawback to this method is that you lose the ability to look up or save parts like this:
Code: // NO LONGER WORKS!
$machine->parts->get();
$machine->save($part);
// NO LONGER WORKS!
The benefit is you can now have multiple joins between the same machine and part, and you can more easily store additional information about the join (including validation rules).
[eluser]warrennz[/eluser]
So, my table structure would essentially be the same? cos that example is exactly what I have right now.
I would just additionally add a model with 1:1 relationship to manage that middle table?..
[eluser]OverZealous[/eluser]
Yes, but, of course, you have to rename the table itself.
Also, it's two N:1 relationships, $has_many on Machine and Part contains 'machinepart', and $has_one on MachinePart contains 'machine' and 'part'.
|
