Yii2. What is the best practice to manage new messages?

Question

I have 3 DB tables: Projects, Votes and junction table project_votes. Each project can has many Votes. Is it a good idea to use a status column in the Votes table (with possible values "new" and "old") in order to distinquish incoming messages? In my app if a user opens his own project all new messages should became old. I do this with following code: Get new votes:

public function getNewVotes()
    {
        return $this->hasMany(Vote::className(), ['id' => 'vote_id'])->where(['like', 'status', 'new'])->via('projVotes');
    }

Set all votes to be old:

$model->newVotes = 'old'//  in Controller

public function setNewVotes($var)// in Model
    {
    foreach ($this->newVotes as $value) {
            $value->status = $var;
            $value->update();           
            }
    }

Or is there better way without foreach loop. For example using update with where clause?

Answer 1

I assume users can vote for projects. If so, why not make vote the junction table.

• project ( id name funds )
• vote ( id project_id user_id value ) // value being a means of expressing a vote
• user ( id name )

Otherwise, why not create a foreign key in vote instead of adding a junction table.

• project ( id name funds )
• vote ( id project_id value )

As for your question, I would add a boolean field 'read' in the vote table which defaults to false.

To get the unread votes for a project, add this to the Project model.

public function getUnreadVotes()
{
    return Vote::find()->where([
        'project_id' => $this->id, 
        'read' => false
    ])->all();
}

In order to set votes to read after fetching them, you can use updateAll().

Vote::updateAll(['read' => true], ['project_id' => $this->id]);