self-referencing relationship is not working using datamapper

[eluser]johnmerlino[/eluser]
Hey all,

I followed this documentation carefully:

http://stensi.com/datamapper/pages/database.html

I have two tables:

categories
mothers_offsprings

The mothers_offsprings table has the following structure:

id
offspring_id
mother_id

The categories table has the following structure:

id
controller
user_id
approved
enabled_comments

Basically, I want to be able to create and retrieve categories, but I also want to create and retrieve subcategories and parent categories and build the relationship between them. The idea is subcategories can have many parent categories and parent categories can have many subcategories. So I have the following models:

Code:

<?php
    class Offspring extends Category {

        
        var $has_many = array("mother");
    }
?>

<?php
    class Mother extends Category {
        var $has_many = array("offspring");
    }
?>

<?php
    class Category extends DataMapper {
        var $has_one = array("user");
        var $has_many = array("post");
    }
?>

In my categories controller I try to instantiate mother:

Code:

$parent = new Mother();

I get mysql error:

Code:

Table 'hmlaw.mothers' doesn't exist

SELECT * FROM `mothers` LIMIT 1

I'm not sure why it's trying to select from mother when it should be creating an empty category object since I followed the self-referencing relationship instructions.

Thanks for response.

[eluser]gowrav vishwakarma[/eluser]
By default when you use datamappers class it looks for its table in plural
like if your calss is monther then $x=new Monther() will look for mothers table in database..
First to bypass this default behavior just use var $table="original table to use for this class" as class variable

eg

Code:

class offspring extends datamapper{
var $table='table to use for this class other wise it will search for offsprings' //look at s at last

var $has_one = .....

[eluser]WanWizard[/eluser]
If production caching is not enabled, Datamapper runs a "SELECT * FROM table LIMIT 1" to get the table properties (like column names, types, etc.).

Note that Stensi's Datamapper is VERY old. You'll find the latest version here: http://datamapper.wanwizard.eu. You can also find the latest documentation there, including the table naming rules.

[eluser]johnmerlino[/eluser]
I am using he latest version of datamapper. I should have mentioned that in original post. That documentation I posted is what I found from a google search.

[eluser]johnmerlino[/eluser]
[quote author="WanWizard" date="1302460096"]If production caching is not enabled, Datamapper runs a "SELECT * FROM table LIMIT 1" to get the table properties (like column names, types, etc.).

Note that Stensi's Datamapper is VERY old. You'll find the latest version here: http://datamapper.wanwizard.eu. You can also find the latest documentation there, including the table naming rules.[/quote]

Multiple Relationships to the Same Model:

Code:

class Post extends DataMapper {
    $has_one = array(
        'creator' => array(
            'class' => 'user',
            'other_field' => 'created_post'
        ),
        'editor' => array(
            'class' => 'user',
            'other_field' => 'edited_post'
        )
    );
}

But I am not trying to say that the creater and editer, for example, must be related to something else like post. I'm saying that, in my example, mothers and offsprings are both categories. So when I instantiate a new Mother or new Offspring, I expect it to create a new categories record for each and when I save $mother.save($offspring) I expect it to generate their respective primary keys in the offspring_id and mother_id field of the mothers_offsprings table. Your example above seems to be for a different purpose.

[eluser]johnmerlino[/eluser]
[quote author="gowrav vishwakarma" date="1302431336"]By default when you use datamappers class it looks for its table in plural
like if your calss is monther then $x=new Monther() will look for mothers table in database..
First to bypass this default behavior just use var $table="original table to use for this class" as class variable

eg

Code:

class offspring extends datamapper{
var $table='table to use for this class other wise it will search for offsprings' //look at s at last

var $has_one = .....

[/quote]

But how do I ensure that the offspring_id and mother_id fields of the mothers_offsprings table get updated with the primary keys of the two catergories records ($mother = new Mother; $offspring = new Offspring) that were created?

[eluser]WanWizard[/eluser]
Read the manual on relations.

Datamapper doesn't do anything automagically, If you want to save a relation, you have to explicitly do so:

Code:

$mother = new Mother();
$category = new Category(6); // as an example, load existing category with id 6

// add some data
$mother->fieldA = 'valueA';
$mother->fieldB = 'valueB';

// save the mother record, and relate it to the loaded category
$mother->save($category);

[eluser]johnmerlino[/eluser]
[quote author="WanWizard" date="1302469209"]Read the manual on relations.
[/quote]

I read the documentation several times:

http://datamapper.wanwizard.eu/pages/adv...tions.html

I have a simple situation where a category can have many subcategories and subcategory can have many categories. So I tried using the "Many-to-Many Reciprocal Self Relationships" technique you described in the docs.

I define this in Category model:

Code:

class Category extends DataMapper {
        var $has_one = array("user");
    //    var $has_many = array("post","zone","categorycategory");
                
        public $has_many = array(
            //parent categories that relate to a subcategory
            'related_category' => array(
                'class' => 'category',
                'other_field' => 'category',
                'reciprocal' => TRUE
            ),
            // subcategories that relate to a parent category
            'category' => array(
             //   'class' => 'category', note: not needed since it is already refering to the category class
                'other_field' => 'related_category',
            ),
            'post',
            'zone'
        );
    }

I create a new category and assign it a relationship with existing category that the user selects from dropdown:

Code:

public function create(){
            $subcategory = new Category;
            $subcategory->controller = $this->input->post('controller');
            $subcategory->enable_comments = $this->input->post('approved');
            $subcategory->user_id = $this->current_user()->id;
            
            $parent_category = new Category();
            $parent_category->where('controller',$this->input->post('parent_controller'))->get();
            $subcategory->save($parent_category);
        }

What I expect to happen is that the primary key generated for the new category and the existing primary key for the existing category get populated in category_id and related_category_id fields respectively of the categories_categories table.


However, I am greeted to this instead:

Code:

A Database Error Occurred

Error Number: 1146

Table 'hmlaw.categories_related_categories' doesn't exist

SELECT * FROM (`categories_related_categories`) WHERE `related_category_id` = 42 AND `category_id` = 26

Clearly there is no categories_related_categories table. There's a categories table (which is a self-reference for the parent category and the subcategory) and there's a categories_categories table which the documentation said to create with the related_category_id and category_id field.

What this line appears to do:

Code:

$subcategory->save($parent_category);

it takes the id of the new record created (42) and the id of the existing record (26) and tries to do a select from categories_related_categories, rather than categories_categories.

SELECT * FROM (`categories_related_categories`) WHERE `related_category_id` = 42 AND `category_id` = 26

I have no clue what I am doing wrong. Thanks for response.

[eluser]johnmerlino[/eluser]
Aftter utter failure with the "Many-to-Many Reciprocal Self Relationships" technique, I tried the "Self Relationships" technique. This was met with better success but still a problem.

The problem is with this:

Code:

public function create(){
            $subcategory = new Category;
            $subcategory->controller = $this->input->post('controller');
            $subcategory->enable_comments = $this->input->post('approved');
            $subcategory->user_id = $this->current_user()->id;
            
            $parent_category = new Category();
            $parent_category->where('controller',$this->input->post('parent_controller'))->get();
            $subcategory->save($parent_category);
            var_dump($subcategory->id);
            var_dump($parent_category->id);
        }

There are two category objects:

Code:

var_dump($subcategory->id); //51
            var_dump($parent_category->id); //26

The new object obviously has a higher id value than the existing category object.

Both of the objects are classes of category:

var_dump(get_class($subcategory)); //Category
var_dump(get_class($parent_category)); //Category

as we specified in the model:

Code:

public $has_many = array(
            //parent categories that relate to a subcategory
            'related_category' => array(
                'class' => 'category',
                'other_field' => 'category',
                'reciprocal' => TRUE
            ),
            // subcategories that relate to a parent category
            'category' => array(
             //   'class' => 'category', note: not needed since it is already refering to the category class
                'other_field' => 'related_category',
            ),
            'post',
            'zone'
        );

When we do this:

Code:

$subcategory->save($parent_category);

The join is sought after. It checks the model and finds that the category class is aliased through related_category and category and so does a database look up for category_related_category and when it finds it, it checks that it has the related_category_id and category_id foreign keys since these are specified as the fields that the other table should have.

But then what happens next is unclear to me. It updates the categories_related_categories table giving the catgory_id value of 26 and the related_category_id value of 51.

That's actually reversed. The related_category_id is the $parent_category object that had an id of 26 and the $subcategory object had the id of 51, which was the new record created. So it appears the cateogires_related_categories field is updated backwards.

Thanks for response.

[eluser]WanWizard[/eluser]
Reciprocal means that when you save a relation, it will also be saved in the other direction. You use it when you have parent->child relations, where a child must always have a parent, AND the parent must have a relation with the child at the same time.

The problem here is that you have multiple relations between categories, and when you just use save(), Datamapper doesn't know which relationship you mean as you haven't told Datamapper which one to use. So it defaults to the relation with the same name as the class, which is your second one.

Either use $parent->save_relation($child), or $parent->save($child, 'relation'). See the manual, http://datamapper.wanwizard.eu/pages/save.html, under "saving an advanced relationship".