22278081669

Committed 22 Feb 2026 01:29PM UTC coverage: 50.0% (+1.3%) from 48.715%

Build # 22278081669

Build Type

Pull #1253

github

Committed by

web-flow

Commit Message

Merge 8da10fe4d into f39da7e53

Pull Request Pull Request #1253: Add NetteDatabaseDataSource for raw SQL queries via Nette\Database\Explorer

Run Details

95 of 124 new or added lines in 1 file covered. (76.61%)

15 existing lines in 1 file now uncovered.

1438 of 2876 relevant lines covered (50.0%)

0.5 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

76.61

/src/DataSource/NetteDatabaseDataSource.php

<?php declare(strict_types = 1);

namespace Contributte\Datagrid\DataSource;

use Contributte\Datagrid\Exception\DatagridDateTimeHelperException;
use Contributte\Datagrid\Filter\FilterDate;
use Contributte\Datagrid\Filter\FilterDateRange;
use Contributte\Datagrid\Filter\FilterMultiSelect;
use Contributte\Datagrid\Filter\FilterRange;
use Contributte\Datagrid\Filter\FilterSelect;
use Contributte\Datagrid\Filter\FilterText;
use Contributte\Datagrid\Utils\DateTimeHelper;
use Contributte\Datagrid\Utils\Sorting;
use Nette\Database\Explorer;
use Nette\Database\ResultSet;

/**
 * Data source for Nette\Database\Explorer with raw SQL queries.
 *
 * Usage:
 *   $dataSource = new NetteDatabaseDataSource($explorer, 'SELECT * FROM users WHERE active = ?', [1]);
 */
class NetteDatabaseDataSource extends FilterableDataSource implements IDataSource
{

        protected array $data = [];

        /** @var mixed[] */
        protected array $queryParameters;

        /** @var array<array{string, mixed[]}> */
        protected array $whereConditions = [];

        protected ?string $orderByClause = null;

        /**
         * @param mixed[] $params
         */
        public function __construct(
                protected Explorer $connection,
                protected string $sql,
                array $params = [],
        )
        {
                $this->queryParameters = $params;
        }

        public function getCount(): int
        {
                $sql = sprintf('SELECT COUNT(*) AS count FROM (%s) AS datagrid_count', $this->buildFilteredSql());

                $row = $this->query($sql, $this->buildParams())->fetch();

                return $row !== null ? (int) $row['count'] : 0;
        }

        /**
         * {@inheritDoc}
         */
        public function getData(): array
        {
                return $this->data !== []
                        ? $this->data
                        : $this->query($this->buildFinalSql(), $this->buildParams())->fetchAll();
        }

        /**
         * {@inheritDoc}
         */
        public function filterOne(array $condition): IDataSource
        {
                foreach ($condition as $column => $value) {
                        $this->addWhereCondition(sprintf('%s = ?', $column), [$value]);
                }

                return $this;
        }

        /**
         * @phpstan-param positive-int|0 $offset
         * @phpstan-param positive-int|0 $limit
         */
        public function limit(int $offset, int $limit): IDataSource
        {
                $sql = sprintf('%s LIMIT %d OFFSET %d', $this->buildFinalSql(), $limit, $offset);

                $this->data = $this->query($sql, $this->buildParams())->fetchAll();

                return $this;
        }

        public function sort(Sorting $sorting): IDataSource
        {
                if (is_callable($sorting->getSortCallback())) {
                        call_user_func(
                                $sorting->getSortCallback(),
                                $this->sql,
                                $sorting->getSort()
                        );

                        return $this;
                }

                $sort = $sorting->getSort();

                if ($sort !== []) {
                        $parts = [];

                        foreach ($sort as $column => $order) {
                                $parts[] = sprintf('%s %s', $column, $order);
                        }

                        $this->orderByClause = implode(', ', $parts);
                }

                return $this;
        }

        public function getDataSource(): Explorer
        {
                return $this->connection;
        }

        /**
         * Returns the current SQL query and its parameters.
         *
         * @return array{string, mixed[]}
         */
        public function getQuery(): array
        {
                return [$this->buildFinalSql(), $this->buildParams()];
        }

        /**
         * @param mixed[] $params
         */
        protected function addWhereCondition(string $sql, array $params = []): void
        {
                $this->whereConditions[] = [$sql, $params];
        }

        protected function buildFilteredSql(): string
        {
                if ($this->whereConditions === []) {
                        return $this->sql;
                }

                $whereParts = array_map(static fn (array $c): string => $c[0], $this->whereConditions);
                $whereClause = implode(' AND ', $whereParts);

                return sprintf('SELECT * FROM (%s) AS datagrid_base WHERE %s', $this->sql, $whereClause);
        }

        protected function buildFinalSql(): string
        {
                $sql = $this->buildFilteredSql();

                if ($this->orderByClause !== null) {
                        $sql .= sprintf(' ORDER BY %s', $this->orderByClause);
                }

                return $sql;
        }

        /**
         * @return mixed[]
         */
        protected function buildParams(): array
        {
                $params = $this->queryParameters;

                foreach ($this->whereConditions as [, $conditionParams]) {
                        $params = array_merge($params, $conditionParams);
                }

                return $params;
        }

        /**
         * @param mixed[] $params
         * @return ResultSet<mixed>
         */
        protected function query(string $sql, array $params = []): ResultSet
        {
                /** @phpstan-ignore argument.type */
                return $this->connection->query($sql, ...$params);
        }

        protected function applyFilterDate(FilterDate $filter): void
        {
                $conditions = $filter->getCondition();

                try {
                        $date = DateTimeHelper::tryConvertToDateTime($conditions[$filter->getColumn()], [$filter->getPhpFormat()]);

                        $this->addWhereCondition(
                                sprintf('DATE(%s) = ?', $filter->getColumn()),
                                [$date->format('Y-m-d')]
                        );
                } catch (DatagridDateTimeHelperException) {
                        // ignore the invalid filter value
                }
        }

        protected function applyFilterDateRange(FilterDateRange $filter): void
        {
                $conditions = $filter->getCondition();

                $valueFrom = $conditions[$filter->getColumn()]['from'];
                $valueTo = $conditions[$filter->getColumn()]['to'];

                if ($valueFrom) {
                        try {
                                $dateFrom = DateTimeHelper::tryConvertToDateTime($valueFrom, [$filter->getPhpFormat()]);
                                $dateFrom->setTime(0, 0, 0);

                                $this->addWhereCondition(
                                        sprintf('DATE(%s) >= ?', $filter->getColumn()),
                                        [$dateFrom->format('Y-m-d')]
                                );
                        } catch (DatagridDateTimeHelperException) {
                                // ignore the invalid filter value
                        }
                }

                if ($valueTo) {
                        try {
                                $dateTo = DateTimeHelper::tryConvertToDateTime($valueTo, [$filter->getPhpFormat()]);
                                $dateTo->setTime(23, 59, 59);

                                $this->addWhereCondition(
                                        sprintf('DATE(%s) <= ?', $filter->getColumn()),
                                        [$dateTo->format('Y-m-d')]
                                );
                        } catch (DatagridDateTimeHelperException) {
                                // ignore the invalid filter value
                        }
                }
        }

        protected function applyFilterRange(FilterRange $filter): void
        {
                $conditions = $filter->getCondition();

                $valueFrom = $conditions[$filter->getColumn()]['from'];
                $valueTo = $conditions[$filter->getColumn()]['to'];

                if ($valueFrom !== '') {
                        $this->addWhereCondition(
                                sprintf('%s >= ?', $filter->getColumn()),
                                [$valueFrom]
                        );
                }

                if ($valueTo !== '') {
                        $this->addWhereCondition(
                                sprintf('%s <= ?', $filter->getColumn()),
                                [$valueTo]
                        );
                }
        }

        protected function applyFilterText(FilterText $filter): void
        {
                $condition = $filter->getCondition();
                $operator = $filter->hasConjunctionSearch() ? 'AND' : 'OR';
                $or = [];
                $bigOrArgs = [];

                foreach ($condition as $column => $value) {
                        $like = '(';
                        $args = [];

                        if ($filter->isExactSearch()) {
                                $like .= sprintf('%s = ? %s ', $column, $operator);
                                $args[] = $value;
                        } else {
                                $words = $filter->hasSplitWordsSearch() === false ? [$value] : explode(' ', $value);

                                foreach ($words as $word) {
                                        $like .= sprintf('%s LIKE ? %s ', $column, $operator);
                                        $args[] = sprintf('%%%s%%', $word);
                                }
                        }

                        $like = substr($like, 0, strlen($like) - (strlen($operator) + 2)) . ')';

                        $or[] = $like;
                        $bigOrArgs = array_merge($bigOrArgs, $args);
                }

                if (count($or) > 1) {
                        $bigOr = '(' . implode(sprintf(' %s ', $operator), $or) . ')';
                        $this->addWhereCondition($bigOr, $bigOrArgs);
                } else {
                        $this->addWhereCondition((string) reset($or), $bigOrArgs);
                }
        }

        protected function applyFilterMultiSelect(FilterMultiSelect $filter): void
        {
                $condition = $filter->getCondition();
                $values = $condition[$filter->getColumn()];

                $placeholders = implode(', ', array_fill(0, count($values), '?'));
                $this->addWhereCondition(
                        sprintf('%s IN (%s)', $filter->getColumn(), $placeholders),
                        array_values($values)
                );
        }

        protected function applyFilterSelect(FilterSelect $filter): void
        {
                foreach ($filter->getCondition() as $column => $value) {
                        $this->addWhereCondition(sprintf('%s = ?', $column), [$value]);
                }
        }

}

1	<?php declare(strict_types = 1);
2
3	namespace Contributte\Datagrid\DataSource;
4
5	use Contributte\Datagrid\Exception\DatagridDateTimeHelperException;
6	use Contributte\Datagrid\Filter\FilterDate;
7	use Contributte\Datagrid\Filter\FilterDateRange;
8	use Contributte\Datagrid\Filter\FilterMultiSelect;
9	use Contributte\Datagrid\Filter\FilterRange;
10	use Contributte\Datagrid\Filter\FilterSelect;
11	use Contributte\Datagrid\Filter\FilterText;
12	use Contributte\Datagrid\Utils\DateTimeHelper;
13	use Contributte\Datagrid\Utils\Sorting;
14	use Nette\Database\Explorer;
15	use Nette\Database\ResultSet;
16
17	/**
18	* Data source for Nette\Database\Explorer with raw SQL queries.
19	*
20	* Usage:
21	* $dataSource = new NetteDatabaseDataSource($explorer, 'SELECT * FROM users WHERE active = ?', [1]);
22	*/
23	class NetteDatabaseDataSource extends FilterableDataSource implements IDataSource
24	{
25
26	protected array $data = [];
27
28	/** @var mixed[] */
29	protected array $queryParameters;
30
31	/** @var array<array{string, mixed[]}> */
32	protected array $whereConditions = [];
33
34	protected ?string $orderByClause = null;
35
36	/**
37	* @param mixed[] $params
38	*/
39	public function __construct(
40	protected Explorer $connection,	1✔
41	protected string $sql,	1✔
42	array $params = [],	1✔
43	)
44	{
45	$this->queryParameters = $params;	1✔
46	}	1✔
47
48	public function getCount(): int
49	{
50	$sql = sprintf('SELECT COUNT(*) AS count FROM (%s) AS datagrid_count', $this->buildFilteredSql());	1✔
51
52	$row = $this->query($sql, $this->buildParams())->fetch();	1✔
53
54	return $row !== null ? (int) $row['count'] : 0;	1✔
55	}
56
57	/**
58	* {@inheritDoc}
59	*/
60	public function getData(): array
61	{
62	return $this->data !== []	1✔
63	? $this->data	1✔
64	: $this->query($this->buildFinalSql(), $this->buildParams())->fetchAll();	1✔
65	}
66
67	/**
68	* {@inheritDoc}
69	*/
70	public function filterOne(array $condition): IDataSource	1✔
71	{
72	foreach ($condition as $column => $value) {	1✔
73	$this->addWhereCondition(sprintf('%s = ?', $column), [$value]);	1✔
74	}
75
76	return $this;	1✔
77	}
78
79	/**
80	* @phpstan-param positive-int\|0 $offset
81	* @phpstan-param positive-int\|0 $limit
82	*/
83	public function limit(int $offset, int $limit): IDataSource	1✔
84	{
85	$sql = sprintf('%s LIMIT %d OFFSET %d', $this->buildFinalSql(), $limit, $offset);	1✔
86
87	$this->data = $this->query($sql, $this->buildParams())->fetchAll();	1✔
88
89	return $this;	1✔
90	}
91
92	public function sort(Sorting $sorting): IDataSource	1✔
93	{
94	if (is_callable($sorting->getSortCallback())) {	1✔
NEW 95	call_user_func(	×
NEW 96	$sorting->getSortCallback(),	×
NEW 97	$this->sql,	×
NEW 98	$sorting->getSort()	×
99	);
100
NEW 101	return $this;	×
102	}
103
104	$sort = $sorting->getSort();	1✔
105
106	if ($sort !== []) {	1✔
107	$parts = [];	1✔
108
109	foreach ($sort as $column => $order) {	1✔
110	$parts[] = sprintf('%s %s', $column, $order);	1✔
111	}
112
113	$this->orderByClause = implode(', ', $parts);	1✔
114	}
115
116	return $this;	1✔
117	}
118
119	public function getDataSource(): Explorer
120	{
NEW 121	return $this->connection;	×
122	}
123
124	/**
125	* Returns the current SQL query and its parameters.
126	*
127	* @return array{string, mixed[]}
128	*/
129	public function getQuery(): array
130	{
131	return [$this->buildFinalSql(), $this->buildParams()];	1✔
132	}
133
134	/**
135	* @param mixed[] $params
136	*/
137	protected function addWhereCondition(string $sql, array $params = []): void	1✔
138	{
139	$this->whereConditions[] = [$sql, $params];	1✔
140	}	1✔
141
142	protected function buildFilteredSql(): string
143	{
144	if ($this->whereConditions === []) {	1✔
145	return $this->sql;	1✔
146	}
147
148	$whereParts = array_map(static fn (array $c): string => $c[0], $this->whereConditions);	1✔
149	$whereClause = implode(' AND ', $whereParts);	1✔
150
151	return sprintf('SELECT * FROM (%s) AS datagrid_base WHERE %s', $this->sql, $whereClause);	1✔
152	}
153
154	protected function buildFinalSql(): string
155	{
156	$sql = $this->buildFilteredSql();	1✔
157
158	if ($this->orderByClause !== null) {	1✔
159	$sql .= sprintf(' ORDER BY %s', $this->orderByClause);	1✔
160	}
161
162	return $sql;	1✔
163	}
164
165	/**
166	* @return mixed[]
167	*/
168	protected function buildParams(): array
169	{
170	$params = $this->queryParameters;	1✔
171
172	foreach ($this->whereConditions as [, $conditionParams]) {	1✔
173	$params = array_merge($params, $conditionParams);	1✔
174	}
175
176	return $params;	1✔
177	}
178
179	/**
180	* @param mixed[] $params
181	* @return ResultSet<mixed>
182	*/
183	protected function query(string $sql, array $params = []): ResultSet	1✔
184	{
185	/** @phpstan-ignore argument.type */
186	return $this->connection->query($sql, ...$params);	1✔
187	}
188
189	protected function applyFilterDate(FilterDate $filter): void
190	{
NEW 191	$conditions = $filter->getCondition();	×
192
193	try {
NEW 194	$date = DateTimeHelper::tryConvertToDateTime($conditions[$filter->getColumn()], [$filter->getPhpFormat()]);	×
195
NEW 196	$this->addWhereCondition(	×
NEW 197	sprintf('DATE(%s) = ?', $filter->getColumn()),	×
NEW 198	[$date->format('Y-m-d')]	×
199	);
NEW 200	} catch (DatagridDateTimeHelperException) {	×
201	// ignore the invalid filter value
202	}
203	}
204
205	protected function applyFilterDateRange(FilterDateRange $filter): void
206	{
NEW 207	$conditions = $filter->getCondition();	×
208
NEW 209	$valueFrom = $conditions[$filter->getColumn()]['from'];	×
NEW 210	$valueTo = $conditions[$filter->getColumn()]['to'];	×
211
NEW 212	if ($valueFrom) {	×
213	try {
NEW 214	$dateFrom = DateTimeHelper::tryConvertToDateTime($valueFrom, [$filter->getPhpFormat()]);	×
NEW 215	$dateFrom->setTime(0, 0, 0);	×
216
NEW 217	$this->addWhereCondition(	×
NEW 218	sprintf('DATE(%s) >= ?', $filter->getColumn()),	×
NEW 219	[$dateFrom->format('Y-m-d')]	×
220	);
NEW 221	} catch (DatagridDateTimeHelperException) {	×
222	// ignore the invalid filter value
223	}
224	}
225
NEW 226	if ($valueTo) {	×
227	try {
NEW 228	$dateTo = DateTimeHelper::tryConvertToDateTime($valueTo, [$filter->getPhpFormat()]);	×
NEW 229	$dateTo->setTime(23, 59, 59);	×
230
NEW 231	$this->addWhereCondition(	×
NEW 232	sprintf('DATE(%s) <= ?', $filter->getColumn()),	×
NEW 233	[$dateTo->format('Y-m-d')]	×
234	);
NEW 235	} catch (DatagridDateTimeHelperException) {	×
236	// ignore the invalid filter value
237	}
238	}
239	}
240
241	protected function applyFilterRange(FilterRange $filter): void	1✔
242	{
243	$conditions = $filter->getCondition();	1✔
244
245	$valueFrom = $conditions[$filter->getColumn()]['from'];	1✔
246	$valueTo = $conditions[$filter->getColumn()]['to'];	1✔
247
248	if ($valueFrom !== '') {	1✔
249	$this->addWhereCondition(	1✔
250	sprintf('%s >= ?', $filter->getColumn()),	1✔
251	[$valueFrom]	1✔
252	);
253	}
254
255	if ($valueTo !== '') {	1✔
256	$this->addWhereCondition(	1✔
257	sprintf('%s <= ?', $filter->getColumn()),	1✔
258	[$valueTo]	1✔
259	);
260	}
261	}	1✔
262
263	protected function applyFilterText(FilterText $filter): void	1✔
264	{
265	$condition = $filter->getCondition();	1✔
266	$operator = $filter->hasConjunctionSearch() ? 'AND' : 'OR';	1✔
267	$or = [];	1✔
268	$bigOrArgs = [];	1✔
269
270	foreach ($condition as $column => $value) {	1✔
271	$like = '(';	1✔
272	$args = [];	1✔
273
274	if ($filter->isExactSearch()) {	1✔
275	$like .= sprintf('%s = ? %s ', $column, $operator);	1✔
276	$args[] = $value;	1✔
277	} else {
278	$words = $filter->hasSplitWordsSearch() === false ? [$value] : explode(' ', $value);	1✔
279
280	foreach ($words as $word) {	1✔
281	$like .= sprintf('%s LIKE ? %s ', $column, $operator);	1✔
282	$args[] = sprintf('%%%s%%', $word);	1✔
283	}
284	}
285
286	$like = substr($like, 0, strlen($like) - (strlen($operator) + 2)) . ')';	1✔
287
288	$or[] = $like;	1✔
289	$bigOrArgs = array_merge($bigOrArgs, $args);	1✔
290	}
291
292	if (count($or) > 1) {	1✔
293	$bigOr = '(' . implode(sprintf(' %s ', $operator), $or) . ')';	1✔
294	$this->addWhereCondition($bigOr, $bigOrArgs);	1✔
295	} else {
296	$this->addWhereCondition((string) reset($or), $bigOrArgs);	1✔
297	}
298	}	1✔
299
300	protected function applyFilterMultiSelect(FilterMultiSelect $filter): void	1✔
301	{
302	$condition = $filter->getCondition();	1✔
303	$values = $condition[$filter->getColumn()];	1✔
304
305	$placeholders = implode(', ', array_fill(0, count($values), '?'));	1✔
306	$this->addWhereCondition(	1✔
307	sprintf('%s IN (%s)', $filter->getColumn(), $placeholders),	1✔
308	array_values($values)	1✔
309	);
310	}	1✔
311
312	protected function applyFilterSelect(FilterSelect $filter): void	1✔
313	{
314	foreach ($filter->getCondition() as $column => $value) {	1✔
315	$this->addWhereCondition(sprintf('%s = ?', $column), [$value]);	1✔
316	}
317	}	1✔
318
319	}

contributte / datagrid / 22278081669

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous