← All changes | src/Framework/Models/ModelQueryBuilder.php +98 -0 4.16.0 → 4.18.0 View file →

		@@ -4,9 +4,11 @@
4	4
5	5	use Give\Framework\Database\DB;
6	6	use Give\Framework\Exceptions\Primitives\InvalidArgumentException;
7	7	use Give\Framework\Models\Contracts\ModelCrud;
	8	+use Give\Framework\QueryBuilder\Clauses\Having;
8	9	use Give\Framework\QueryBuilder\Clauses\RawSQL;
	10	+use Give\Framework\QueryBuilder\Clauses\Select;
9	11	use Give\Framework\QueryBuilder\QueryBuilder;
10	12
11	13	/**
12	14	* @since 2.19.6
		@@ -34,8 +36,11 @@
34	36
35	37	/**
36	38	* Returns the number of rows returned by a query
37	39	*
	40	+ * @since 4.18.0 Honor an explicit column in grouped counts by summing the column's non-null values per group.
	41	+ * @since 4.18.0 Preserve SELECT aliases referenced by HAVING when counting a grouped query.
	42	+ * @since 4.18.0 Count the groups of a grouped query rather than the first group's rows.
38	43	* @since 2.24.0
39	44	*
40	45	* @param null|string $column
41	46	*/
		@@ -42,8 +47,16 @@
42	47	public function count($column = null): int
43	48	{
44	49	$column = ( ! $column || $column === '*') ? '1' : trim($column);
45	50
	51	+ /*
	52	+ * A grouped query returns one row per group and get_row() reads only the first of them,
	53	+ * so the number of groups is what the caller is actually asking for.
	54	+ */
	55	+ if ($this->groupByColumns) {
	56	+ return +DB::get_row($this->getGroupCountSQL($column))->count;
	57	+ }
	58	+
46	59	if ('1' === $column) {
47	60	$this->selects = [];
48	61	}
49	62	$this->selects[] = new RawSQL('SELECT COUNT(%1s) AS count', $column);
		@@ -150,6 +163,91 @@
150	163
151	164	return array_map(static function ($object) use ($model) {
152	165	return $model::fromQueryBuilderObject($object);
153	166	}, $results);
	167	+ }
	168	+
	169	+ /**
	170	+ * Wraps the grouped query so that its rows, one per group, are what gets counted. A
	171	+ * COUNT(DISTINCT ...) over the grouped columns would drop every group holding a NULL.
	172	+ *
	173	+ * An explicit column counts the column's non-null values, so each group contributes its
	174	+ * non-null count and the wrapper sums them instead of counting groups.
	175	+ *
	176	+ * The grouped columns are aliased because a derived table rejects duplicate column names, and
	177	+ * the ordering and paging are dropped because neither changes the number of groups. SELECT
	178	+ * entries whose aliases a HAVING clause references are kept, since the replaced select list
	179	+ * would otherwise leave HAVING pointing at an alias that no longer exists.
	180	+ *
	181	+ * @since 4.18.0
	182	+ *
	183	+ * @param string $column
	184	+ */
	185	+ private function getGroupCountSQL($column = null): string
	186	+ {
	187	+ $innerSelects = [];
	188	+
	189	+ if ($column && '1' !== $column) {
	190	+ $innerSelects[] = DB::prepare('COUNT(%1s) AS nonNullCount', $column);
	191	+ }
	192	+
	193	+ foreach ($this->groupByColumns as $index => $groupByColumn) {
	194	+ $innerSelects[] = "{$groupByColumn} AS groupedColumn{$index}";
	195	+ }
	196	+
	197	+ foreach ($this->getHavingReferencedSelects() as $select) {
	198	+ $innerSelects[] = $select;
	199	+ }
	200	+
	201	+ $this->selects = [new RawSQL('SELECT ' . implode(', ', $innerSelects))];
	202	+ $this->orderBys = [];
	203	+ $this->limit = null;
	204	+ $this->offset = null;
	205	+
	206	+ if ($column && '1' !== $column) {
	207	+ return "SELECT SUM(nonNullCount) AS count FROM ({$this->getSQL()}) AS groupedQuery";
	208	+ }
	209	+
	210	+ return "SELECT COUNT(*) AS count FROM ({$this->getSQL()}) AS groupedQuery";
	211	+ }
	212	+
	213	+ /**
	214	+ * Renders the SELECT entries whose aliases a HAVING clause references, since the grouped
	215	+ * select list that count() builds would otherwise leave HAVING pointing at a missing alias.
	216	+ *
	217	+ * @since 4.18.0
	218	+ *
	219	+ * @return string[]
	220	+ */
	221	+ private function getHavingReferencedSelects(): array
	222	+ {
	223	+ $referencedAliases = [];
	224	+
	225	+ foreach ($this->havings as $having) {
	226	+ if ($having instanceof Having) {
	227	+ $referencedAliases[] = $having->column;
	228	+ }
	229	+ }
	230	+
	231	+ if ( ! $referencedAliases) {
	232	+ return [];
	233	+ }
	234	+
	235	+ $selects = [];
	236	+
	237	+ foreach ($this->selects as $select) {
	238	+ if ($select instanceof Select) {
	239	+ if (in_array($select->alias, $referencedAliases, true)) {
	240	+ $selects[] = DB::prepare('%1s AS %2s', $select->column, $select->alias);
	241	+ }
	242	+ } elseif ($select instanceof RawSQL) {
	243	+ if (preg_match('/\s+AS\s+([^\s,]+)\s*$/i', $select->sql, $matches) &&
	244	+ in_array(trim($matches[1], '`\'"'), $referencedAliases, true)
	245	+ ) {
	246	+ $selects[] = $select->sql;
	247	+ }
	248	+ }
	249	+ }
	250	+
	251	+ return $selects;
154	252	}
155	253	}