public function makeLogVisitsQueryString($idSite, $period, $date, $segment, $offset, $limit, $visitorId, $minTimestamp, $filterSortOrder)
{
// If no other filter, only look at the last 24 hours of stats
if (empty($visitorId) && empty($limit) && empty($offset) && empty($period) && empty($date)) {
$period = 'day';
$date = 'yesterdaySameTime';
}
list($whereClause, $bindIdSites) = $this->getIdSitesWhereClause($idSite);
list($whereBind, $where) = $this->getWhereClauseAndBind($whereClause, $bindIdSites, $idSite, $period, $date, $visitorId, $minTimestamp);
if (strtolower($filterSortOrder) !== 'asc') {
$filterSortOrder = 'DESC';
}
$segment = new Segment($segment, $idSite);
// Subquery to use the indexes for ORDER BY
$select = "log_visit.*";
$from = "log_visit";
$groupBy = false;
$limit = $limit >= 1 ? (int) $limit : 0;
$offset = $offset >= 1 ? (int) $offset : 0;
$orderBy = '';
if (count($bindIdSites) <= 1) {
$orderBy = 'idsite ' . $filterSortOrder . ', ';
}
$orderBy .= "visit_last_action_time " . $filterSortOrder;
$orderByParent = "sub.visit_last_action_time " . $filterSortOrder;
// this $innerLimit is a workaround (see https://github.com/piwik/piwik/issues/9200#issuecomment-183641293)
$innerLimit = $limit;
if (!$segment->isEmpty()) {
$innerLimit = $limit * 10;
}
$innerQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy, $innerLimit, $offset);
$bind = $innerQuery['bind'];
// Group by idvisit so that a given visit appears only once, useful when for example:
// 1) when a visitor converts 2 goals
// 2) when an Action Segment is used, the inner query will return one row per action, but we want one row per visit
$sql = "\n\t\t\tSELECT sub.* FROM (\n\t\t\t\t" . $innerQuery['sql'] . "\n\t\t\t) AS sub\n\t\t\tGROUP BY sub.idvisit\n\t\t\tORDER BY {$orderByParent}\n\t\t";
if ($limit) {
$sql .= sprintf("LIMIT %d \n", $limit);
}
return array($sql, $bind);
}