Laravel 8 issue with pagination and unique method

@tykus I have below tables with one to many relationship, I need first table row with the latest recorded row of the second table. Table 1

$table->increments('id')->unsigned();
          $table->string('Pmid')->unique();
          $table->string('Ministry');
          $table->string('province');
          $table->string('P_name');
          $table->bigInteger('Budget'); 
          $table->string('doer');
          $table->string('tamwil');
          $table->date('start_date');
          $table->date('end_date');
          $table->timestamp('Registered_at');

Table 2

    Schema::create('project__reports', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('added_fund');
            $table->bigInteger('final_fund');
            $table->bigInteger('added_days');
            $table->date('final_date');
            $table->string('prgs_prc');
            $table->string('prgs_prc_2');
            $table->string('status');
            $table->longtext('notes');
            $table->unsignedInteger('project_id')->nullable();
            $table->foreign('project_id')
                  ->references('id')->on('projects');
            $table->timestamp('Registered_date');
        });

@Aishan can you make an effort your format your code so your question is easier to understand?

@tykus How? I am new here and I am just copy pasting my code

@Aishan read the format your code link I gave you! And wrap your code in backticks like this

```
your code
```

@tykus I did, thanks I think now it’s understandable

@Aishan so your requirement... what is unique about I need first table row with the latest recorded row of the second table

@tykus There is one to many relationship between them, one is table 1 and many is table 2 when I query them I need only one row which combine the row of first table with unique ID of it joined with the last recorded row related to the that Id in table 2 .

@Aishan what defines the last recorded row; the ID or a date column?

@tykus I prefer ID but even the date registered is correct

@tykus

Table 1 
1   ka12   Dam 

Table 2 
1   1   %50 Continuous
2   1  %70  Continous 
3   1    %100 Stopped 

And I want this result from query:
1   ka12  Dam   3    1    %100    Stopped

@Snapey I think I have tried this before but still do not give me the unique result, but the row for the same ID will be repeated, I am new to laravel can u please help me of how to call the eloquent model function for the latest row in my query and inside controller?

@Aishan what models do you have?

honestly Im going to struggle as your code makes my eyes hurt

@Snapey 😂 why? I have two modes one for table 1 and the second for table 2 . Their names are Projects and Project_Reports respectively.

@Aishan models should be singular

columns should be lowercase

use user_id if related to User model

double underscore in table name?

@Snapey u are right, I’m not on my pc right now. I will post its picture later but I have two models one for projects and the other for the reports on each projects in which each time I need to retrieve the latest update of report

@Aishan if you want all of the attributes as siblings (i.e. Project instance will have project__reports properties), then the oneOfMany approach will not be for you. In that case, you can use a window function to join the appropriate latest record from the related table:

select 
    *
from (
SELECT 
    projects.*,  
    project__reports.status,  
    project__reports.notes, 
    project__reports.prgs_prc, 
    project__reports.prgs_prc_2, 
    project__reports.Registered_date, 
    project__reports.added_days, 
    project__reports.added_fund, 
    project__reports.final_fund, 
    project__reports.final_date,
    RANK() OVER(PARTITION BY `project__reports`.`project_id` ORDER BY `project__reports`.`ID` DESC) `rank`
    FROM projects
  LEFT JOIN `project__reports`
    ON `project__reports`.`project_id` = `projects`.`project_id`
) `projects_with_latest_report`
WHERE 
    `rank` = 1

Can you check this query returns the correct records in your SQL GUI? It can be translated to Query Builder syntax if correct.

@tykus So I need to convert it to laravel query builder, right!?

@Aishan first, did the query I gave you above work as expected?

@tykus I am not on my pc dude, I understand now how to test it. As soon as I did I will let u know about the result , thank you

@tykus cool it works, I just fixed some syntax errors .thank you .Now, How can I convert it to laravel code?

select * from ( SELECT projects.*, project__reports.status, project__reports.notes, project__reports.prgs_prc, project__reports.prgs_prc_2, project__reports.Registered_date, project__reports.added_days, project__reports.added_fund, project__reports.final_fund, project__reports.final_date, RANK() OVER(PARTITION BY project__reports.project_id ORDER BY project__reports.id DESC) rank FROM projects LEFT JOIN project__reports ON project__reports.project_id = projects.id ) projects_with_latest_report WHERE rank = 1;

@Aishan something like this:

$sub = Project::selectRaw('
    projects.*,
    project__reports.status,
    project__reports.notes,
    project__reports.prgs_prc,
    project__reports.prgs_prc_2,
    project__reports.Registered_date,
    project__reports.added_days,
    project__reports.added_fund,
    project__reports.final_fund,
    project__reports.final_date,
    RANK() OVER(PARTITION BY project__reports.project_id ORDER BY project__reports.id DESC) rank)
')->leftJoin('project__reports', 'project__reports.project_id', '=', 'projects.id');

$search_result = DB::fromSub($sub, 'projects_with_latest_report')->where('rank', 1)->get();

@tykus Call to undefined method Illuminate\Database\MySqlConnection::fromSub()

I am getting this error while applying it in laravel.