DataMapper ORM v1.8.0

[eluser]danijelb[/eluser]
[quote author="introvert" date="1303932135"][quote author="danijelb" date="1303931608"]Hello

I have an unusual relationship and I don't know how to connect those two tables.

First table is Contents, second table is Pages...
Every row in Contents has in_table_id (page_id) and every of the content type tables (Pages, Articles, Images) have an id...

How should I connect those two?

I would normally do this:

Code:

SELECT p.id, p.title, p.content, p.language_id, c.id as content_id, c.slug, c.uid, c.parent_id
FROM pages p
LEFT JOIN contents AS c
ON p.id = c.in_table_id

[/quote]

Oh my god, rtfm - model relations.[/quote]
I did...

Wouldn't come here if I didn't try to connect them myself first -.- ...

Whatever I do, DataMapper expects from me to have a seperate relationship table. Also, nothing useful in "In-table relations" in the manual... And I don't know way for 'join_*_as' to be just "ID"

[eluser]rherriman[/eluser]
introvert:

I tried taking a similar approach to you a month or so ago, but realized that is not how things are meant to work with the nested sets plugin. It wants you to have a single root node per tree. And each tree has its own set of left/right values.

Naming the `root_id` field `tree_id` might have been more accurate, because that seems to be its true purpose. Make sense?

[eluser]rherriman[/eluser]
danijelb:

It would be easier to answer your question with more schema details. It's difficult to tell from your post what tables there are, and what fields they contain.

[eluser]danijelb[/eluser]
[quote author="rherriman" date="1303933915"]danijelb:

It would be easier to answer your question with more schema details. It's difficult to tell from your post what tables there are, and what fields they contain.[/quote]

Here it is:
http://i56.tinypic.com/14t3pyp.png

Greyed out tables are just here to show the rest of schema, but are not important for my question.

A connection between Pages and Content needs to be created to get be as close as the Desired result table (which I can get by classic SQL queries)...

Also, the connection (relationship) will need to be created dinamically (but I think I will be able to handle it, just first I need to figure out the way to create this type of relationship)...

This is a concept where all site's content can be nested whether it's a page, an article, image, category, or something else... Also, this way - multilingual content is much more easier to manipulate and I can have global comment system (which would store comments based on contents.id)... And it would be easy to incorporate new content types and they would get all benefits of this way of storing (they would get comments, multilinguality, nesting...)

But for now I just need to figure out how to (manually) create this (Page-Content) relationship...

[eluser]WanWizard[/eluser]
You didn't follow the rules on table and key naming, so you're making it extremely difficult on yourself.

If you had used 'page_id' instead of 'in_table_id', a simple $has_many = array('content') in the Page model, and a $has_one = array('page') in the Content model would be enough to establish the relationship.

Now you have to use advanced relationship definitions to deal with the non-standard in-table foreign key. Try this:

Code:

Class Page extends Datamapper
{
    public $has_many = array(
        'content' => array(
            'join_self_as' => 'in_table'
        )
    );
}

Code:

Class Content extends Datamapper
{
    public $has_one = array('page');
}

See http://datamapper.wanwizard.eu/pages/adv...tions.html on how to deal with the consequences of ignoring the usage rules...

As a refresh, see http://datamapper.wanwizard.eu/pages/database.html for table and column naming rules.

[eluser]danijelb[/eluser]
[quote author="WanWizard" date="1303945895"]You didn't follow the rules on table and key naming, so you're making it extremely difficult on yourself.

If you had used 'page_id' instead of 'in_table_id', a simple $has_many = array('content') in the Page model, and a $has_one = array('page') in the Content model would be enough to establish the relationship.

Now you have to use advanced relationship definitions to deal with the non-standard in-table foreign key. Try this:

Code:

Class Page extends Datamapper
{
    public $has_many = array(
        'content' => array(
            'join_self_as' => 'in_table'
        )
    );
}

Code:

Class Content extends Datamapper
{
    public $has_one = array('page');
}

See http://datamapper.wanwizard.eu/pages/adv...tions.html on how to deal with the consequences of ignoring the usage rules...

As a refresh, see http://datamapper.wanwizard.eu/pages/database.html for table and column naming rules.[/quote]
Thanks, I'll try that...

I know it would be easier if I followed conventions but it can't be Page_ID because that can also (in other cases) be an article, an image or other content type... So 'in_table_id' seemed most logical to me...

[eluser]nomie7[/eluser]
Hi I am new to this and would like to know how can I get grab information from both tables.

I have

Code:

CREATE TABLE `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_type` varchar(50) NOT NULL,
  `vehicle_make` varchar(50) NOT NULL,
  `vehicle_model` varchar(50) NOT NULL,
  `vehicle_year` varchar(50) NOT NULL,
  `vin` varchar(50) NOT NULL,
  `registered_state` varchar(10) NOT NULL,
  `license_plate` varchar(20) NOT NULL,
  `insurrance_policy` varchar(50) NOT NULL,
  PRIMARY KEY(`id`)
)
ENGINE=INNODB;

CREATE TABLE `drivers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `ss_no` varchar(50) NOT NULL,
  `address` varchar(100) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(10) NOT NULL,
  `zip_code` int(5) NOT NULL,
  `cell_phone` varchar(50) NOT NULL,
  `home_phone` varchar(50),
  `dl_no` varchar(50) NOT NULL,
  `dl_state` varchar(10) NOT NULL,
  `dl_exp` date NOT NULL,
  `dl_2_no` varchar(50) NOT NULL,
  `dl_2_state` varchar(10) NOT NULL,
  `dl_2_exp` date NOT NULL,
  `vehicle_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY(`id`),
  CONSTRAINT `Ref_01` FOREIGN KEY (`vehicle_id`)
    REFERENCES `vehicles`(`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE=INNODB;

SET FOREIGN_KEY_CHECKS=1;

as you can see every driver has a car associated with him. How can can query every driver and every car that is associated with him in an object.

I can get all the drivers using this.

Code:

$d = new Driver();
$data['driver'] = $d->get();

In my model for driver has

Code:

var $has_one = array('vehicle');

I want to get all the records in

Code:

$data['driver']

[eluser]WanWizard[/eluser]
@Nomie7,

To avoid any relationship issues, always define the relationship both ways. So your vehicle model should contain

Code:

public $has_many = array('driver');

When fetching relationships, Datamapper doesn't (automatically) fetch related objects, because it may have to create a lot of objects, which will make your application slow down, and use lots of memory. Only fetch objects when you want to manipulate them, or if you need to access relations.

You can use include_related() to add the fields of a second table to the result, like you would do with a normal join:

Code:

$d = new Driver();
$data['driver'] = $d->include_related('vehicle')->get();

Alternatively you can have vehicle objects created for every driver object by using

Code:

$d = new Driver();
$data['driver'] = $d->include_related('vehicle', '*', TRUE, TRUE)->get();

See http://datamapper.wanwizard.eu/pages/get....Selection for more information.

[eluser]nomie7[/eluser]
Thank you for your response. I understand now.

[eluser]Atas[/eluser]
Hi!, i am using this library time a go, but i can't do this relationship:

Tables:

Code:

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

CREATE TABLE `island` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `element` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `description` text,
  `link` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1;


//this table has the ids of all above tables
CREATE TABLE `category_element` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `element_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `island_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `element_id` (`element_id`),
  KEY `category_id` (`category_id`),
  KEY `island_id` (`island_id`),  
) ENGINE=InnoDB AUTO_INCREMENT=637 DEFAULT CHARSET=latin1;

As you can see every element has many categories in many islands.

Models:

Code:

class Category extends DataMapper {
    
    var $table = 'category';
    var $has_many = array('element') ;
    
    function __construct($id = NULL)
    {
        parent::__construct($id);
    }    
}

class Island extends DataMapper {
    
    var $table = 'island';
    
    var $has_many = array(        
       'element' => array(
            'class' => 'element',
            'other_field' => 'island',    
            'join_self_as' => 'island',            
            'join_other_as' => 'element',
            'join_table' => 'category_element',
        )        
    );
    
    function __construct($id = NULL)
    {
        parent::__construct($id);
    }
    
}

class Element extends DataMapper {
    
    var $table = 'element';
    
    var $has_many = array('category',        
        'island' => array(
            'class' => 'island',
            'other_field' => 'element',
            'join_self_as' => 'element',                  
            'join_other_as' => 'island',
            'join_table' => 'category_element',
        )        
    );
    
    function __construct($id = NULL)
    {
        parent::__construct($id);
    }
    
}

I tried to manage both relationships (category <-> element and island <-> element) from the same table (category_element), for that reason i had to define Manually the relationship "Island <-> element".

My problem is when i try to save both relationship at same time using "Save Multiple Relations $u->save($b->all);)".


In other words I need, for example do this:

Code:

INSERT INTO category_element (element_id, category_id, island_id) VALUES (1, 1 , 1);
INSERT INTO category_element (element_id, category_id, island_id) VALUES (1, 2 , 1);
INSERT INTO category_element (element_id, category_id, island_id) VALUES (1, 1 , 2);

I had an approach changing line 4832 in datamapper.php from this:

Code:

if ($query->num_rows() == 0)

to

Code:

if (1 == 1) //Super temporary hack

But, didn't work, i couldn't save both relationships in one register.

Anybody can help me?

Sorry my english!!