Question 274690 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel eloquent query BelngsToMany pivot table

I am trying to write an Eloquent query for a BelongsToMany relationship and where the results return only one distinct result based on the pivot table. See below for example.

Installer:

Id Name
1 Installer 1
2 Installer 2

State:

Id Name
1 State 1
2 State 2

Pivot:

Id State Id Installer Id Baseline Created At
1 1 1 1.0 2023-01-01
2 1 1 1.2 2023-01-02
3 2 1 1.5 2023-01-01

So I would like to run the query $state->installers()->get() where, using the example above, Installer 1 only returns ID 2 and 3. The reason I do not want ID 1 is because it is an older record by the created at column. Is the only way I can accomplish this is with a join? Not eloquent ORM?

Tags:

2

Answers


  1. 0

    you can apply a subquery to load the latest distinct installers for a state.

    for example like below

    $states = State::with(['installers' => function ($query) {
    $query->select(['installer_id', DB::raw('MAX(created_at) as latest_created_at')])
        ->groupBy('installer_id')
        ->orderBy('latest_created_at', 'desc');
}])->find($state_id);

    $states->installers will contain the latest distinct installer records for the specified state.

    Login or Signup to reply.
  2. 0

    I understand your question and relationship in Pivot Table and I found this solution:

    Try this raw SQL join with Eloquent.

    $state = State::find($stateId); // Replace $stateId with the ID of the state you want to retrieve data for.

$installers = Installer::select('installers.id', 'installers.name')
    ->join('installer_state', 'installers.id', '=', 'installer_state.installer_id')
    ->where('installer_state.state_id', $stateId)
    ->orderBy('installer_state.created_at', 'desc')
    ->distinct()
    ->get();

    This query will returns the data by $stateId where it is firstly check the condition for $stateId and get the data in orderBy created_date field with distinct value.

    I hope this will work for you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search