[Deprecated] DMZ 1.5.3 (DataMapper OverZealous Edition)

[eluser]OverZealous[/eluser]
[quote author="mcnux" date="1251380311"]
I think what you meant to say was as the model is called 'base_list', the ITFK on list_item should be called 'base_list_id'.[/quote]

No, I meant what I said. ;-P The name of the relationship defines the column name for in-table foreign keys. In other words, if your relationship looks like this:

Code:

$has_one = array(
    'something' => ...
//   ^^^^^^^^^-- The Relationship Key
);

Then the column name is going to be something_id, no matter what you call the model. The exception to this is if you override the column names by using join_self_as and join_other_as, which have to be specified on both models in a relationship. This is how I joined two models that were stored on the same table, and shared most of the same relationships, but had subtle differences. It becomes hard to maintain, however, and it's even harder to explain properly.

Hmm. Isn’t it more important to be able to name your models differently to your tables? Especially for single table inheritance, for example. I was expecting the model to map correctly because I’ve specified a different table name. I would have thought to specify different foreign keys than the table name would be a further modification.

I don't understand what you mean by this. :-S DMZ has a very explicit expectation for model/table names. The table name never comes into play when determining the name of any column names.

I don't know if this will help, but:
1) You also should never have two different classes with the same $model, because this will cause problems with the way DMZ caches object information in memory.
2) DMZ also expects a self-referencing dedicated join table to be of the form (but alphabetized):

Code:

plural($model1) . '_' .plural($model2)

Using significantly different table names and model names than what is expected for DMZ is just not a supported feature. I understand that you want it to read a certain way, but DMZ is designed to work with specially designed databases. The $table and $model keys are mostly there to provide fixes for English words that pluralize in a unique manner, and for certain table inheritance situations.

If you want better assistance, I suggest you put together some clear examples, showing example code & database structure, the expected result, and the result or errors you actually got. I would prefer if you could ZIP up the example content, and send it to me in a PM. I'd be happy to look at it then. :-)

[eluser]mcnux[/eluser]
[quote author="OverZealous" date="1251382009"]
No, I meant what I said. ;-P The name of the relationship defines the column name for in-table foreign keys. In other words, if your relationship looks like this:

Code:

$has_one = array(
    'something' => ...
//   ^^^^^^^^^-- The Relationship Key
);

Then the column name is going to be something_id, no matter what you call the model. The exception to this is if you override the column names by using join_self_as and join_other_as, which have to be specified on both models in a relationship. This is how I joined two models that were stored on the same table, and shared most of the same relationships, but had subtle differences. It becomes hard to maintain, however, and it's even harder to explain properly.
[/quote]
Yes of course sorry. This does allow you to customise foreign keys. I just need to get my head around it and then I think I might be able to solve the problem.

If you want better assistance, I suggest you put together some clear examples, showing example code & database structure, the expected result, and the result or errors you actually got. I would prefer if you could ZIP up the example content, and send it to me in a PM. I'd be happy to look at it then. :-)

Thanks I'll do this at some point, if needs be, after I've cracked advanced relationships.

[eluser]OverZealous[/eluser]
[quote author="mcnux" date="1251386803"]Thanks I'll do this at some point, if needs be, after I've cracked advanced relationships.[/quote]

Rockin'! Let me know if you have any other issues. Advanced Relationships is tricky, to say the least. I did my best when designing it to make it simple, but I still think it would be easier with an actual application (like a desktop app) to help set up the first time through. I already have something in mind. Someday...

[eluser]mcnux[/eluser]
Ok I'm going to try very hard to make this as clear as possible. I've been reading the Advanced Relationships section over and over and am getting there but I don't get how to get my setup to work.

Essentially what I want is list item's which can optionally have child list items. As this is a one to many self relationship, my table is as follows:

Code:

list_items
==========
id
list_item_id
whatever

I can then set 'list_item_id' FK on 'list_item' to specify that it has a parent list item. Obviously I can then find any child list items by finding 'list_item' where 'list_item_id' is my id.

That's the plan anyhoo. Here's my model:

Code:

class List_item extends DataMapper{
  var $has_one = array(
    'parent_list_item'=>array(
      'class'=>'list_item',
      'other_field'=>'list_item',
      'join_other_as'=>'list_item',
      'join_self_as'=>'list_item'
    )
  );
  var $has_many = array(
    'list_item'=>array(
      'class'=>'list_item',
      'other_field'=>'parent_list_item',
      'join_other_as'=>'list_item',
      'join_self_as'=>'list_item'
    )
  );
}

This is obviously wrong as the query executed when I ask for $listItemIDIsOne->list_item->count() is:

Code:

SELECT COUNT(*) AS `numrows`
FROM (`list_items`)
WHERE `id` = 1
AND `list_item_id` IS NOT NULL

But I don't know what I've done wrong.

[eluser]OverZealous[/eluser]
@mcnux

With your exact examples, it's much simpler than you think. First off, though, you have to declare the relationship differently on the parent side than the child side. In DMZ, all relationships have to be specified uniquely on both sides.

To get a One-to-Many self-referencing relationship, you can keep the table structure as you have it. The "parent" is going to be called 'list_item'. The child, for the sake of example, I'll call 'sub_item'.

Then your model looks like this:

Code:

class List_item extends DataMapper{
  var $has_one = array(
    'list_item'=>array(
      'other_field'=>'sub_item'
    )
  );
  var $has_many = array(
    'sub_item'=>array(
      'class'=>'list_item'
    )
  );
}

That's it! This is almost the exact same as the example code from the manual, under Self Relationships. ;-)

Usage Examples:

Code:

$parent = new List_Item();
$parent->get_by_id($parent_id);

$child = new List_Item();
$child-> ...

// Saving, will save $child at the same time:
$child->save_list_item($parent);
// OR this also works, if $child is already saved:
$parent->save_sub_item($child);

// Accessing:
$parent->sub_item->get();
foreach($parent->sub_item as $item) {
    ...
}

If you want to keep the name "parent_list_item", then I recommend you rename the column from 'list_item_id' to 'parent_list_item_id'. It's much, much easier that way. So, your table becomes

Code:

list_items
==========
id
parent_list_item_id
whatever

And your model becomes:

Code:

class List_item extends DataMapper{
  var $has_one = array(
    'parent_list_item'=>array(
      'class' => 'list_item',
      'other_field' => 'sub_item'
    )
  );
  var $has_many = array(
    'sub_item'=>array(
      'class' => 'list_item',
      'other_field' => 'parent_list_item'
    )
  );
}

Notice how the relationship is defined from both points of view, and they are symmetric.

Usage Examples:

Code:

// Setup parent and child as before...

// Saving, will save $child at the same time:
$child->save_parent_list_item($parent);
// OR this also works, if $child is already saved:
$parent->save_sub_item($child);

// Accessing:
$parent_of_child = $child->parent_list_item->get();

If you just have to have the table named the way you did, but you still want the term 'parent_list_item', then it gets more difficult, and you have to get creative with the 'join_self_as' and 'join_other_as' fields. Just remember that they cannot be the same values.

P.S.
I should point out that you really shouldn't ever need 'join_self/other_as' in just about any system. Especially if you are designing it new. So, avoid using them if at all possible. This is me speaking from experience! ;-)

[eluser]mcnux[/eluser]
First off, thanks for the very clear, detailed reply. I've never received such good support with any other open source product.

[quote author="OverZealous" date="1251397411"]

Code:

class List_item extends DataMapper{
  var $has_one = array(
    'list_item'=>array(
      'other_field'=>'sub_item'
    )
  );
  var $has_many = array(
    'sub_item'=>array(
      'class'=>'list_item'
    )
  );
}

That's it! This is almost the exact same as the example code from the manual, under Self Relationships. ;-)
[/quote]
Yeah I tried that first off but it wasn't working - I just tried it again with my model identical to your example and was still having a problem with the FKs. I've now found the problem - it fails for count() but is fine for get(). If I'd have just tried doing a get() first I would have found the problem a lot earlier. Doh!

$parentItem->sub_item->get():

Code:

SELECT `list_items`.*
FROM (`list_items`)
LEFT OUTER JOIN `list_items` as list_item_list_items ON `list_item_list_items`.`id` = `list_items`.`list_item_id`
WHERE `list_item_list_items`.`id` = 1

$parentItem->sub_item->count():

Code:

SELECT COUNT(*) AS `numrows`
FROM (`list_items`)
WHERE `id` = 1 AND `sub_item_id` IS NOT NULL

EDIT: also have the problem on delete when removing relationship:
$parentItem->delete():

Code:

UPDATE `list_items` SET `sub_item_id` = NULL WHERE `sub_item_id` = 1

Surely that's not me right? Please tell me it's not me!

[eluser]Mirage[/eluser]
Howdy Phil -

Thanks for 1.5.0. It just keeps getting better! Love implementation of the subclass prefix, but wanted to point out that it's not listed in your online documentation.

Cheers!

[eluser]Mirage[/eluser]
I'm finishing up a Tree extension implementing Modified Preorder Tree Traversal for DMZ. So far so good. Only a few methods to go, but I've run into a wall.

Basically, I wanted to avoid having the user define a self-relationship on the model and handle that in the extension. Is that possible?

Here is the original recipe from the mysql site:

Code:

// Find the immediate subordinates of a node
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'PORTABLE ELECTRONICS'
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

So basically that requires a join. Can this be written [in the extension] without having to define a self relationship on the model?

I actually was able to do something like this by adding addressing selects and sources right to ActiveRecord. But it feels hacky, wrong and expectedly fails, because DataMapper adds the tablename to the FROM statement:

Code:

function get_children($node) {
        // get children by gettin descendants with count and limit that to 1
        // De-reference some vars for readability
        $lcol = $this->options['left_colname'];
        $rcol = $this->options['right_colname'];
        
        // Isolate the dependants of the parent which are 1 level away
        $node->db->select('node.*, (COUNT(parent.id)-1) as depth');
        $node->db->from("{$node->table} as node, {$node->table} as parent");
        
        // Only descendants, parent node not included
        $node->db->where("node.{$lcol} > parent.{$lcol}");
        $node->db->where("node.{$lcol} < parent.{$rcol}");
      
        // Only for the parents part of the tree  
        $node->db->where("node.{$lcol} >", $node->$lcol );
        $node->db->where("node.{$rcol} <", $node->$rcol );
                
        $node->db->group_by("node.id");
        $node->db->having("depth =",1);
        $node->db->order_by("node.{$lcol}");
        
        // Run query
        $node->get();
   }

The above generates this query for me:

Code:

SELECT `node`.*, (COUNT(parent.id)-1) as depth FROM (`apn_pages` as node, `apn_pages` as parent, `apn_pages`) WHERE `node`.`lft` > parent.lft AND `node`.`lft` < parent.rgt AND `node`.`lft` > '8' AND `node`.`rgt` < '15' GROUP BY `node`.`id` HAVING `depth` = 1 ORDER BY `node`.`lft`

As you can see there an extra `apn_pages` in the table list and thus the query won't return anything. If I edit the SQL to run this maunally I do get the expected results:

Code:

SELECT `node`.*, (COUNT(parent.id)-1) as depth FROM (`apn_pages` as node, `apn_pages` as parent) WHERE `node`.`lft` > parent.lft AND `node`.`lft` < parent.rgt AND `node`.`lft` > '8' AND `node`.`rgt` < '15' GROUP BY `node`.`id` HAVING `depth` = 1 ORDER BY `node`.`lft`

Suggestions?

[eluser]OverZealous[/eluser]
[quote author="mcnux" date="1251400245"]Surely that's not me right? Please tell me it's not me![/quote]

Nope, it's not you. There is definitely a bug in the implementation of count() and delete(). I hadn't accounted for self-relationships. But, I just fixed them.

I'm testing the changes, and I'll try to get an updated version of DMZ out in the next couple of hours.

[eluser]OverZealous[/eluser]
DataMapper OverZealous Edition 1.5.1 Update

[quote author="OverZealous" date="1251211354"]Version 1.5.1:
• Fixed self-referencing, in-table foreign key bugs.
• You can now query without an ID
• Local extensions now override global extensions.
[/quote]

Download the Latest Version Here

View the change log and the upgrade process
Having issues? Please look through the Troubleshooting Guide & FAQs
View the Complete Manual

[/quote]