Cancel and save?

Question

Yii

There is a column (active) in the table. It can only have one true, everything else must be false. That is, when one changes, all of them change as well. Can you please tell me how to implement this?
My way:
In the table model, add the setActive method:
make two queries in it, the first one will set everything to false

$resetFields = \Yii::$app->db->createCommand("UPDATE table SET active=:no")
            ->bindValue(':no', 0)
            ->execute();

The second, which will write true to the desired field:

$query = \Yii::$app->db->createCommand("UPDATE table SET true=:yes WHERE id=:id")
            ->bindValue(':id', $this->id)
            ->bindValue(':yes', 1)
            ->execute();

Answer 1

Why do you need 2 requests?

UPDATE `table`
SET `active` = IF(`id` = :id, 1, 0);

Answer 2

it would be nice if the DB specialists said that it works faster: update, order by + update, or select + update
otherwise you can sort by value and update only the first or last element, or just select and update it

Answer 3

Alternatively, you can create a table table_active_attribute with the structure

id
table_id
active

As a result, this new table will store one record, which through table_id will be associated with the active record from table. As a result, you will only need to manage one entry in the table_active_attribute table, rather than updating all entries in table. The disadvantage of this option is that if you want to use this attribute, then you need to use join

Answer 4

From my point of view, you'd better clarify your first request.
Add an index on the active field and make two queries to update only two records, which will significantly speed up the first query on a large amount of data (by the way, you didn’t say how big the table can be).
We remove the active one with the condition:

$resetFields = \Yii::$app->db->createCommand("UPDATE table SET active=:no WHERE active=:yes")
            ->bindValue(':no', 0)
            ->bindValue(':yes', 1)
            ->execute();