Data not getting saved in database using Laravel

Question

Im building blog app and im trying to store average rating in posts table with this function in PostController.php.

public function avgRating ($avgRating) {
    $posts=DB::statement('UPDATE posts p
    SET p.avgRating=
        (SELECT ROUND(AVG(r.rating), 0)
        FROM ratings r
        WHERE p.id=r.rateable_id)');
        $posts = Post::all();
  }

Every other function that i created is saving data in database except this one. I tried to run sql query by itself in phpmyadmin and it works, its storing the average rating of each post in that table. Can anyone explain how do i write that function correctly in Laravel?

Could you try update instead of statement to check if it's doing something? ```dd(DB::update()``` — Jorge Rodríguez, Aug 29 '19 at 11:31
So you're trying to update all the rows in the posts table, to store in the column posts.avgRating the average of the related ratings, is that right? — Jorge Rodríguez, Aug 29 '19 at 11:51
Your system is running in safe update mode. That means that you can't update unless you specify the rows to update. You try to run **SET SQL_SAFE_UPDATES = 0;** before your update. Can see this answer https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench — nacho, Aug 29 '19 at 11:54
@JorgeRodríguez my bad for writing that im not trying to update them all. YES i am trying to update them all. i have 3 tables: users, posts, and ratings, when user logs in and he rates a post, value of rating goes to ratings table and i want that after other users rate some posts that average of each post gets stored inside table posts, in column average rating. so i can see average rating for each post. — Niki, Aug 29 '19 at 12:10
@nacho In phpmyadmin says that sql safe updates is set to off — Niki, Aug 29 '19 at 13:13

score 0 · Answer 1 · answered Aug 29 '19 at 12:28

Ok, i am not an expert in laravel, but i think you could deactivate the safe mode before, and activate it back again when you finish. You could try to do it in this way:

public function avgRating ($avgRating) {
    DB::statement("SET SQL_SAFE_UPDATES = 0;");
    $posts=DB::statement('UPDATE posts p
    SET p.avgRating=
        (SELECT ROUND(AVG(r.rating), 0)
        FROM ratings r
        WHERE p.id=r.rateable_id)');
        $posts = Post::all();
    DB::statement("SET SQL_SAFE_UPDATES = 1;");
  }

Data not getting saved in database using Laravel

1 Answers1