DMZ 1.7.1 (DataMapper OverZealous Edition)

[eluser]Tom Vogt[/eluser]
I'm looking for a way to access multiple relationships.

Here's my problem:

I have users, assets and items.

A user has many assets and many items.
An asset belongs to one user, and can contain many items.
Every item belongs to one asset and one user.

I want to find the items belonging to a specific user and a specific asset. Or, in other words, my SQL query would be:

Code:

select * from items where user_id=123 and asset_id=456

For added complexity, I only know the user via the asset, so I'm processing assets, and the user is $asset->user.

None of the below:

Code:

$asset->user->item->get();
$asset->user->get()->item->get();

seem to work correctly.

[eluser]happydude[/eluser]
Somebody... pleeeeaaaaaseee.

[eluser]OverZealous[/eluser]
@introvert
Well, you are doing it wrong. If you read the docs, you'll see that get_sql wipes out the current query. If you want to see the generated query, then you should use check_last_query, as outlined in the troubleshooting page of the manual.

Everything that DMZ is doing is exactly what you asked it to do.


@Tom Vogt
You said that an asset only belongs to one user, correct? then why not simply do:

Code:

$asset->item->get();

That's all items associated with that asset. Since there's only one user per asset, there's no reason to make it more complicated.


@happydude
There's an entire section in the manual on join fields. Please spend some time reading that.

[eluser]introvert[/eluser]
[quote author="OverZealous" date="1279065998"]@introvert
Well, you are doing it wrong. If you read the docs, you'll see that get_sql wipes out the current query. If you want to see the generated query, then you should use check_last_query, as outlined in the troubleshooting page of the manual.

Everything that DMZ is doing is exactly what you asked it to do.
[/quote]

What am I doing wrong with the rest of the code so that I don't get the expected result?

check_last_query will return:

Code:

SELECT *
FROM (`groups`)
WHERE `updated` >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
OR
    `groups`.`running` = 1
ORDER BY `groups`.`updated` ASC

SELECT *
FROM (`groups`)
WHERE `updated` >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
OR
    `groups`.`running` = 1
ORDER BY `groups`.`updated` ASC

Which seems like a double query string.

[eluser]OverZealous[/eluser]
@introvert
If you'd read the docs, you'd see that check_last_query, by default, renders to the browser automatically. If you are also echoing it, you'll get the same output twice.

Obviously that would cause a database error if it tried to execute that.

[eluser]Atas[/eluser]
Hello !

I am reading about Self Relationships in the manual and i am doing exactly what it says, but it doesn't work.

I have the following tables.

CREATE TABLE `platform` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`name_url` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

CREATE TABLE `platforms_parentplatforms` (
`id` int(11) NOT NULL auto_increment,
`platform_id` int(11) NOT NULL,
`parentplatform_id` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

And this model:

Code:

class Platform extends DataMapper {

    var $table   = 'platform';
    var $has_one = array();
    /*var $has_many = array('game');*/

    var $has_many = array(
        'game',
        'parentplatform' => array(
            'class' => 'platform',
            'other_field' => 'platform'
        ),
        'platform' => array(
            'other_field' => 'parentplatform'
        )
    );


    /**
     * Constructor: calls parent constructor
     */
    function __construct($id = NULL){
        parent::__construct($id);
    }
}

The following error appears:


A Database Error Occurred

Code:

Error Number: 1146

Table 'dbname.parentplatforms_platforms' doesn't exist

SELECT `platform`.* FROM (`platform`) LEFT OUTER JOIN `parentplatforms_platforms` parentplatform_parentplatforms_platforms ON `platform`.`id` = `parentplatform_parentplatforms_platforms`.`platform_id` WHERE `parentplatform_parentplatforms_platforms`.`parentplatform_id` = 4

Any ideas ?

Thanks in advance

[eluser]OverZealous[/eluser]
@Atas
Alphabetical order on join table names: platform comes AFTER parent.

Also, please when asking for help, explain WHAT doesn't work, WHAT you tried, and any errors you might have received.

EDIT: My bad, you did include the error, but that error seems fairly damn obvious what's wrong to me.

[eluser]Atas[/eluser]
Thank you for your time @OverZealous, next time i am going to post more information about my issues, but is very difficult to me write in english :red: .

I understood the problem and solved modifying the table to this:

CREATE TABLE `platforms_relatedplatforms` (
`id` int(11) NOT NULL auto_increment,
`platform_id` int(11) NOT NULL,
`relatedplatform_id` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


Thanks!

[eluser]Tom Vogt[/eluser]
[quote author="OverZealous" date="1279065998"]
@Tom Vogt
You said that an asset only belongs to one user, correct? then why not simply do:

Code:

$asset->item->get();

That's all items associated with that asset. Since there's only one user per asset, there's no reason to make it more complicated.[/quote]

Yes, if you assume that $asset->user is always identical to $item->user - but it isn't. Items can be "borrowed", so to speak. They can be at an asset that does not belong to the item owner. Though in the particular query I want to make, the two are actually identical. I want to filter out the "foreign" items.

[eluser]introvert[/eluser]
[quote author="OverZealous" date="1279073654"]@introvert
If you'd read the docs, you'd see that check_last_query, by default, renders to the browser automatically. If you are also echoing it, you'll get the same output twice.

Obviously that would cause a database error if it tried to execute that.[/quote]

I don't get any error if I execute it with phpmyadmin 3.2.4. (MySQL client version: 5.1.37)
Can you please tell me which error do you get?
Do you maybe have any idea how I should restructure it to make it work? I have no idea what is going wrong.

Thanks a lot.