Hi Peeps - Can you help me please?
I’m using newquery to retrieve records across four tables:
User -> Story -> StorySentances ->Sentance
$c = $modx->newQuery('Users');
$c->select('Users.user_id, STY.sty_title, STYSTC.stystc_sentance_id, STC.stc_text');
$c->innerJoin('Story','STY','Users.user_id = STY.sty_owner_id');
$c->innerJoin('StorySentances','STYSTC','STY.sty_id = STYSTC.stystc_story_id');
$c->innerJoin('Sentance','STC','STYSTC.stystc_sentance_id = STC.stc_id');
$c->where(array(
'Users.user_id' => 43, ));
$stories = $modx->getCollection('Users',$c);
foreach ($stories as $s)
{
$output .= '<br />'.$s->get('user_id');
$output .= '<br />'.$s->get('sty_title');
$output .= '<br />'.$s->get('stystc_sentance_id');
$output .= '<br />'.$s->get('stc_text');
}
$output .= '<br /><br />'.$c->toSQL();
return $output;
It works but only gives me the very last record.
43
Personal space
24
When I give people enough space, I get to play with and talk to people, I make friends and have fun.
The sql generated is:
SELECT Users.user_id, STY.sty_title, STYSTC.stystc_sentance_id, STC.stc_text FROM `shl_users` AS `Users` JOIN `shl_story` `STY` ON Users.user_id = STY.sty_owner_id JOIN `shl_story_sentances` `STYSTC` ON STY.sty_id = STYSTC.stystc_story_id JOIN `shl_sentance` `STC` ON STYSTC.stystc_sentance_id = STC.stc_id WHERE `Users`.`user_id` = 43
Which gives me all the records if I run it directly against the tables through a client:
If I try the same query but using the following:
$users = $modx->getObject('Users',43);
$output .= $users->get('user_id');
$stys = $users->getMany('Story');
$output .= '<br /><br />Stories:';
foreach ( $stys as $s )
{
$output .= '<br />Storiy Title - '.$s->get('sty_title');
$styjoin = $s->getMany('StorySentances');
foreach ( $styjoin as $s )
{
$stystc = $s->getMany('Sentance');
foreach ($stystc as $c)
{
$output .= '<br />'.$c->get('stc_text');
}
}
$output .='<br />';
}
It gives me all the records as well
43
Stories:
Storiy Title - How To Listen Well
It's important to look at people and stop what I'm doing when they have something to tell me.
Sometimes grown-ups tell me very important things that I need to know.
If I don't look & listen I might miss something important and make the grown-ups angry.
I know it's wrong to keep doing what I'm doing when grown-ups want me to listen.
I will listen to grown-ups when they talk to me.
Storiy Title - Personal space
When I talk to people I need to give them their space and stay away from their faces.
When people come too close it makes other people uncomfortable.
Everybody needs space.
When I make people uncomfortable, they want to get away from me.
They might not want to ever talk to me again.
When I give people enough space, I get to play with and talk to people, I make friends and have fun.
So where am I going wrong with newQuery - what am I missing?
Any help would be much appreciated -
