Optimize SQL query using Eloquent de Laravel

Navigation
1

I hope you're all right, I have the following query on my system. 

public function visits()
{

    foreach ($this->countrys as $country) 
    {   
        $query = Visit::where('country',$country)->count();
        $visits[$country]=$query;

    }

    return $visits;
}

The query iterates the next array 

public $countrys = array('AF','AX','AL','DZ','AS','AD','AO','AI','AQ','AG','AR','AM','AW','AU','AT','AZ','BS','BH','BD','BB','BY','BE','BZ','BJ','BM','BT','BO','BQ','BA','BW','BV','BR','IO','VG','BN','BG','BF','BI','KH','CM','CA','CV','KY','CF','TD','CL','CN','CX','CC','CO','KM','CK','CR','HR','CU','CW','CY','CZ','CD','DK','DJ','DM','DO','TL','EC','EG','SV','GQ','ER','EE','ET','FK','FO','FJ','FI','FR','GF','PF','TF','GA','GM','GE','DE','GH','GI','GR','GL','GD','GP','GU','GT','GG','GN','GW','GY','HT','HM','HN','HK','HU','IS','IN','ID','IR','IQ','IE','IM','IL','IT','CI','JM','JP','JE','JO','KZ','KE','KI','XK','KW','KG','LA','LV','LB','LS','LR','LY','LI','LT','LU','MO','MK','MG','MW','MY','MV','ML','MT','MH','MQ','MR','MU','YT','MX','FM','MD','MC','MN','ME','MS','MA','MZ','MM','NA','NR','NP','NL','AN','NC','NZ','NI','NE','NG','NU','NF','KP','MP','NO','OM','PK','PW','PS','PA','PG','PY','PE','PH','PN','PL','PT','PR','QA','CG','RE','RO','RU','RW','BL','SH','KN','LC','MF','PM','VC','WS','SM','ST','SA','SN','RS','CS','SC','SL','SG','SX','SK','SI','SB','SO','ZA','GS','KR','SS','ES','LK','SD','SR','SJ','SZ','SE','CH','SY','TW','TJ','TZ','TH','TG','TK','TO','TT','TN','TR','TM','TC','TV','VI','UG','UA','AE','GB','US','UM','UY','UZ','VU','VA','VE','VN','WF','EH','YE','ZM','ZW');

and the result is this more or less this, of course more summarized 

array["AF"=>32,"AX"=>21];//Asi sucesivamente hasta obtener todos lo resultados de array

But there is a performance problem and it is like 251 queries to the database and every time I enter the dashboard of my application.

Is there a way to make a count of each parameter of the array to the database in a single query?

the columns of the table are the following:  id, ip, country, created_at and updated_at

Thank you for apologizing for the ignorance.

    
asked by Robert Hernández 08.10.2018 в 02:07
source

1 answer

1

After investigating for a while I got the following solution in case someone can help. 

public function visits()
{


    $visits = Visit::select('country')
                    ->selectRaw('count(*) as totalCountry')
                    ->groupBy('country')
                    ->get();

    return $visits;

}
    
answered by 08.10.2018 в 05:51
JSON sent from PHP and read it in JAVASCRIPT Is there a log of the querys executed in Postgresql?