$c -> select() limitation?

37 Posts

2byte Reply #1, 15 years, 11 months ago

This line

“SUM(`Tracker`.`lastclick`-`Tracker`.`logintime`) AS `duration`,”

works directly as a query in mySQL, but breaks when added to

$c = $modx->newQuery('modUser');
$c->select('SUM(`Tracker`.`lastclick`-`Tracker`.`logintime`) AS `duration`');

Both lastclick and logintime are timestamp integers..

If I take out the bit of arithmetic (-`Tracker`.`logintime`), or remove the SUM keyword, it works, but unfortunately I cannot get them to play nice together.

I have isolated this line as breaking the whole query when run through modx, any suggestion?

Thanks..

37 Posts

2byte Reply #2, 15 years, 11 months ago

On further investigation it looks like the minus operator (-) is causing all the problems here..

Anyone know a way to escape this?

4,149 Posts

splittingred Reply #3, 15 years, 11 months ago

Can I see your whole query?

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

37 Posts

2byte Reply #4, 15 years, 11 months ago

$c = $modx->newQuery('modUser');
$c->innerJoin('modUserProfile','Profile','modUser.id = Profile.internalKey'); 
$c->innerJoin('modUserGroupMember','UserGroupMembers','modUser.id = UserGroupMembers.member');
$c->innerJoin('modUserGroup','UserGroup','`UserGroupMembers`.`user_group` = `UserGroup`.`id`');
$c->leftJoin('sesTracker','Tracker','modUser.id = Tracker.user');

$c->where(array(
	'active' => true,
	'UserGroupMembers.user_group:=' => '2', // specific user group
));
$c->andCondition("`Tracker`.`logintime` > '".$periodStart."'");
$c->andCondition("`Tracker`.`lastclick` < '".$periodEnd."'");
foreach($hideDoms as $hid){
	$c->andCondition("`Profile`.`email`  NOT LIKE '%".$hid."'");
}


$c->groupby('modUser.id'); 



	$c->select('
		`modUser`.`username`, 
		`Profile`.`fullname` as `fullname`, 
		`Profile`.`email` as `email`, 
		`Profile`.`internalKey` as `userid`, 
		`Profile`.`extended` as `extended`, 
		`UserGroup`.`name` AS `usergroup_name`, 
		SUM( `Tracker`.`lastclick` - `Tracker`.`logintime` ) AS `duration`,
		SUM(`Tracker`.`clicks`) AS `totalClicks`,
		COUNT(`Tracker`.`id`) AS `sessionCount`
	');
	
	/* this line is breaking it:
	 SUM(`Tracker`.`lastclick`-`Tracker`.`logintime`) AS `duration`,
	*/
	$c->sortby('duration','DESC');
	$c->limit($topNumber,0);
	
	$sessions = $modx->getCollection('modUser',$c);

37 Posts

2byte Reply #5, 15 years, 11 months ago

This is the query that works in MySQL which I want to run:

SELECT `modUser`.`username`, 
`Profile`.`fullname` as `fullname`, 
`Profile`.`email` as `email`, 
`Profile`.`internalKey` as `userid`, 
`Profile`.`extended` as `extended`, 
`UserGroup`.`name` AS `usergroup_name`, 
SUM(`Tracker`.`lastclick`-`Tracker`.`logintime`) AS `duration`, 
SUM(`Tracker`.`clicks`) AS `totalClicks`, 
COUNT(`Tracker`.`id`) AS `sessionCount` 
FROM `modx_users` AS `modUser` 
JOIN `modx_user_attributes` `Profile` ON modUser.id = Profile.internalKey 
JOIN `modx_member_groups` `UserGroupMembers` ON modUser.id = UserGroupMembers.member 
JOIN `modx_membergroup_names` `UserGroup` ON `UserGroupMembers`.`user_group` = `UserGroup`.`id` 
LEFT JOIN `modx_session_tracker` `Tracker` ON modUser.id = Tracker.user 
WHERE ( ( `modUser`.`active` = '1' AND `UserGroupMembers`.`user_group` = '2' ) 
AND `Tracker`.`logintime` > '1287752197' 
AND `Tracker`.`lastclick` < '1288356997' 
GROUP BY modUser.id 
ORDER BY duration DESC 
LIMIT 10

Maybe if there’s a way to access the database resource directly through modx I can bypass these xpdo.modx functions?

4,149 Posts

splittingred Reply #6, 15 years, 11 months ago

A few things:

1. You don’t need the initial ’modUser.id = Profile.internalKey’ in the first innerJoin, because MODx knows that relationship to the primary object. So your joins can be this:

$c->innerJoin('modUserProfile','Profile');
$c->innerJoin('modUserGroupMember','UserGroupMembers');
$c->innerJoin('modUserGroup','UserGroup','`UserGroupMembers`.`user_group` = `UserGroup`.`id`');
$c->leftJoin('sesTracker','Tracker','modUser.id = Tracker.user');

2. You *always* need to make sure to include the primary key in your queries, otherwise xPDO’s getCollection wont be able to handle it. So you need to add ’modUser.id’ to the select statement.
3. I’m not having any problems with a SUM( field - field ) clause.
4. To do better debugging, you should do this before your query:

$modx->setLogTarget('ECHO');

And after your getCollection call:

var_dump($c->toSql());

(or you can do the toSql before getCollection, but you need to call $c->prepare(); first then).

You might want to try simplifying the query to isolate the problem.

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

37 Posts

2byte Reply #7, 15 years, 11 months ago

Thanks so much Shaun, that is so helpful.
Changed the select query to include the primary key and it now works..
God bless ya grin