[Deprecated] DMZ 1.5.3 (DataMapper OverZealous Edition)

[eluser]ZeusChicago[/eluser]
[quote author="OverZealous" date="1253170734"]@ZeusChicago

First off, I see some (possibly unrelated) problems:

1) There's no reason to manually query the fa_user_id. Instead, use the supported where_related method:

Code:

$subModel->where_related('fa_user', 'id', $this->db_session->userdata('id'));

This is less error prone, and less risky.
[/quote]

note sure what you meant here. I was not querying the fa_user_id field, but mearly setting a where condition to only include records where the fa_user_id was set to the userid of the logged in user

$subscriptionWhere = ('fa_user_id =' .$this->db_session->userdata('id'));
$subModel->where($subscriptionWhere);

So I didnt change this.

[quote author="OverZealous" date="1253170734"]@ZeusChicago
2) join_related is deprecated. Although this won't cause a problem for now, it has been replaced with include_related
[/quote]

Gotcha, I changed to the following

$subModel->include_related('subscriptiontypes','*');

[quote author="OverZealous" date="1253170734"]@ZeusChicago
3) Your SubscriptionType model is incorrect. You always need to describe both sides of the relationship. It must have a $has_many that includes subscription. This is in the troubleshooting guide as things to look for. I have to assume your fa_user model is incorrect, as well.
[/quote]

I have corrected the subscriptiontype model to define both sides of the relationship. You were correct my fa_user model was incorrect as well. This however did not fix the problem either.


[quote author="OverZealous" date="1253170734"]@ZeusChicago
Beyond those problems, I can only assume you have a configuration error in CodeIgniter or DMZ, you are using a class to extend the DataMapper class that is causing conflicts, or something is happening in code outside the code you posted.

Please try the previously recommended hint of seeing what the database is returning, try stripping down your query to fundamental parts (say, just querying for all subscriptions with the given fa_user id), or some other basic debugging.

You also may want to look over the Change Log to see what features might have changed in the past.[/quote]

So I did some additional testing and found this corrected the issue (both the new error I was seeing + only seeing 1 record instead of 2. Either is a configuration issue on my site or a bug in your code. Since I can get around it im happy to move past it at the moment and try to come back later to figure out whats going on, but what I can tell you is this the following

Code:

$subModel->include_related('subscriptiontypes');

returns this error

Code:

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`) LEFT OUTER JOIN `subscriptiontypes` as subscriptiontypes ON `subscriptiontype' at line 1

SELECT `subscriptions`.*, `subscriptiontypes`.` AS subscriptiontype_ FROM (`subscriptions`) LEFT OUTER JOIN `subscriptiontypes` as subscriptiontypes ON `subscriptiontypes`.`id` = `subscriptions`.`subscriptiontype_id` WHERE `subscriptions`.`fa_user_id` =1 ORDER BY `subscriptions`.`startdatetime` DESC

the query thats being built thats the problem is this part
SELECT `subscriptions`.*, `subscriptiontypes`.` AS subscriptiontype_ FROM

Its missing the * and the closing '

If I however switch it to the following where I specify the fields I want returned, she works, and i might also add the query is now properly returning 2 records as I expected and not 1

Code:

$subModel->include_related('subscriptiontypes','displayname');

also if I use an array to include a list of fields I want return such as

Code:

$subModel->include_related('subscriptiontypes',array('displayname','name'));

Then the everything works as expected as well.

Finally, if I attempt to pass * such as

Code:

$subModel->include_related('subscriptiontypes','*');

I also get an sql statement that had an error in it which follows. Its similar to the first but not exactly the same

Code:

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM (`subscriptions`) LEFT OUTER JOIN `subscriptiontypes` as subscriptiontype' at line 1

SELECT `subscriptions`.*, `subscriptiontypes`.* AS subscriptiontype_* FROM (`subscriptions`) LEFT OUTER JOIN `subscriptiontypes` as subscriptiontypes ON `subscriptiontypes`.`id` = `subscriptions`.`subscriptiontype_id` WHERE `subscriptions`.`fa_user_id` =1 ORDER BY `subscriptions`.`startdatetime` DESC

As you can see the issue in the generated query has moved down a bit
SELECT `subscriptions`.*, `subscriptiontypes`.* AS subscriptiontype_* FROM

I am not a sql expert but I am pretty sure you cant alias (with AS) to a fieldname that has a * character in it. So subscriptiontype_* would be an invalid alias.

So in both cases, not passing a field list (which your include_related defaults to NULL) or passing * both produce a SQL query that is not valid.

Thank you for your quick response as they have lead me to a workable solution and in reality its better for me to name the fields I want instead of returning them all any (as im not using them so its just wasting memory return * lol.

Regards

Zeus

p.s. thanks for DMZ, other than today it has made my life much easier