Select values in array, separate and query the database laravel

Question

I have ids that are coming by array, I need to get them all and make selections in the bank using each one with different returns according to the selection. I am able to bring the numbers but it is not working to separate and use the select automatically according to the number of records in the array.

Below is the way I get the array:

Result Array $schools

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [school_type_id] => 2
            [external_id] => 
            [name] => School1
            [active] => 1
            [created_at] => 2020-02-21 23:42:11
            [updated_at] => 2020-02-21 23:42:11
            [deleted_at] => 
            [active_text] => Ativo

        )

    [1] => stdClass Object
        (
            [id] => 81
            [school_type_id] => 2
            [external_id] => 
            [name] => School2
            [active] => 1
            [created_at] => 2015-05-27 18:08:52
            [updated_at] => 2015-05-27 18:08:52
            [deleted_at] => 
            [active_text] => Ativo
           
        )

)

In the controller I'm using to bring only the values I want but I need to make a select with them and separate according to each one.

Controller.php

$schools = collect($api->schools);
    $info = $schools->pluck('id');

    for($i = 0;$i<count($info);$i++){

        $select = DB::table('sys_record')
        ->where('sys_record.id_step','<=',4)
        ->where('sys_users.schoolId','=',$info[$i])
        ->join('sys_users', 'sys_users.id_jovens', '=', 'sys_record.id_user')
        ->get();
        $information[] = count($select);  
        print_r($information);  
/*return Array
(
    [0] => 3
)
Array
(
    [0] => 3
    [1] => 1
)

        }*/

array 31 is being returned, I need 4

I believe it is returning a "3" and a "1" but you see them as "31" because print_r does not add any space or line break between results. Try to move the the print_r outside of the for and see what you get there — Cornel Raiu
– Cornel Raiu, Commented Jul 19, 2020 at 18:50
I just saw your edit. So you need to get the sum of all the entries in the array? if yes, after the for just do a array_sum( $informtation ) and you will get it — Cornel Raiu
– Cornel Raiu, Commented Jul 19, 2020 at 18:51

Cornel Raiu · Accepted Answer · 2020-07-19 18:59:52Z

1

From what I understand you want to retrieve the sum of the results.

For that your code should look like this:

for($i = 0;$i<count($info);$i++){

    $select = DB::table('sys_record')
    ->where('sys_record.id_step','<=',4)
    ->where('sys_users.schoolId','=',$info[$i])
    ->join('sys_users', 'sys_users.id_jovens', '=', 'sys_record.id_user')
    ->get();

    $information[] = count($select);
}

$total = array_sum($information);

print_r( $total );

Or a bit nicer:

$schools = collect($api->schools);
$info = $schools->pluck('id');
$information = 0;

foreach( $info as $k => $i){
    $results_count = DB::table('sys_record')
      ->where('sys_record.id_step','<=',4)
      ->where('sys_users.schoolId','=',$info[$i])
      ->join('sys_users', 'sys_users.id_jovens', '=', 'sys_record.id_user')
      ->count();

    $information += $results_count;
}

print_r( $information );

EDIT Or even nicer ( not sure if this works, but you get the idea ). This will only run one query for all schools instead of running one separate query for each school:

$schools = collect($api->schools);
$info = $schools->pluck('id');

$results_count = DB::table('sys_record')
      ->where('sys_record.id_step','<=',4)
      ->whereIn('sys_users.schoolId', $info)
      ->join('sys_users', 'sys_users.id_jovens', '=', 'sys_record.id_user')
      ->count();

print_r( $results_count );

edited Jul 19, 2020 at 18:59

answered Jul 19, 2020 at 18:53

Cornel Raiu

3,0693 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cornel Raiu Over a year ago

@testCode check out the latest edit. It might optimize your code even more

Madhur Bhaiya · Accepted Answer · 2020-07-19 07:25:20Z

1

If you only need to get id values out in a separate array/collection, you can try the pluck method instead of the for loop:


// Get schools as a Collection
$schools = collect($api->schools);

// Pluck out all the 'id' key values
$info = $schools->pluck('id');

answered Jul 19, 2020 at 7:25

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

1 Comment

testCode Over a year ago

when I count this value it returns me 31 where I put it in an array but how do I get 4 instead of 31?

Collectives™ on Stack Overflow

Select values in array, separate and query the database laravel

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related