/** * Returns query object to retrieve list of recently viewed records by * module. * * @param SugarBean $seed Instance of current bean. * @param array $options Prepared options. * @return SugarQuery query to execute. */ protected function getRecentlyViewedQueryObject($seed, $options) { $currentUser = $this->getUserBean(); $query = new SugarQuery(); $query->from($seed); // FIXME: FRM-226, logic for these needs to be moved to SugarQuery // Since tracker relationships don't actually exist, we're gonna have to add a direct join $query->joinRaw(sprintf(" JOIN tracker ON tracker.item_id=%s.id AND tracker.module_name='%s' AND tracker.user_id='%s' ", $query->from->getTableName(), $query->from->module_name, $currentUser->id), array('alias' => 'tracker')); // we need to set the linkName to hack around tracker not having real relationships /* TODO think about how to fix this so we can be less restrictive to raw joins that don't have a relationship */ $query->join['tracker']->linkName = 'tracker'; $query->select(array('id', array('tracker.module_name', 'module_name'))); if (!empty($options['date'])) { $td = new SugarDateTime(); $td->modify($options['date']); $query->where()->queryAnd()->gte('tracker.date_modified', $td->asDb()); } foreach ($query->select()->select as $v) { $query->groupBy($v->table . '.' . $v->field); } $query->select()->fieldRaw('MAX(tracker.date_modified)', 'last_viewed_date'); return $query; }
protected static function addTrackerFilter(SugarQuery $q, SugarQuery_Builder_Where $where, $interval) { global $db; $td = new SugarDateTime(); $td->modify($interval); $min_date = $td->asDb(); // Have to do a subselect because MAX() and GROUP BY don't get along with // databases other than MySQL $q->joinRaw(" INNER JOIN ( SELECT t.item_id item_id, MAX(t.date_modified) track_max " . " FROM tracker t " . " WHERE t.module_name = '" . $db->quote($q->from->module_name) . "' " . " AND t.user_id = '" . $db->quote($GLOBALS['current_user']->id) . "' " . " AND t.date_modified >= " . $db->convert("'" . $min_date . "'", 'datetime') . " " . " GROUP BY t.item_id " . " ) tracker ON tracker.item_id = " . $q->from->getTableName() . ".id ", array('alias' => 'tracker')); // Now, if they want tracker records, so let's order it by the tracker date_modified $q->order_by = array(); $q->orderByRaw('tracker.track_max', 'DESC'); $q->distinct(false); }