Setting a primary row in a one to many relationship

Question

In some cases you may have a default or a primary row with a one to many relationship. Which is determined by a column like primary. And if you change the primary row you would set the one you want to 1 and the others to 0;

Here's an example showing my approach to how I solved this using Yii2.

Ex: we have two tables User and UserAddress

This is a function in the UserAddress class. It's job is to make the current UserAddress as the primary address for the User.

public function makePrimary() {
    $addresses = $this -> getUser() -> getUserAddresses() -> all();

    foreach ( $addresses As $address ) {
        $address -> is_primary = 0;
        $address -> save();
    }

    $this -> is_primary = 1;
    $this -> save();
}

Now my question, is this a good approach, that you normally do? If not, can you suggest a better approach to achieve the same result?

Answer 1

With your approach the performance takes a big hit as more the number of address rows, more the load on the server.

Consider you have 50 address rows for one user. As per your code the query execution is as follows

1. execute a query to get the user record while calling $this->getUser()
2. execute a query to get user addresses while calling getUserAddresses()->all()
3. loop through all the 50 address rows & execute 50 update queries to set is_primary as 0
4. execute a query to save the currently selected row as is_primary = 1

So in total the MySQL database queried for about 53 times.

But consider the following approach. I'm assuming that getUser() is related to getUserAddresses() via "user_id" and the name of the address table is "user_addresses"

public function makePrimary() {
    \Yii::$app->db->createCommand()->update('user_addresses', array(
        'is_primary'=>0,
    ), 'user_id=:id', array(':id'=>$this->user_id)); //Set is_primary as 0 for all addresses in one go

    $this -> is_primary = 1;
    $this -> save();
}

Here you can update is_primary as 0 for all the rows using one update query. So in total you just used 2 queries to perform the task

Answer 2

Thanks to Balaji Viswanath. His answer reminds me with another solution that can achieve the same. But I think is better.

Here's the code. (The functions is inside the UserAddress model class)

public function makePrimary() {
    self::updateAll(['is_primary' => 0], 'is_primary=1 AND user_ID=' . $this -> user_ID);
    // or just
    // self::updateAll(['is_primary' => 0], 'user_ID=' . $this -> user_ID);

    $this -> is_primary = 1;
    $this -> save();
}

With the above solution. I believe I have achieved the same purpose with only 2 queries as Balaji Viswanath does with solution he come up with.