feat: group N by fields and groups sort supports

[cevin]

https://manual.manticoresearch.com/Searching/Grouping#Give-me-N-rows https://manual.manticoresearch.com/Searching/Grouping#Sorting-inside-a-group

usage

group N by column

use $builder->groupN(int $num) to enable group N by fields (effective when $num is greater than 0)

Products::search($keywords, fn($builder) => $builder->groupN(3)->groupBy('field-a', 'field-b'))
->get();

// select * from products where match (keywords) group 3 by fielda, fieldb

// normally group filter
Products::search($keywords, fn($builder) => $builder->groupBy('field-a', 'field-b'))
->get();

// select * from products where match (keywords) group by fielda, fieldb

groups sort

sorting in groups

Products::search($keywords, fn($builder) => $builder->groupOrderBy($column, $direction = 'asc'))
->get();

// select * from products where match (keywords) group 3 by fielda, fieldb

compatible with mysql mode only

[RomanStruk]

unable to confirm changes. because at the final stage all grouping methods https://manual.manticoresearch.com/Searching/Grouping#Grouping-search-results

[cevin]

The group n is a global syntax and affects the global group condition, once the n parameter is present it means that all group fields will apply to n rows.

The groupN method acts as a switch to indicate whether or not this global switch is turned on (syntax), and when it is turned on, it means that all group conditions will be returned as n rows.

Without groupN , it is a normal group query.

This is as expected.

[RomanStruk]

if ($query->groupN > 0) {
    return 'group ' . $query->groupN . 'by ' . $this->columnize($groups);
}

set default groupN 1 and if ($query->groupN > 1)

group 1 by does not make sense

[RomanStruk]

Grouping by MVA (multi-value attributes) will you implement?

[cevin]

Grouping by MVA (multi-value attributes) will you implement?

Maybe it's already implemented.

https://manual.manticoresearch.com/Searching/Grouping#Grouping-by-MVA-(multi-value-attributes)

// `column` it's a mva column
Products::search($keyword, fn ($builder) => $builder->selectRaw('id, groupby() gb')->groupBy('column'))->get();

will get sql:

select id, groupby() gb from table where match(keyword) group by column

select columns must include model primary key, for example id.

And dynamic column like count(*) will not be map to the model.

And, this scenario doesn't seem to fit Laravel.

Because, a mva column is an array<int, int> type, which is not well represented in Laravel Model itself.

// database
create table model ( ... ,    mva varchar(100),  ....)

// model definetion
class model { $fillable = ['mva'] ... }
toSearchableArray() { return [ .....,   'mva' => explode(',', $this->mva) ... ] } 
// sync to manticore will be ok

But, manticore groupby MVA will returned as splits result

For example:

database:

id	keyword	mva
1	....	1,2,3
2	....	1,4,5

group mva will returned as : select groupby() gb, count(*) c from model where match(keyword) group by mva	gb	count
1	2
2	1
3	1
4	1
5	1

Therefore, this scenario lends itself to the use of the raw() method at the developer's discretion

$raw = Products::search($keyword), function (Builder $builder) {
    return $builder->selectRaw('groupby() gb, count(*) c')
                ->groupBy('mva');
})->raw();

// $raw: ['hits' => [    ['gb'=>1, 'c'=>2]   ]]

[cevin]

if ($query->groupN > 0) {
    return 'group ' . $query->groupN . 'by ' . $this->columnize($groups);
}

set default groupN 1 and if ($query->groupN > 1)

group 1 by does not make sense

It's done.