[Deprecated] DMZ 1.5.3 (DataMapper OverZealous Edition)

[eluser]Neeraj Kumar[/eluser]
Hi all!

well here's a quick question. first of all, lemme tell you guys that I am not good at joins.

here's the scenario:

I have three tables:

Peoples, Qualifications, Degrees

* Peoples "has_many" relationship with qualifications
* Qualifications "has_one" relationship with peoples

* Qualifications "has_one" relationship with degrees
* Degrees "has_many" relationship with qualifications

As you can see there is no direct relationship between a 'people' and a 'degree'

What I want is to get the degree associated with a person based on some parameter (based on his current qualification)

I am using DMZ and want to get the result in one query.
Till now what I was doing was: for each person I was using following code:

Code:

$person->qualification->where("position", "0")->get(); //reveals only one record
$person->qualification->degree->get();

How can I get the degree without writing more code by using DMZ including join tables capability... plz help!

[eluser]MeanStudios[/eluser]
Just 2 quick notes Phil. Thanks a ton for this!! Also, the other day when I downloaded your latest version of DMZ from your site, you kinda left in that testing code when saving a record. My client had a WTF moment and I had no idea where it was coming from heh. So, just to let you know, I haven't downloaded it since, but I'm guessing it might still be in your latest version .

Keep up the great work! You've saved me heaps of time...HEAPS!

[eluser]tolyx[/eluser]
Hi Phil

I have a DISTINCT query like this:

Code:

SELECT DISTINCT case_studies.sub_title, case_studies.url_sub_title FROM case_studies ORDER BY case_studies.sub_title ASC

When run directly on the db, it produces about 7 records. However, when I run this:

Code:

foreach ($sub_titles->all as $s)
            echo $s->sub_title;

Only the last, single, record is output. Any ideas where I'm going wrong?

Dan.

[eluser]NachoF[/eluser]
I have a question about database design.... Since Datamapper requires that each table have its own id field... how do you usually set up your many to many relaionship tables?? for instance... if you have employees and departments in a many to many relationship... I understand that the table departments_employees would have
id, employee_id, department_id but... which of those three fields would be primary key??... logic tells me it should be id but wouldnt that make the database corruptible?.. allowing the same employee and the same department to be related more than once??... I would usually have that table with only employee_id, department_id and both as primary keys and foreign keys (with InnoDB contraints)thus making it impossible for an employee/department to be related more than once..... I hope Im making myself clear here... thanks in advance.

[eluser]OverZealous[/eluser]
@Codemaster Snake
I'm a little confused by your description: Is your relationship this:

* People have many qualifications, qualifications have one person
* Qualifications have one degree, degrees have many qualifications

Or this:

* People have one qualification, qualifications have many people
* Qualifications have many degrees, degrees have one qualification

Or something else? You example doesn't make that clear. This really matters because you can only perform certain optimizations on $has_one relationships.

[eluser]OverZealous[/eluser]
[quote author="MeanStudios" date="1245948829"]Also, the other day when I downloaded your latest version of DMZ from your site, you kinda left in that testing code when saving a record.[/quote]

Well, hopefully it's gone now! I just did a scan of the source, and coudn't find anything, but I rebuilt and uploaded the ZIP just to be sure. Thanks for letting me know!

I'm glad it's helping save time!

[eluser]OverZealous[/eluser]
[quote author="tolyx" date="1245953813"]
Only the last, single, record is output. Any ideas where I'm going wrong?[/quote]

You always have to include the id column when using DataMapper. The id column is used to store the result set in the ->all array. What's most likely happening is your results are getting stored on top of each other.

This leads to a secret trick in DataMapper: You can actually get a specific item from a result by doing this:

Code:

// run your normal query
$object->get();
// get the item with the id of 4
$object4 = $object->all[4]; // not the 4th item, but $id=4

I don't know how useful it is, or even if this will stay this way forever, but it's an interesting side effect.

[eluser]OverZealous[/eluser]
[quote author="NachoF" date="1245956235"]which of those three fields would be primary key?[/quote]

In the DataMapper examples, the id is always the primary key.

DataMapper doesn't actually use the relationship table id column anywhere. It is only there for database design, to be used as the primary key. You are right that it is more correct to set up the two foreign key columns as your primary key, and have foreign key restrictions.

I haven't done a lot of research into the side effects of using the FK columns instead of using the id column. If you decide to try it let me know how it works. I know one area that it could be an issue is when dealing with multiple relationships to the same objects, because you join table looks like this:

Code:

posts_users
=====================================================================
id | created_post_id | creator_id | edited_post_id | editor_id | ...
---+-----------------+------------+----------------+-----------+-----
1  |               1 |          2 |           null |      null |
2  |            null |       null |              1 |         4 |

So your primary key would then be (created_post_id, creator_id, edited_post_id, editor_id).

In fact, if it proves that this works well, I would happily update the documentation and switch to using proper FK restrictions and multiple-column primary keys instead.

[eluser]NachoF[/eluser]
I remember I tried it some time ago and the problem I ran into was with the foreign keys....cause sometimes datamapper inserts something with null values and then automatically updates it(Im sure you know more about that than I do).. so if your database has foreign key constraints this will fail...the other thing is that if you have id, employee_id, department_id then all THREE would have to be primary keys cause otherwise how would you do auto_incremented on id if its not primary??... but if it IS primary then we have the same vulnerability cause you could do
deparments_employees
===================================
id | employee_id |department_id |
---+-----------------+-------------+
1 | 1 | 2 |
2 | 1 | 2 |

and again have the same employee related to the same department twice cause it has a different id.... what a pickle.

[eluser]NachoF[/eluser]
Basically the ideal (for me) would be to not have the id field on many-to-many join tables....do you have any ideas on how to get around this issue?