Warning: Illegal string offset '_imagedir' in /home/cakephpf/public_html/lofiversion/index.php on line 545

Warning: Illegal string offset '_emodir' in /home/cakephpf/public_html/lofiversion/index.php on line 546
CakePHP UnOfficial Community Forum > I need 2 parents from the same table
Help - Search - Member List - Calendar
Full Version: I need 2 parents from the same table
CakePHP UnOfficial Community Forum > Cake Best Practices

marvelade
Hi All,

I'm coding a virtual rabbit pedigree app. (SL Ozimals system, doesn't really matter, the concept is simple)


Each bunny in my bunnies table can have a mother and a father, both members of that same bunnies table. Each bunny has a field "father_id" and "mother_id" which points back to a record of that same table.

Anny suggestions on how I'd code that? I see examples on the internet that code a system for one-parent, but nowhere for 2 parents.

I tried extending the Bunny model class to FatherBunny and MotherBunny, but got a bunch of errors, so I aborted that (guess general OOP practices don't apply to cake anymore cause it ain't pure MVC...)

If anyone can point me in the righti direction, that would be great.

thanks in advance,
Marv

apsillers
Fortunately, part of what you want is described quite nicely in the docs! See http://book.cakephp.org/view/1046/Multiple...-the-same-model.

To associate a bunny with its parents, you'll probably want something like this in your Bunny model:

CODE
var $belongsTo = array(
   'Mother' => array(
       'className' => 'Bunny',
       'foreignKey' => 'mother_id'
   ),
   'Father' => array(
       'className' => 'Bunny',
       'foreignKey' => 'father_id'
   )
);


It's a bit more tricky, though, to associate a Bunny with its children, since there are two different ways a bunny can be a child of another bunny. I suggest something like this:

CODE
var $hasMany = array(
   'Children' => array(
       'className' => 'Bunny',
       'finderQuery' => 'SELECT Bunny.* from bunnies as Bunny WHERE Bunny.mother_id = {$__cakeID__$} OR Bunny.father_id = {$__cakeID__$};'
   )
);


Instead of a foreign key, this hasMany relationship uses an SQL query to find Bunnies that have the current Bunny (whose id is identified by the special token {$__cakeID__$}) as either a mother or father. I'm reasonably confident that this will do what you want, but I don't have experience working with finderQuery. (A little bit of info on finderQuery is located halfway down this page: http://book.cakephp.org/view/1043/hasMany.)
marvelade
Hey apsillers,

thx for the swift reply!

I could've sworn that I'd already tried that, but I think I used a hasOne instead of a belongsTo relationship (linguistically "a bunny has one father" means the same as "a bunny belongs to a father", but I guess there's a difference in OOP terminology which i didn't quite comprehend, and to be honest still don't, but I'll study that further today smile.gif )


Anyway I pasted in the 2 variables you provided but then came up with this error:

QUOTE
SQL Error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23,' at line 1

Query: SELECT Bunny.* from bunnies as Bunny WHERE Bunny.mother_id =1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76 OR Bunny.father_id = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76;


Since this is a series of id's, my first guess would be to enclose this list in parentheses and replace the"=" with "IN":


CODE
'finderQuery' => 'SELECT Bunny.* from bunnies as Bunny WHERE Bunny.mother_id IN ({$__cakeID__$}) OR Bunny.father_id IN ({$__cakeID__$});


this takes away the SQL error, since it is now a valid SQL query, but I get a new error notice saying:

QUOTE
Undefined index:  Children [CORE/cake/libs/model/datasources/dbo_source.php, line 1084]


which I don't understand what it means in CakePHP.

I'm not sure that what I did to the SQL query was the right thing to do, but I'm looking forward to anyones feedback or other thoughts on this matter.

Another thought would be to further narrow down the records that are eligable as a father or a mother using the sex field of the bunnies table. it's an ENUM field which can have the values 'Unknown', 'Male', 'Female'. The 'Unknown' status is used when a bunny is still a genderless nest that came from 2 parents, but it needs to be inserted in the database already.


Best regards and many thanks,
Marv



marvelade
P.S.: Found this on a Rails forum (http://stackoverflow.com/questions/899981/...s-to-vs-has-one)

QUOTE
Your new solution is how we think of the situation, but it will not work. When you do bug.status, it will look for a bug_id column in status, which does not exist. A has_one or has_many needs to be matched by a belongs_to in the class that is being "had".
... 
It should be Bug.belongs_to :status, like the asker guessed. It looks awkward, but that's the way Rails needs the relationship to be expressed AFAIK. The belongs_to means "my table has the foreign key." That way bug.status and status.bugs will both work correctly



I think the bold statements are key to the concept or am I wrong?

EDIT:

I now find this blogpost that tells it exactly the other way around, I'm confused :s
(http://jbenner.net/blog/understanding-cakephp-associations)



best regards,
Marv
apsillers
The way I always remember hasOne vs. belongsTo is this:

Think about what the hasMany relationship means in database terms, and then replace "many" with "one".

"A hasMany B" means there are many B records that point to any given A
"A hasOne B" means that there is one B record that points to any given A

On the other hand, "B belongsTo A" means that every B knows innately which A it belongs to, because it has a field pointing to some A. If you found that B lying by the side of the road, you'd know immediately which A it belonged to, because it has the A's id stamped right on it.

And as for your problem above: could identify and share exactly what bit of code is giving you that error?
marvelade
Hey Apsillers,

First off thanks for your time looking into this. I know I'm a total MVC n00b, but I hope I can get the hang of it with the help of helpful ppl like yourself. smile.gif

1) I have another issue with my index listing, but I'll probably be able to solve that when I get the relations straight. For some reason the controller method index() that creates the $bunnies variable in the index View with this line of code:
[PHP]
$this -> set('bunnies', $this -> Bunny -> find('all', Array('order' => 'Bunny.name ASC')) );
[/PHP]

messes up REALLY bad. When I print_r the $bunnies array only a fraction of the data from the tables is included in this multidim array even thought the data is present in the tables.

Again, this might be due to my crappy understanding of Cake's MVC implementation which seamlessly brings me to the next item smile.gif


2) Let's see if I'm getting this straight:

Each bunnies record has an 'ear_type_id' field which points to the table "ear_types"
QUOTE

"A hasOne B" means that there is one B record that points to any given A


substituting my case:

QUOTE

"Bunny hasOne Eartype" means that there is one ear_types record that points to any given bunny.


That's not true... Eartype doesn't point anywhere; It's basically just an ID => Value list of kinds of ears that a bunny can have:

id ear_type_name
----------------------
1 Upright
2 Half-a-lop
3 Lop


So, from what your saying, it's not "Bunny hasOne EarType" and "EarType hasMany Bunny" ?

'cause The way I have it coded now is like this:


[PHP]
/*models/bunny.php*/
class Bunny extends AppModel
{
.....
var $hasOne = Array(
....
....
"EarType" => array(
'className' => 'EarType',
'foreignKey' => 'id'
),

.....
}


/*models/ear_type.php*/
class EarType extends AppModel
{
var $name = "EarType";
var $hasMany = Array("Bunny" => Array('className' => 'Bunny',
'foreignKey' => 'id',
'dependent' => false
)
);
}


[/PHP]

reading this code back it strikes me as odd that there's no belongsTo in either end of this relationship and it still works, on top of the fact that the foreign key is "id" 2 times, there where I'd expect that at least one of them would be ear_type_id, no?


/me is still confused...



3) When I click the notice link I get this stack trace:


CODE

DboSource::__mergeHasMany() - CORE/cake/libs/model/datasources/dbo_source.php, line 1084
DboSource::queryAssociation() - CORE/cake/libs/model/datasources/dbo_source.php, line 950
DboSource::read() - CORE/cake/libs/model/datasources/dbo_source.php, line 838
Model::find() - CORE/cake/libs/model/model.php, line 2087
BunniesController::index() - APP/controllers/bunnies_controller.php, line 16
Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 204
Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 171
[main] - APP/webroot/index.php, line 83


line 16 of my bunnies_controller is inside the index method :


[PHP]function index()
{
$this -> _setFathers();
$this -> _setMothers();

$this -> set('bunnies', $this -> Bunny -> find('all', Array('order' => 'Bunny.name ASC')) ); // this is line 16
}[/PHP]


however, the only place where I have the word 'Children' is in the $hasMany var inside my Bunny model:


Here's the complete model file bunny.php:

[PHP]
class Bunny extends AppModel
{
var $name = "Bunny";

var $belongsTo = array(


'Mother' => array(
'className' => 'Bunny',
'foreignKey' => 'mother_id',
'conditions' => "Bunny.sex = 'Female'"
),

'Father' => array(
'className' => 'Bunny',
'foreignKey' => 'father_id',
'conditions' => "Bunny.sex = 'Male'"
),
);

var $hasMany = array(
'Children' => array(
'className' => 'Bunny',
'conditions' => "Bunny.father_id > 0 AND Bunny.mother_id > 0",
'finderQuery' => ' SELECT Bunny.*
FROM bunnies as Bunny
WHERE
Bunny.mother_id IN ({$__cakeID__$})
OR
Bunny.father_id IN ({$__cakeID__$});'

)
);



var $hasOne = Array(

"EyeColor" => array(
'className' => 'EyeColor',
'foreignKey' => 'id'
),

"MainType" => array(
'className' => 'MainType',
'foreignKey' => 'id'
),


"SubType" => array(
'className' => 'SubType',
'foreignKey' => 'id'
),


"EarType" => array(
'className' => 'EarType',
'foreignKey' => 'id'
),


"Shade" => array(
'className' => 'Shade',
'foreignKey' => 'id'
),



);
}
[/PHP]

Anyone with some form of advice is more than welcome to give their opinion.

best regards,
Marv
apsillers
1) See my response to point #3 below, since its directly related to your #1.


2) Here is another hasMany/hasOne/belongsTo example, using people owning cars:

If I (a person) want to show that a car belongs to me, I can write my name on it. Every time I collect a new car, I write my name (or, really, my id) on it to show it belongs to me. If you find a car, you can see which person that car belongs to by checking what name/id is written on it. The car has a property (in CakePHP, it's called person_id) that points to its owner.

Thus, a person hasMany cars; the person_id written on each car points to its owner, and a person may have many cars pointing to them.

If we pass a law that says each person may have only one car, then we've just redefined the relationship so a person hasOne car. the owner of each car is still identifiable by looking at the name/id on the car (so car belongsTo person, still) but we're certain that each person has exactly one car.

And so:

You are correct that EarType hasMany Bunny; there are many Bunnies that point to any given EarType (just like many cars point to any given person). However, this means that Bunny belongsTo EarType. Each bunny records holds an id for an EarType -- this means it belongsTo earType, just like the cars held their owner's id.

- The act of pointing (i.e. knowing something else's id) implies belonging to the target. Think of the foriegnKey here as a pointer to a record's owner, like writing your name on stuff you own.

- Being pointed at implies having everyone who points at you. The foreignKey in a hasMany/hasOne relationship indicates how the other class will point at this object.

In both cases your foreignKey should be ear_type_id, since that's how bunny points to EarType and how EarType is pointed to by Bunny


3) The reson you're getting the error is that the find() operation is trying to get models related to the ones you requested, since find() by default helpfully returns objects directly related to the objects you asked to find. This is because of the "recursion=1" setting in find() (if you set recursion to 2, for example, it would do even more and return objects that were two relationships away).

Anyway, that just explains why "Children" has shown up at all. I am not sure what is actually going wrong when trying to fetch a Bunny's children. I'd suggest stripping down the finderQuery SQL to something really basic, then slowly building it back up, and see at what point it breaks. (Do this after you fix the other relationships, though.)

One more thing: all of your hasOne relationships should be belongsTo relationships, and the foreignKeys for all of them should be set to (whatever)_id, instead of just id.

(And you're quite welcome -- I'm just learning CakePHP myself, and I've found that the best way to learn it is to help other people with their own problems. It give me a reason me to read (and re-read) the docs a lot more, and think through tough CakePHP challenges. smile.gif )
marvelade
Good morning Apsillers (I don't know if it's morning where you live though, but still smile.gif ),

1) It'll be easier to adress this once I've read the rest, indeed.

2) Please allow me to rephrase this in my own words and see if the concept made it to my brain OK:

a ) Marvelade hasMany books, but doesn't quite know by himself which ones. To keep track of this he does the following:
b ) In every book he writes on the first page "This book belongsTo Marvelade"

From looking at the first page of that book, Anyone can see who the owner is (provided that they know who I am wink.gif ) , but if someone asked me "What books do you have" I couldn't accurately answer that, cause I don't keep a record of it; I just know that I have many, but that's just about it. Somehow every book returns to me after someone has borrowed it, because each of my books belongsTo me ('cause it says so on the first page).

Actually the bold parts in my quote from yesterday make much more sense to me now:

QUOTE

A has_one or has_many needs to be matched by a belongs_to in the class that is being "had".
The belongs_to means "my table has the foreign key."



Which makes total sense in my book example. If I don't write 'This book belongsTo Marvelade' on the first page and I lended it to someone, there's a slim chance of me ever getting it back, because the borrower isn't reminded of who the books owner is. The belongsTo in the books first page means "my first page has the foreign key (a.k.a. points to my owner)"

This also tells me that having a foreignKey attribute inside a hasOne or hasMany doesn't make any sense, because tables that haveOne X or haveMany Y by definition DON'T have the foreign key. Only the belongsTo can have that.


Please let me know if I'm getting it right smile.gif

3) I've rewritten my bunny model like this, which totally solved problem #1 THANKS!!!:

[PHP]
<?php

class Bunny extends AppModel
{
var $name = "Bunny";

var $belongsTo = array(


'Mother' => array(
'className' => 'Bunny',
'foreignKey' => 'mother_id',
'conditions' => "Bunny.sex = 'Female'"
),

'Father' => array(
'className' => 'Bunny',
'foreignKey' => 'father_id',
'conditions' => "Bunny.sex = 'Male'"
),
"EyeColor" => array(
'className' => 'EyeColor',
'foreignKey' => 'eye_color_id'
),

"MainType" => array(
'className' => 'MainType',
'foreignKey' => 'main_type_id'
),


"SubType" => array(
'className' => 'SubType',
'foreignKey' => 'sub_type_id'
),


"EarType" => array(
'className' => 'EarType',
'foreignKey' => 'ear_type_id'
),


"Shade" => array(
'className' => 'Shade',
'foreignKey' => 'shade_id'
),



);

var $hasMany = array(
'Children' => array(
'className' => 'Bunny',
'conditions' => "Bunny.father_id > 0 AND Bunny.mother_id > 0",
'finderQuery' => ' SELECT Bunny.*
FROM bunnies as Bunny
WHERE
Bunny.mother_id IN ({$__cakeID__$})
OR
Bunny.father_id IN ({$__cakeID__$});'

)
);
}
?>
[/PHP]


I'll try and mess with the finderQuery and see if that can shed a ligh on the matter, but that's for another post.

Thanks a mill for helping me get this far!


Best regards,
Marv
marvelade
Ok, so I completely cut out the Children node from the hasMany var and the result is the same (except the error is gone smile.gif ).

What I apparently needed to do was also eliminate the "conditions" key from the Mother and Father aliases so now I can reach the mother in the view using $bunny['Mother']['Name'], which only returned data for 3 of my 70+ bunnies when there was still a condition of Bunny.sex='Female' resp. Bunny.sex = 'Male'....

Those 3 bunnies were either fathers who had at least one son or mothers who had at least one daughter from the looks of it, so that kinda makes sense, since the condition was Bunny.sex and not Father.sex. Anyway, the program now acts as it's supposed to without those conditions. (at least the index view. I'm now struggling with the edit view which apparently is a whole other story smile.gif )

For clarity, here's my revised bunny.php model class:

[PHP]<?php

class Bunny extends AppModel
{
var $name = "Bunny";

var $belongsTo = array(


'Mother' => array(
'className' => 'Bunny',
'foreignKey' => 'mother_id'
),

'Father' => array(
'className' => 'Bunny',
'foreignKey' => 'father_id'
),

"EyeColor" => array(
'className' => 'EyeColor',
'foreignKey' => 'eye_color_id'
),

"MainType" => array(
'className' => 'MainType',
'foreignKey' => 'main_type_id'
),


"SubType" => array(
'className' => 'SubType',
'foreignKey' => 'sub_type_id'
),


"EarType" => array(
'className' => 'EarType',
'foreignKey' => 'ear_type_id'
),


"Shade" => array(
'className' => 'Shade',
'foreignKey' => 'shade_id'
),



);

}

?>
[/PHP]
apsillers
Excellent! Glad you're getting somewhere. smile.gif

And, yes, from your example, you understand hasMany/hasOne/beongsTo perfectly. As you said, if a person hasMany books, there's no way for a Person record to have hold all of its related Book keys. The books hold their person_id, because they belongTo a person. So just remember that 1) hasMany doesn't know any of its related keys and that 2) hasOne works the same way as hasMany, in terms of keys.

Well, it looks like you've gotten past the original issues in this post, so I guess you can start new threads for any new issues you run into.

See you around!
marvelade
I have a basic version running now, but I have some features I want to add. I'll post new topics as I run into problems (which will most likely happen smile.gif ).

Thanks for your immense help so far; it's really appreciated.



Best regards,
Marv
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.
Lo-Fi 1.1 iDS Beta, Originally written by Matt,
re-written by Shaun Harrison, Layer 04.com, for pre IPB2.0 versions.