StatisticsRepository::getFrequentSearchTermsFromStatisticsByFrequentSearchConfiguration() - Code Metrics - Inspection of "[BUGFIX] Fix sorting and counts in statistics modu..." - dkd-kaehm/ext-solr - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( 93b1b5...fcd324 )

by Rafael

created 2017-08-28 07:10 UTC

getFrequentSearchTermsFromStatisticsByFrequentSearchConfiguration() A

↳ Parent: StatisticsRepository

Complexity

Conditions	1
Paths	1

Size

Total Lines	16
Code Lines	12

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	16
rs	9.4285
c	0
b	0
f	0
cc	1
eloc	12
nc	1
nop	1

<?php declare(strict_types = 1);
namespace ApacheSolrForTypo3\Solr\Domain\Search\Statistics;

/***************************************************************
 *  Copyright notice
 *
 *  (c) 2016 Thomas Hohn <[email protected]>
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/

use ApacheSolrForTypo3\Solr\System\Records\AbstractRepository;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;

/**
 * Calculates the SearchQueryStatistics
 *
 * @author Thomas Hohn <[email protected]>
 */
class StatisticsRepository extends AbstractRepository
{
    /**
     * @var string
     */
    protected $table = 'tx_solr_statistics';

    /**
     * Fetches must popular search keys words from the table tx_solr_statistics
     *
     * @param int $rootPageId
     * @param int $days number of days of history to query
     * @param int $limit
     * @return mixed
     */
    public function getSearchStatistics(int $rootPageId, int $days = 30, $limit = 10)
    {
        $now = time();
        $timeStart = (int)($now - 86400 * $days); // 86400 seconds/day
        $limit = (int)$limit;

        return $this->getPreparedQueryBuilderForSearchStatisticsAndTopKeywords($rootPageId, $timeStart, $limit)
            ->execute()->fetchAll();
    }

    /**
     * Returns prepared QueryBuilder for two purposes:
     * for getSearchStatistics() and getTopKeyWordsWithOrWithoutHits() methods
     *
     * @param int $rootPageId
     * @param int $timeStart
     * @param int $limit
     * @return QueryBuilder
     */
    protected function getPreparedQueryBuilderForSearchStatisticsAndTopKeywords(int $rootPageId, int $timeStart, int $limit) : QueryBuilder
    {
        $countRows = $this->countByRootPageId($rootPageId);
        $queryBuilder = $this->getQueryBuilder();
        $statisticsQueryBuilder = $queryBuilder
            ->select('keywords')
            ->add('select', $queryBuilder->expr()->count('keywords', 'count'), true)
            ->add('select', $queryBuilder->expr()->avg('num_found', 'hits'), true)
            ->add('select', '(' . $queryBuilder->expr()->count('keywords') . ' * 100 / ' . $countRows . ') AS percent', true)
            ->from($this->table)
            ->andWhere(
                $queryBuilder->expr()->gt('tstamp', $timeStart),
                $queryBuilder->expr()->eq('root_pid', $rootPageId)
            )
            ->groupBy('keywords')
            ->orderBy('count', 'DESC')
            ->addOrderBy('hits', 'DESC')
            ->addOrderBy('keywords', 'ASC')
            ->setMaxResults($limit);

        return $statisticsQueryBuilder;
    }

    /**
     * Find Top search keywords with results
     *
     * @param int $rootPageId
     * @param int $days number of days of history to query
     * @param int $limit
     * @return array
     */
    public function getTopKeyWordsWithHits(int $rootPageId, int $days = 30, int $limit = 10) : array
    {
        return $this->getTopKeyWordsWithOrWithoutHits($rootPageId, $days, $limit, false);
    }

    /**
     * Find Top search keywords without results
     *
     * @param int $rootPageId
     * @param int $days number of days of history to query
     * @param int $limit
     * @return array
     */
    public function getTopKeyWordsWithoutHits(int $rootPageId, int $days = 30, int $limit = 10) : array
    {
        return $this->getTopKeyWordsWithOrWithoutHits($rootPageId, $days, $limit, true);
    }

    /**
     * Find Top search keywords with or without results
     *
     * @param int $rootPageId
     * @param int $days number of days of history to query
     * @param int $limit
     * @param bool $withoutHits
     * @return array
     */
    protected function getTopKeyWordsWithOrWithoutHits(int $rootPageId, int $days = 30, int $limit = 10, bool $withoutHits = false) : array
    {
        $now = time();
        $timeStart = $now - 86400 * $days; // 86400 seconds/day

        $queryBuilder = $this->getPreparedQueryBuilderForSearchStatisticsAndTopKeywords($rootPageId, $timeStart, $limit);
        // Check if we want without or with hits
        if ($withoutHits === true) {
            $queryBuilder->andWhere($queryBuilder->expr()->eq('num_found', 0));
        } else {
            $queryBuilder->andWhere($queryBuilder->expr()->gt('num_found', 0));
        }

        return $queryBuilder->execute()->fetchAll();
    }

    /**
     * Get number of queries over time
     *
     * @param int $rootPageId
     * @param int $days number of days of history to query
     * @param int $bucketSeconds Seconds per bucket
     * @return array [labels, data]
     */
    public function getQueriesOverTime(int $rootPageId, int $days = 30, int $bucketSeconds = 3600) : array
    {
        $now = time();
        $timeStart = $now - 86400 * intval($days); // 86400 seconds/day

        $queryBuilder = $this->getQueryBuilder();
        $result = $queryBuilder
            ->addSelectLiteral(
                'FLOOR(`tstamp`/' . $bucketSeconds . ') AS `bucket`',
                // @todo: Works only with MySQL. Add own column with Date type to prevent converting DateTime to Date
                'unix_timestamp(from_unixtime(`tstamp`, "%y-%m-%d")) AS `timestamp`',
                $queryBuilder->expr()->count('*', 'numQueries')
            )
            ->from($this->table)
            ->andWhere(
                $queryBuilder->expr()->gt('tstamp', $timeStart),
                $queryBuilder->expr()->eq('root_pid', $rootPageId)
            )
            ->groupBy('bucket', 'timestamp')
            ->orderBy('bucket', 'ASC')
            ->execute()->fetchAll();

        return $result;
    }

    /**
     * Regurns a result set by given plugin.tx_solr.search.frequentSearches.select configuration.
     *
     * @param array $frequentSearchConfiguration
     * @return array Array of frequent search terms, keys are the terms, values are hits
     */
    public function getFrequentSearchTermsFromStatisticsByFrequentSearchConfiguration(array $frequentSearchConfiguration) : array
    {
        $queryBuilder = $this->getQueryBuilder();
        $resultSet = $queryBuilder
            ->addSelectLiteral(
                $frequentSearchConfiguration['select.']['SELECT']
            )
            ->from($frequentSearchConfiguration['select.']['FROM'])
            ->add('where', $frequentSearchConfiguration['select.']['ADD_WHERE'], true)
            ->add('groupBy', $frequentSearchConfiguration['select.']['GROUP_BY'], true)
            ->add('orderBy', $frequentSearchConfiguration['select.']['ORDER_BY'])
            ->setMaxResults((int)$frequentSearchConfiguration['limit'])
            ->execute()->fetchAll();

        return $resultSet;
    }

    /**
     * Counts rows for specified site
     *
     * @param int $rootPageId sites root page id
     * @return int
     */
    public function countByRootPageId(int $rootPageId) : int
    {
        $queryBuilder = $this->getQueryBuilder();
        $numberRows = $this->getQueryBuilder()
            ->count('*')
            ->from($this->table)
            ->andWhere($queryBuilder->expr()->eq('root_pid', $rootPageId))
            ->execute()->fetchColumn(0);

        return (int)$numberRows;
    }

    /**
     * Persists statistics record
     *
     * @param array $statisticsRecord
     * @return void
     */
    public function saveStatisticsRecord(array $statisticsRecord)
    {
        $queryBuilder = $this->getQueryBuilder();
        $queryBuilder->insert($this->table)->values($statisticsRecord)->execute();
    }

    /**
     * Counts rows for specified site
     *
     * @param int $rootPageId
     * @return int
     */
    public function countByRootPageId(int $rootPageId): int
    {
        $queryBuilder = $this->getQueryBuilder();
        return (int)$this->getQueryBuilder()
            ->count('*')
            ->from($this->table)
            ->andWhere($queryBuilder->expr()->eq('root_pid', $rootPageId))
            ->execute()->fetchColumn(0);
    }
}


1			<?php declare(strict_types = 1);
2			namespace ApacheSolrForTypo3\Solr\Domain\Search\Statistics;
3
4			/***************************************************************
5			* Copyright notice
6			*
7			* (c) 2016 Thomas Hohn <[email protected]>
8			* All rights reserved
9			*
10			* This script is part of the TYPO3 project. The TYPO3 project is
11			* free software; you can redistribute it and/or modify
12			* it under the terms of the GNU General Public License as published by
13			* the Free Software Foundation; either version 2 of the License, or
14			* (at your option) any later version.
15			*
16			* The GNU General Public License can be found at
17			* http://www.gnu.org/copyleft/gpl.html.
18			*
19			* This script is distributed in the hope that it will be useful,
20			* but WITHOUT ANY WARRANTY; without even the implied warranty of
21			* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22			* GNU General Public License for more details.
23			*
24			* This copyright notice MUST APPEAR in all copies of the script!
25			***************************************************************/
26
27			use ApacheSolrForTypo3\Solr\System\Records\AbstractRepository;
28			use TYPO3\CMS\Core\Database\Query\QueryBuilder;
29
30			/**
31			* Calculates the SearchQueryStatistics
32			*
33			* @author Thomas Hohn <[email protected]>
34			*/
35			class StatisticsRepository extends AbstractRepository
36			{
37			/**
38			* @var string
39			*/
40			protected $table = 'tx_solr_statistics';
41
42			/**
43			* Fetches must popular search keys words from the table tx_solr_statistics
44			*
45			* @param int $rootPageId
46			* @param int $days number of days of history to query
47			* @param int $limit
48			* @return mixed
49			*/
50			public function getSearchStatistics(int $rootPageId, int $days = 30, $limit = 10)
51			{
52			$now = time();
53			$timeStart = (int)($now - 86400 * $days); // 86400 seconds/day
54			$limit = (int)$limit;
55
56			return $this->getPreparedQueryBuilderForSearchStatisticsAndTopKeywords($rootPageId, $timeStart, $limit)
57			->execute()->fetchAll();
58			}
59
60			/**
61			* Returns prepared QueryBuilder for two purposes:
62			* for getSearchStatistics() and getTopKeyWordsWithOrWithoutHits() methods
63			*
64			* @param int $rootPageId
65			* @param int $timeStart
66			* @param int $limit
67			* @return QueryBuilder
68			*/
69			protected function getPreparedQueryBuilderForSearchStatisticsAndTopKeywords(int $rootPageId, int $timeStart, int $limit) : QueryBuilder
70			{
71			$countRows = $this->countByRootPageId($rootPageId);
72			$queryBuilder = $this->getQueryBuilder();
73			$statisticsQueryBuilder = $queryBuilder
74			->select('keywords')
75			->add('select', $queryBuilder->expr()->count('keywords', 'count'), true)
76			->add('select', $queryBuilder->expr()->avg('num_found', 'hits'), true)
77			->add('select', '(' . $queryBuilder->expr()->count('keywords') . ' * 100 / ' . $countRows . ') AS percent', true)
78			->from($this->table)
79			->andWhere(
80			$queryBuilder->expr()->gt('tstamp', $timeStart),
81			$queryBuilder->expr()->eq('root_pid', $rootPageId)
82			)
83			->groupBy('keywords')
84			->orderBy('count', 'DESC')
85			->addOrderBy('hits', 'DESC')
86			->addOrderBy('keywords', 'ASC')
87			->setMaxResults($limit);
88
89			return $statisticsQueryBuilder;
90			}
91
92			/**
93			* Find Top search keywords with results
94			*
95			* @param int $rootPageId
96			* @param int $days number of days of history to query
97			* @param int $limit
98			* @return array
99			*/
100			public function getTopKeyWordsWithHits(int $rootPageId, int $days = 30, int $limit = 10) : array
101			{
102			return $this->getTopKeyWordsWithOrWithoutHits($rootPageId, $days, $limit, false);
103			}
104
105			/**
106			* Find Top search keywords without results
107			*
108			* @param int $rootPageId
109			* @param int $days number of days of history to query
110			* @param int $limit
111			* @return array
112			*/
113			public function getTopKeyWordsWithoutHits(int $rootPageId, int $days = 30, int $limit = 10) : array
114			{
115			return $this->getTopKeyWordsWithOrWithoutHits($rootPageId, $days, $limit, true);
116			}
117
118			/**
119			* Find Top search keywords with or without results
120			*
121			* @param int $rootPageId
122			* @param int $days number of days of history to query
123			* @param int $limit
124			* @param bool $withoutHits
125			* @return array
126			*/
127			protected function getTopKeyWordsWithOrWithoutHits(int $rootPageId, int $days = 30, int $limit = 10, bool $withoutHits = false) : array
128			{
129			$now = time();
130			$timeStart = $now - 86400 * $days; // 86400 seconds/day
131
132			$queryBuilder = $this->getPreparedQueryBuilderForSearchStatisticsAndTopKeywords($rootPageId, $timeStart, $limit);
133			// Check if we want without or with hits
134			if ($withoutHits === true) {
135			$queryBuilder->andWhere($queryBuilder->expr()->eq('num_found', 0));
136			} else {
137			$queryBuilder->andWhere($queryBuilder->expr()->gt('num_found', 0));
138			}
139
140			return $queryBuilder->execute()->fetchAll();
141			}
142
143			/**
144			* Get number of queries over time
145			*
146			* @param int $rootPageId
147			* @param int $days number of days of history to query
148			* @param int $bucketSeconds Seconds per bucket
149			* @return array [labels, data]
150			*/
151			public function getQueriesOverTime(int $rootPageId, int $days = 30, int $bucketSeconds = 3600) : array
152			{
153			$now = time();
154			$timeStart = $now - 86400 * intval($days); // 86400 seconds/day
155
156			$queryBuilder = $this->getQueryBuilder();
157			$result = $queryBuilder
158			->addSelectLiteral(
159			'FLOOR(`tstamp`/' . $bucketSeconds . ') AS `bucket`',
160			// @todo: Works only with MySQL. Add own column with Date type to prevent converting DateTime to Date
161			'unix_timestamp(from_unixtime(`tstamp`, "%y-%m-%d")) AS `timestamp`',
162			$queryBuilder->expr()->count('*', 'numQueries')
163			)
164			->from($this->table)
165			->andWhere(
166			$queryBuilder->expr()->gt('tstamp', $timeStart),
167			$queryBuilder->expr()->eq('root_pid', $rootPageId)
168			)
169			->groupBy('bucket', 'timestamp')
170			->orderBy('bucket', 'ASC')
171			->execute()->fetchAll();
172
173			return $result;
174			}
175
176			/**
177			* Regurns a result set by given plugin.tx_solr.search.frequentSearches.select configuration.
178			*
179			* @param array $frequentSearchConfiguration
180			* @return array Array of frequent search terms, keys are the terms, values are hits
181			*/
182			public function getFrequentSearchTermsFromStatisticsByFrequentSearchConfiguration(array $frequentSearchConfiguration) : array
183			{
184			$queryBuilder = $this->getQueryBuilder();
185			$resultSet = $queryBuilder
186			->addSelectLiteral(
187			$frequentSearchConfiguration['select.']['SELECT']
188			)
189			->from($frequentSearchConfiguration['select.']['FROM'])
190			->add('where', $frequentSearchConfiguration['select.']['ADD_WHERE'], true)
191			->add('groupBy', $frequentSearchConfiguration['select.']['GROUP_BY'], true)
192			->add('orderBy', $frequentSearchConfiguration['select.']['ORDER_BY'])
193			->setMaxResults((int)$frequentSearchConfiguration['limit'])
194			->execute()->fetchAll();
195
196			return $resultSet;
197			}
198
199			/**
200			* Counts rows for specified site
201			*
202			* @param int $rootPageId sites root page id
203			* @return int
204			*/
205			public function countByRootPageId(int $rootPageId) : int
206			{
207			$queryBuilder = $this->getQueryBuilder();
208			$numberRows = $this->getQueryBuilder()
209			->count('*')
210			->from($this->table)
211			->andWhere($queryBuilder->expr()->eq('root_pid', $rootPageId))
212			->execute()->fetchColumn(0);
213
214			return (int)$numberRows;
215			}
216
217			/**
218			* Persists statistics record
219			*
220			* @param array $statisticsRecord
221			* @return void
222			*/
223			public function saveStatisticsRecord(array $statisticsRecord)
224			{
225			$queryBuilder = $this->getQueryBuilder();
226			$queryBuilder->insert($this->table)->values($statisticsRecord)->execute();
227			}
228
229			/**
230			* Counts rows for specified site
231			*
232			* @param int $rootPageId
233			* @return int
234			*/
235			public function countByRootPageId(int $rootPageId): int
236			{
237			$queryBuilder = $this->getQueryBuilder();
238			return (int)$this->getQueryBuilder()
239			->count('*')
240			->from($this->table)
241			->andWhere($queryBuilder->expr()->eq('root_pid', $rootPageId))
242			->execute()->fetchColumn(0);
243			}
244			}
245

dkd-kaehm / ext-solr

Push — master ( 93b1b5...fcd324 )

getFrequentSearchTermsFromStatisticsByFrequentSearchConfiguration() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like