Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Josadec's avatar

Help with a query to conver in eloquent with relationship

I'm using Laravel 8 I have many relationships in the models, I want to create this query in Eloquent, could somebody help me?

SELECT D.name AS Dimensiones, SUM(A.value) AS Total_Dimensiones FROM answer_application ANP INNER JOIN answers A ON ANP.answer_id = A.id INNER JOIN questions Q ON Q.id = A.question_id INNER JOIN dimensions D ON D.id = Q.dimension_id GROUP BY D.name;

This is my models AnswerApplication Model class AnswerApplication extends Model { use HasFactory; protected $guarded = [];

public function exam ()
{
    return $this->belongsTo(Exam::class);
}

} Application Model class Application extends Model { use HasFactory; protected $guarded = [];

public function exam(){
    return $this->belongsTo(Exam::class);
}

public function answers (){
    return $this->belongsToMany(Answer::class)->withTimestamps();
}

public function question()
{
    return $this->belongsTo(Question::class);
}

} Question Model class Question extends Model { use HasFactory; protected $guarded = [];

public function answers ()
{
    return $this->hasMany(Answer::class);
}

public function exam ()
{
    return $this->belongsTo(Exam::class);
}

public function dimension()
{
    return $this->belongsTo(Dimension::class);
}

} Answer Model class Answer extends Model { use HasFactory; protected $guarded = [];

public function question()
{
    return $this->belongsTo(Question::class);
}

public function applications (){
    return $this->BelongsToMany(Application::class);
}

} Dimension Model class Dimension extends Model { use HasFactory; protected $guarded = [];

public function domain(){
    return $this->belongsTo(Domain::class);
}

} Domain Model class Domain extends Model { use HasFactory; protected $guarded = [];

public function category (){
    return $this->belongsTo(Category::class);
}

} Category Model class Category extends Model { use HasFactory; protected $guarded = [];

 public function domains()
{
    return $this->hasMany(Category::class);
}

}

0 likes
4 replies
sr57's avatar

st like this

DB::table("answer_application anp")
->join("answers a", function($join){
	$join->on("anp.answer_id", "=", "a.id");
})
->join("questions q", function($join){
	$join->on("q.id", "=", "a.question_id");
})
->join("dimensions d", function($join){
	$join->on("d.id", "=", "q.dimension_id");
})
->select("d.name as dimensiones", "sum (a.value) as total_dimensiones")
->groupBy("d.name")
->get();
Josadec's avatar

It doesn't work I have the error:

BadMethodCallException Call to undefined method Illuminate\Database\Query\Builder::innerJoin() (View: C:\xampp\htdocs\Norma035ASI\resources\views\dashboard.blade.php)

But If I remove the inner and just leave join I have this error:

Illuminate\Database\QueryException SQLSTATE[42S02]: Base table or view not found: 1146 Table 'norma035asi.answer_application anp' doesn't exist (SQL: select d.name as name_dimension, sum(a.value) as total_dimension from answer_application anp inner join answers a on anp.answer_id = a.id inner join questions q on q.id = a.questions_id inner join dimensions d on d.id = q.dimension_id group by d.name) (View: C:\xampp\htdocs\Norma035ASI\resources\views\dashboard.blade.php)

Table is really exists in the database

Livewire component { class Dashboard extends Component { public function render() { $sumatotal = Application::sum('result'); /* $dimensiones = Application::with('answers') ->Where('id','question_id') ->get(); */ $dimensiones = DB::table('answer_application anp') ->Join('answers a', function($join) { $join->on('anp.answer_id','=','a.id'); }) ->Join('questions q', function ($join){ $join->on('q.id','=','a.questions_id'); }) ->Join('dimensions d', function ($join) { $join->on('d.id','=','q.dimension_id'); }) ->select('d.name as name_dimension', 'sum(a.value) as total_dimension') ->groupBy('d.name') ->get() ;

    return view('livewire.dashboard.dashboard',compact('sumatotal','dimensiones'));
}

}

}

sr57's avatar

I remove the inner and just leave join

Right , I updated my answer.

norma035asi.answer_application

What's this name? What's the name of your table? no final s? I took my name from your OP.

Try also

DB::select("SELECT D.name AS Dimensiones, SUM(A.value) AS Total_Dimensiones FROM answer_application ANP INNER JOIN answers A ON ANP.answer_id = A.id INNER JOIN questions Q ON Q.id = A.question_id INNER JOIN dimensions D ON D.id = Q.dimension_id GROUP BY D.name"); 
Josadec's avatar
Josadec
OP
Best Answer
Level 1

@sr57 I find the problem with the Query I need to select the table answers in this case because I have a relationship

this was my solution

$answers = Answer::join('questions','answers.question_id','=','questions.id') ->join('answer_application','answer_application.answer_id','=','answers.id') ->select('questions.id AS question_id','questions.description AS question_name', 'answers.description AS answer_name', 'answers.value AS answer_value') ->orderby('question_id') ->get()

However SR57 thank you so much

Please or to participate in this conversation.