I need to get these data from action_users table I have write a query in Mysql and it works fine but when I am trying to Convert SQL Statement into Laravel Eloquent i got this error
i am using laravel 8
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘gelecek_üyeliği_sistemi.action_users,users’ doesn’t exist (SQL: select users
.name
, users
.email
, sum(action_users
.point)
from action_users,users
where users
.id
= action_users.user_id group by users
)
here is my query in mysql and works fine in mysql
SELECT users.id, users.name, users.email, SUM(action_users.point) FROM action_users, users WHERE users.id=action_users.user_id group by users.id
here is my same query in the controller
$total = DB::table("action_users,users") ->select("users.name","users.email", "sum(action_users.point)") ->where("users.id", "=","action_users.user_id") ->groupBy("users") ->get(); dd($total);
here is action_users migration
$table->id(); $table->unSignedBigInteger('admin_id')->unsigned(); $table->unSignedBigInteger('user_id')->unsigned(); $table->unSignedBigInteger('action_id')->unsigned(); $table->foreign('admin_id') ->references('id')->on('admins'); $table->foreign('user_id') ->references('id')->on('users'); $table->foreign('action_id') ->references('id')->on('actions'); $table->integer('point');
Advertisement
Answer
your query must be changed using join and you must remove users in the first line of query:
$total = DB::table("action_users") ->join("users","users.id","action_users.user_id") ->select("users.name","users.email", "sum(action_users.point)") ->where("users.id", "=","action_users.user_id") ->groupBy("users.id") ->get();