Exemple #1
0
    }
    if ($filter['operator'] != 'like') {
        $filter['operator'] = 'like';
    }
    if (strlen($filter['value']) > 0) {
        $filterStr = 'WHERE al.' . $filter['property'] . '::text ' . $filter['operator'] . ' \'%' . $filter['value'] . '%\'';
    }
}
//Запрос для подсчета общего кол-ва записей для пейджинга
$sqltext = 'SELECT count(*) FROM (
              SELECT DISTINCT al.ip, ci.client, ci.os
              FROM access_log al
              LEFT JOIN client_info ci ON ci.ip = al.ip
              ' . $filterStr . '
            ) m';
$a = $dataAccess->execQuery($sqltext, array());
$sqltext = 'SELECT DISTINCT
                al.ip,
                ci.client,
                ci.os,
                (SELECT al1.url_from FROM access_log al1 WHERE al1.ip = al.ip ORDER BY al1.access_date ASC LIMIT 1)  AS url_from,
                (SELECT al2.url_to   FROM access_log al2 WHERE al2.ip = al.ip ORDER BY al2.access_date DESC LIMIT 1) AS url_to,
                count (DISTINCT al.url_to) AS url_count
            FROM access_log al
            LEFT JOIN client_info ci ON ci.ip = al.ip -- выбираем записи независимо есть ли для них браузер
            ' . $filterStr . '
            GROUP BY al.ip, ci.client, ci.os
            ORDER BY ' . $sortStr . '
            LIMIT $1 OFFSET $2';
$records = $dataAccess->execQuery($sqltext, array($limit, $start));
echo json_encode(array('records' => $records, 'totalCount' => $a[0]['count']));