Level 75
You can find the answer in the documentation.
Dec 26, 2022
9
Level 4
Relational search filter
I have 3 tables "courses", "levels", and a pivot "course_level". The levels table has data like "Basic", "Intermediate", "Advanced"
Course model has this relation
public function level()
{
return $this->belongsToMany(Level::class);
}
The level mode has this relation
public function courses()
{
return $this->belongsToMany(Course::class);
}
How can I achieve this query by eloquent?
SELECT * from courses c
JOIN course_level cl
on cl.level_id = c.id
WHERE cl.level_id = 1 OR 2 OR 3;
Level 4
@MichalOravec is it possible by eloquent ORM?
Level 75
@feralam You have only 3 rows in levels table? If so:
$courses = Course::has('levels')->get();
Level 4
@MichalOravec I wrote the query this way and it works but getting the "created_at" column null
why getting the "created_at" column null?
Here $request->id is an array like [1, 2, 3, .....]
$course = DB::table('courses')
->join('course_level', 'course_level.level_id', '=', 'courses.id')
->whereIn('course_level.level_id', $request->id)
->get();
return $course;
Level 44
Think this is the way to write that query using eloquent instead of DB Facade (untested btw).
$levelIds = $request->id;
return Course::whereHas('level', function ($level) use ($levelIds){
$query->whereLevelId($levelIds);
})->get();
Level 4
@MiguelBarros $query is undefined.
Level 44
@feralam , sorry instead of function ($level) is function ($query)
$levelIds = $request->id;
return Course::whereHas('level', function ($query) use ($levelIds){
$query->whereLevelId($levelIds);
})->get();
Level 2
Before to use Eloquent, i use Views on MySQL. I get a error when i tried to use your example. I changed to:
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `new_view` AS
SELECT
`courses`.`id` AS `idCourse`,
`courses`.`course` AS `course`,
`course_level`.`id` AS `idLevel`
FROM
(`courses`
JOIN `course_level` ON ((`courses`.`id` = `course_level`.`course_id`)))
and i get this:
<table>
idCourse course idLevel
1 English 1
1 English 2
1 English 3
2 Mathematics 1
2 Mathematics 2
2 Mathematics 3
</table>
I suggest first study the MySQL , and then translate to Eloquent.
Level 1
try it:
return Course::whereHas('level', function ($query) use ($levelIds){
$query->whereLevelId($levelIds);
})->get(); ```
Please or to participate in this conversation.