Unique index with condition

[sptGabriel]

Hello I didn't find anything in the documentation, I would like to know if it is possible to create a unique index with condition?

Something like this on the knex:

this.getKnex().raw(
              "create unique index employeePosition on employees(position, departament_id) where position <> 'tecnico'",
            ),

Entity:

export const enum Positions {
  TECNICO,
  GERENTE,
  DIRETOR,
}
@Entity({ tableName: 'employees' })
@Unique({ properties: ['position', 'departament_id'] })
export class Employee {
  @PrimaryKey()
  public readonly id: string;
  @Property()
  public matricula: string;
  @Property()
  public first_name: string;
  @Property()
  public last_name: string;
  @Property()
  public departament_id: string;
  @Enum()
  public position: Positions;
  @Property()
  public createdAt = new Date();
  @Property({ onUpdate: () => new Date() })
  public updatedAt = new Date();
  @Property()
  public deletedAt?: Date;
  
}

I would like to know if there is any possibility of putting any conditions in my unique index?

[B4nan]

Not really, apparently another feature that was PR'd to knex years ago but... Not merged, as always :/ This one even supports all possible drivers and have proper tests 🤷

knex/knex#2401

[sptGabriel]

so sad :/
@B4nan thank u

Could you ask me one more question?
Is it possible to make this query with mikro orm, without the need to use querybuilder?

code:

 const rowProducts = await this.db
      .select('*')
      .from<Product>(this.tableName)
      .whereIn('id', [id])
      .then(row => {
        return row.map(product => {
          return Product.toDomain(product);
        });
      });

[B4nan]

Why would you need a QB there? Its literally just a look up by PK, right?

[sptGabriel]

Why would you need a QB there? Its literally just a look up by PK, right?

I want to do without it,
something like repository.find (wherein: ids)

public byArray = async (ids: string[]): Promise<Product[] | undefined>  => {
    return await this.repository.find({$in:})
  }

[B4nan]

Yes, and I asked why do you think you need QB for that :] repo.find([1, 2, 3]) works just fine, as well as repo.find({ id: [1, 2, 3] }) orrepo.find({ id: { $id: [1, 2, 3] } }).

[ryall]

Looks like conditional indexes might finally be available in Knex since Oct:
knex/knex#4768

[johanneslumpe]

@B4nan Just now running into this exact issue, requiring partial indexes. Since knex now supports partial unique indexes by adding a where condition, do you think it's worth revisiting this and adding first class support for partial indexes in mikro?

I have so far solved this with a custom Index expression.

[B4nan]

You can use index expressions nowadays:

@Index({ name: 'custom_index_expr', expression: 'alter table `author` add index `custom_index_expr`(`title`)' })
@Property()
title!: string;

https://mikro-orm.io/docs/defining-entities#indexes

[johanneslumpe]

@B4nan thanks for the follow up - that’s exactly what I ended up doing :)