GROUP BY settings¶
SetGroupBy¶
Prototype: function SetGroupBy ( $attribute, $func, $groupsort=“@group desc” )
Sets grouping attribute, function, and groups sorting mode; and enables grouping (as described in Grouping (clustering) search results).
$attribute
is a string that contains group-by attribute name.
$func
is a constant that chooses a function applied to the attribute
value in order to compute group-by key. $groupsort
is a clause that
controls how the groups will be sorted. Its syntax is similar to that
described in Sorting modes.
Grouping feature is very similar in nature to GROUP BY clause from SQL. Results produces by this function call are going to be the same as produced by the following pseudo code:
SELECT ... GROUP BY func(attribute) ORDER BY groupsort
Note that it’s $groupsort
that affects the order of matches in the
final result set. Sorting mode (see SetSortMode)
affect the ordering of matches within group, ie. what match will be
selected as the best one from the group. So you can for instance order
the groups by matches count and select the most relevant match within
each group at the same time.
Aggregate functions (AVG(), MIN(), MAX(), SUM()) are supported through SetSelect() API call when using GROUP BY.
Grouping on string attributes is supported, with respect to current collation.
SetGroupDistinct¶
Prototype: function SetGroupDistinct ( $attribute )
Sets attribute name for per-group distinct values count calculations. Only available for grouping queries.
$attribute
is a string that contains the attribute name. For each
group, all values of this attribute will be stored (as RAM limits
permit), then the amount of distinct values will be calculated and
returned to the client. This feature is similar to COUNT(DISTINCT)
clause in standard SQL; so these Manticore calls:
$cl->SetGroupBy ( "category", SPH_GROUPBY_ATTR, "@count desc" );
$cl->SetGroupDistinct ( "vendor" );
can be expressed using the following SQL clauses:
SELECT id, weight, all-attributes,
COUNT(DISTINCT vendor) AS @distinct,
COUNT(*) AS @count
FROM products
GROUP BY category
ORDER BY @count DESC
In the sample pseudo code shown just above, SetGroupDistinct()
call
corresponds to COUNT(DISINCT vendor)
clause only. GROUP BY
,
ORDER BY
, and COUNT(*)
clauses are all an equivalent of
SetGroupBy()
settings. Both queries will return one matching row for
each category. In addition to indexed attributes, matches will also
contain total per-category matches count, and the count of distinct
vendor IDs within each category.