field ordering of compound index

[mcfog]

The order of field in compound index is crucial ( a,b index and b,a index is totally different thing) but now we just determine it by order of fields() declaration. In #203 we gain ability to declare multiple compound about same field, but we can't declare field order in index

e.g. we have a table (a,b,c,d), we cannot declare index b_c(b,c) and index a_c_b(a,c,b) at same time

IMO there's two way to solve this, first is change the index format in fields() declaration to something like

'index' => [true, 'b_c' => 1, 'a_c_b' => 3],

decide index group by key, order by value

or we can just separate a new indexes() method to declare these kind of indexes

    public static function indexes()
    {
        return [
            'b_c' => ['unique' => false, 'fields' => ['b', 'c']],
            'a_c_b' => ['unique' => false, 'fields' => ['a', 'c', 'b']],
        ];
    }

the second way make compound index looks more clearly but might involve more code, and interaction with the old 'index'=>'name' way

[nwoodthorpe-test1]

Removed

[mcfog]

FYI here's my monkey patch works on both 2.2 tag and master branch

extend Mapper

class DMapper extends Mapper
{
    public function resolver()
    {
        return new DResolver($this);
    }
}

extend Resolver to call Entity's alterTableSchema

class DResolver extends Resolver
{
    public function migrateCreateSchema()
    {
        $schema = parent::migrateCreateSchema();
        foreach ($schema->getTables() as $table) {
            $entityName = $this->mapper->entity();
            $entityName::alterTableSchema($table);
        }

        return $schema;
    }
}

extend Entity base class with new mapper and default empty alterTableSchema

class DEntity extends Entity
{
    protected static $mapper = DMapper::class;

    public static function alterTableSchema(Table $table)
    {

    }
}

finally, manually add index in Entity class

    public static function alterTableSchema(Table $table)
    {
        parent::alterTableSchema($table);

        $table->addIndex(['a', 'c', 'b'], 'a_c_b');
    }

[nwoodthorpe-test1]

No time for monkeying around.

Dont monkey patch bro 🙈

[splio-aleroux]

The second options seems to be the best one. I would like to see such changes too.

I'll try to open a PR for one of the two solutions if i have some time for that soon.