New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

object graph nodes do not filter by foreign criteria #118

Open
spectrum48k opened this Issue Jun 26, 2017 · 2 comments

Comments

Projects
None yet
2 participants
@spectrum48k
Copy link

spectrum48k commented Jun 26, 2017

Summary

getObjectGraph() and getCollectionGraph() return results not filtered by foreign criteria and deepest related objects are are lost

Step to reproduce

<object  #class="prefixRoom" table="prefix_room" extends="xPDOSimpleObject">
<aggregate alias="substRoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank:!=":0}]]></criteria></aggregate>
$uid = 1; //some user id
$dataGraph = '{"RoomStudent" : {"Room" : {"substRoomTeacher" : {"Teacher" : {"RoomTeachers" : {}}}}}}';
$result = $modx->getObjectGraph('prefixStudent', $dataGraph, $uid)
->RoomStudent->Room->substRoomTeacher->Teacher;

Observed behavior

substRoomTeacher contains all membership records

Expected behavior

substRoomTeacher should contain one record in accordance to {"isstudent":0,"rank":0}

Environment

MODX revo 2.5.5, MySQL 5.7.18, Apache/2.4.18 (Ubuntu) Server

@wshawn

This comment has been minimized.

Copy link

wshawn commented Jun 30, 2017

Your code:
$result = $modx->getObjectGraph('prefixStudent', $dataGraph, $uid) ->RoomStudent->Room->substRoomTeacher->Teacher;

My first thought was that you are attempting to use a compound relation where it may not be supported. You may also have your relationships misdefined. Also the criteria is isstudent = false and rank not 0 (probably greater than 0). Your expected behavior is rank being equal to 0 (or true).

Your prefixRoom object has the relation so you should probably be coming in from prefixRoom->substRoomTeacher which should probably have a cardinality of many due to many substitute teachers possibly teaching in the same room. The would change it to $modx->getCollectionGraph() or $xpdo->getCollectionGraph(). Try the query without relying on the relation, then move on to refining the schema.

I have already built a school board management system with MODX/ xPDO, so I hope this will help you.

@spectrum48k

This comment has been minimized.

Copy link

spectrum48k commented Jul 4, 2017

OK, I will try to clarify and show more debug info.

schema fragment:

<object class="prefixRoom" table="prefix_room" extends="xPDOSimpleObject">
    <!--here some fields, indexes, validation-->

    <!--here some other relations-->

    <aggregate alias="RoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0}]]></criteria></aggregate>
    <aggregate alias="RoomTeachers" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="many"><criteria target="foreign"><![CDATA[{"isstudent":0}]]></criteria></aggregate>
    <aggregate alias="RoomStudent" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":1}]]></criteria></aggregate>
    <aggregate alias="RoomStudents" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="many"><criteria target="foreign"><![CDATA[{"isstudent":1}]]></criteria></aggregate>
    <aggregate alias="mainRoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank":0}]]></criteria></aggregate>
    <aggregate alias="substRoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank:!=":0}]]></criteria></aggregate>
    <aggregate alias="substRoomTeachers" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="many"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank:!=":0}]]></criteria></aggregate>
</object>

simple data fetch by hydrating fields

//just for example
$dataGraph = '{"RoomStudent" : {"Room" : {"substRoomTeacher" : {"Teacher" : {}}}}}';
$result = $modx->getObject($studentClass, 303);

$allRoomTeachers = $result->RoomStudent->Room->RoomTeachers; //then toArray() for each
$mainRoomTeacher = $result->RoomStudent->Room->mainRoomTeacher->toArray();
//here is another opened xPDO issue: If getOne() or getMany() hits the same criteria-hash-named cache file, you will get maximum nesting level error
$modx->cacheManager->refresh(array('db' => array())); //prevent fatal error when cached collection results
$substRoomTeacher = $result->RoomStudent->Room->substRoomTeacher->toArray();
$modx->cacheManager->refresh(array('db' => array())); //prevent fatal error when cached single object result
$substRoomTeachers = $result->RoomStudent->Room->substRoomTeachers; //then toArray() for each

results (it's OK):
_054

optimized data fetch with graphs

debug code fragment (only for substitutes for short):

$substsDataGraph = '{"RoomStudent" : {"Room" : {"substRoomTeachers" : {}}}}';
$substRoomTeachersGraph = $modx->getObjectGraph($studentClass, $substsDataGraph, 303);

results (too many sql result rows and missing deepest related data):

/* observed query -- no additional criteria*/
SELECT
  `substRoomTeachers`.`user_id`   AS `substRoomTeachers_user_id`,
  `substRoomTeachers`.`room_id`   AS `substRoomTeachers_room_id`,
  `substRoomTeachers`.`rank`      AS `substRoomTeachers_rank`,
  `substRoomTeachers`.`isstudent` AS `substRoomTeachers_isstudent`
FROM `tp_users` AS `PrefixStudent`
  LEFT JOIN `tp_prefix_room_user` `RoomStudent` ON `PrefixStudent`.`id` = `RoomStudent`.`user_id`
  LEFT JOIN `tp_prefix_room` `Room` ON `RoomStudent`.`room_id` = `Room`.`id`
  LEFT JOIN `tp_prefix_room_user` `substRoomTeachers` ON `Room`.`id` = `substRoomTeachers`.`room_id`
WHERE (`PrefixStudent`.`id` = 303 AND `PrefixStudent`.`class_key` = 'prefixStudent')
ORDER BY `PrefixStudent`.`id` ASC;

_056
_057

/* desired query example -- got it after some test fixes*/
SELECT

  `substRoomTeachers`.`user_id`   AS `substRoomTeachers_user_id`,
  `substRoomTeachers`.`room_id`   AS `substRoomTeachers_room_id`,
  `substRoomTeachers`.`rank`      AS `substRoomTeachers_rank`,
  `substRoomTeachers`.`isstudent` AS `substRoomTeachers_isstudent`
FROM `tp_users` AS `PrefixStudent`
  LEFT JOIN `tp_prefix_room_user` `RoomStudent` ON (`RoomStudent`.`isstudent` = '1' AND `PrefixStudent`.`id` = `RoomStudent`.`user_id`)
  LEFT JOIN `tp_prefix_room` `Room` ON `RoomStudent`.`room_id` = `Room`.`id`
  LEFT JOIN `tp_prefix_room_user` `substRoomTeachers`
    ON ((`substRoomTeachers`.`isstudent` = '0' AND `substRoomTeachers`.`rank` != '0') AND `Room`.`id` = `substRoomTeachers`.`room_id`)
WHERE (`PrefixStudent`.`id` = 303 AND `PrefixStudent`.`class_key` = 'prefixStudent')
ORDER BY `PrefixStudent`.`id` ASC;

I found at least three problems while I was looking for details:

  1. xPDOQuery::bindGraphNode() : foreign criteria not applies (if no local criteria)
  2. xPDOQuery::hydrateGraphNode() : using foreach in recursive call makes empty last related object as on screenshot
  3. xPDOQuery::hydrateGraphNode() : addOne() overwrites already linked related object (so can not get more than 1 item from array of substRoomTeachers)
  4. core/xpdo/xpdo.class.php:1065 log action will be never executed

after apply some of test fixes it seems to works correctly:

_058
_059
_060

  1. (found later) xPDOQuery::hydrateGraphNode()::addMany() "many" relations overwrite because recurcive calls get new instantiated object by _loadInstance(), but not a link to already existing one

I tried to apply more test fixes and it seems I got all the data but since I'm not an expert in xPDO and similar fixes I can not offer as pull requests I would like to discuss with the developers the problems and solutions that I found.

deep nesting of data previews:
image
image

for this test example db results are 177columns x 55 rows, 530 hydrateGraphNode() calls and ~500 fromArray() calls. I will try to test some fromArray() optimisation because fromArray() walks through every one field of 177 for every call in this test case

@opengeek do you mind paying attention to my problem and fixing it or showing me the right way please

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment