Loop through query items before paginating

Hey guys i have an array like this

'A52D4F3A-1236-4E35-A452-B948E5A33521' : "Effective"
'77DC1BE4-9B38-4926-B5B9-B0850A592B8B' : "Effective"
'CEA780EB-E293-4649-8419-759F0BBC66D0' : "Effective"
'56CA0131-2169-4CE8-AF37-1C6E0560BE35' : "Effective"
'516F7FDB-6272-4A00-9846-96B1E8943C46' : "Effective"
'3476636B-E8D3-4FFA-B628-DBF1693A4BB5' : "Effective"
'B1EB10F7-180E-4683-9384-5F8F614FE487' : "Effective"
'1307C4E9-B096-495F-9516-B590B29E03C3' : "Effective"
'E67A0E52-F714-415B-9F1A-9232A0E15EF6' : "Effective"
'31DAF9BC-0A2A-4678-A2CA-FE0369D5C2D6' : "Effective"
'0ED51C0D-BE22-40FF-A091-822C95728F07' : "Effective"

These are ids of a table called ssv_background_informations. The problem is that i want to run the query and assign the scores based on their id and possibly paginate the results. But i just don't know how to do it. This is my query

$ssv =  Db::table('ssv_background_informations as ssv')
  ->leftJoin('lgas as l', 'l.id', '=', 'ssv.lga_id')
  ->leftJoin('users as u', 'ssv.user_sso_id', '=', 'u.id')
  ->leftJoin('users as u2', 'ssv.creator_user_id', '=', 'u2.id')
  ->leftJoin('schools', 'schools.id', '=', 'ssv.school_id')
  ->select(
    'ssv.*',
    'l.name as lga_name',
    DB::raw("CONCAT(u2.first_name,' ',u2.last_name)  AS creator_user_name"),
    'schools.name as school_name',
    DB::raw("CONCAT(u.first_name,' ',u.last_name)  AS user_sso_name")
  )
  ->whereIn('ssv.lga_id', $lgas)
  ->orderBy('ssv.created_at', 'Desc')
  ->paginate(request(config('app.pagination_query_param'), config('app.pagination_size')));

This is the data

        +"id": "AB36F85B-AFA1-43B9-BEB0-9DCF45333515"
        +"created_at": "2020-02-12 11:45:50"
        +"updated_at": "2020-02-12 11:45:50"
        +"deleted_at": null
        +"lga_id": "071D7D86-4229-35E4-B7E5-33DB226958F2"
        +"user_sso_id": "E46C51F6-E66A-4202-9AB3-AEF4A7532A44"
        +"creator_user_id": "66D70A1A-8ED8-47B0-B12D-2AAD7134DECD"
        +"school_id": "90079F9C-5CC9-4320-8EFB-4DCFF3415812"
        +"head_teacher": "E6470006-B248-405D-BAC1-FCD81A8E24D8"
        +"school_code": null
        +"date": "2020-02-12 00:00:00"
        +"teacher_present": "0"
        +"ssv_count": 5
        +"ssv_submitted": "4"
        +"planned_ht": "0"
        +"hours_spent": 4
        +"feedback_do_well": ""[CE2B151B-2B48-3803-94B6-797D43C41CC2, 2C034265-0DBE-39E8-8176-AE0399F90573, 9DEC42D7-D258-3B10-A5A9-99036560D07B]""
        +"feedback_needs_improve": "[2415D163-9AF7-39FB-A099-554A8E518F30]"
        +"feedback_further_needs_improve": "[0E815DC4-837F-31B6-AD09-D35F215F5000]"
        +"school_year_id": null
        +"school_term_id": null
        +"elv": null
        +"lat": null
        +"lng": null
        +"lga_name": "name"
        +"creator_user_name": "name"
        +"school_name": "school"
        +"user_sso_name": "name"

i want to add another field into the data like 'overall_score=>effective'. Can anyone help me with this please?

bwrigley

6 years ago

Level 5

Is there any reason you are asking the DB to do all the heavy lifting? Would you consider accessing the data and using Laravel to process the data to get what you need instead?

FallOutBoi

6 years ago

Level 1

Just client thing, it was written like this before i don't want to change too much stuff

kevinbui

6 years ago

Best Answer

Level 41

What about paginating the query to get the results, and then add an extra attribute to each of the model instance:

$results = DB::table(...)
    ->where(...)
    ->paginate()
    ->each(function ($record) {
        $record->setAttribute('score', 'some_score');
    })

Please or to participate in this conversation.