Laravel Experts ! please help me.
I am new to laravel and just made datatable using yajra datatable.
The issue is that its speed is too slow when the result has over 2k rows.
Please check my code and give me the best solution for fixing it.
Please note I am a beginner and forgive me with my Pure coding.
I didn't use get() for collection and maybe Join and WhereIn clauses have problems?
Controller:
public function MyController(Request $request)
{
if(request()->ajax())
{
$season_id_array = $request -> season_id_array;
$season_id_array = explode(",", $season_id_array);
$league_id_array = $request -> league_id_array;
$league_id_array = explode(",", $league_id_array);
$weeknumber_date = $request -> weeknumber;
$match_result = DB::table('season_match_plan as a')
->join('team_list as b', 'b.team_id', '=', 'a.home_team_id')
->join('team_list as c', 'c.team_id', '=', 'a.away_team_id')
->join('season as d', 'd.season_id', '=', 'a.season_id')
->join('league as e', 'e.league_id', '=', 'a.league_id')
->join('season_league_team_info as f',
function($join)
{
$join->on('a.season_id', '=','f.season_id');
$join->on('a.home_team_id', '=', 'f.team_id');
})
->join('season_league_team_info as g',
function($join)
{
$join->on('a.season_id', '=','g.season_id');
$join->on('a.away_team_id', '=', 'g.team_id');
})
->leftJoin('odds as h2',
function($join)
{
$join->on('a.match_id', '=','h2.match_id');
$join->on('h2.bookmaker_id', '=', DB::raw('(SELECT id FROM bookmakers WHERE bookmaker_name = "highest")'));
}
)
->leftjoin('real_price_dcl as r', 'a.DCL_refer_id', '=', 'r.id')
-> where(
'a.status', '=', 'END'
)
-> where(function($q) use($season_id_array){
$q -> orwhereIn('a.season_id', $season_id_array);
})
-> where(function($q) use($league_id_array){
$q -> orwhereIn('a.league_id', $league_id_array);
})
->when($weeknumber_date, function($query, $weeknumber_date) {
$query -> where( 'c_WN', '=' ,function ($q)use($weeknumber_date){
$q -> select('week') ->from('date_week_map') ->where('date','=', $weeknumber_date);
});
})
-> select(
DB::raw('e.`league_title` AS League') ,
DB::raw('d.`season_title` AS Season') ,
DB::raw('a.`date` AS Date') ,
DB::raw('a.c_WN') ,
DB::raw(" CONCAT(b.`team_name` , ' : ', c.`team_name` ) AS Game"),
DB::raw("CONCAT('<a href="eachMatch/', TO_BASE64(a.`match_id`), '" target="_blank">', CONCAT(a.`total_home_score` , ' : ', a.`total_away_score`), '</a>') AS Score"),
'a.total_home_score', 'a.total_away_score',
DB::raw('b.`team_name` AS Home_team_name'),
DB::raw(' f.`S_H_ranking` AS Static_HRank'),
DB::raw(' a.`D_Home_RS_8` AS Dynamic_HRS_8'),
DB::raw(' a.`D_Home_RS_6` AS Dynamic_HRS_6'),
DB::raw(' a.`home_team_score` AS Home_score'),
DB::raw(' a.`home_team_strength` AS Home_strength'),
DB::raw(' c.`team_name` AS Away_team_name'),
DB::raw('g.`S_A_ranking` AS Static_ARank'),
DB::raw('a.`D_Away_RS_8` AS Dynamic_ARS_8'),
DB::raw('a.`D_Away_RS_6` AS Dynamic_ARS_6'),
DB::raw('a.`Away_team_score` AS Away_score'),
DB::raw('a.`Away_team_strength` AS Away_strength'),
DB::raw('CONCAT(f.`S_H_ranking`, " v " , g.`S_A_ranking`) AS staticRank'),
DB::raw('CONCAT(a.`D_Home_ranking_8`, " v ", a.`D_Away_ranking_8`) AS DynamicRank_8'),
DB::raw('CONCAT(a.`D_Home_ranking_6`, " v ", a.`D_Away_ranking_6`) AS DynamicRank_6'),
DB::raw('h2.Home AS highest_Home'),
DB::raw('h2.Draw AS highest_Draw'),
DB::raw('h2.Away AS highest_Away'),
DB::raw('r.H_price AS Real_Home'),
DB::raw('r.D_price AS Real_Draw'),
DB::raw('r.A_price AS Real_Away')
)
->orderBy('a.match_id');
return DataTables()::of($match_result)
->editColumn('Score', function($match_result) {
return html_entity_decode($match_result->Score);
})
->rawColumns(['Score'])
->make(true);
}
}
Js:
$('#table').DataTable({
processing: false,
lengthMenu: [[10, 50, 100, -1], [ 10,50, 100, "All"]],
pageLength: 100,
destroy: true,
serverSide: true,
ajax: {
url: "showRanking",
type: 'POST',
data:
{
_token : $('meta[name="csrf-token"]').attr('content'),
season_id_array : season_id_array,
league_id_array : league_id_array,
weeknumber : weeknumber,
}
},
columns: [
{ data: 'League', name: 'League', width: '200px' },
{ data: 'Season', name: 'Season' },
{ data: 'Date', name: 'a.Date' },
{ data: 'c_WN', name: 'c_WN' },
{ data: 'Game', name: 'Game', width:'200px'},
{ data: 'Score', name: 'Score' },
{ data: 'Home_team_name', name: 'Home_team_name'},
{ data: 'Static_HRank', name: 'Static_HRank' },
{ data: 'Dynamic_HRS_8', name: 'Dynamic_HRS_8'},
{ data: 'Dynamic_HRS_6', name: 'Dynamic_HRS_6'},
{ data: 'Home_score', name: 'Home_score'},
{ data: 'Home_strength', name: 'Home_strength'},
{ data: 'Away_team_name', name: 'Away_team_name'},
{ data: 'Static_ARank', name: 'Static_ARank' },
{ data: 'Dynamic_ARS_8', name: 'Dynamic_ARS_8'},
{ data: 'Dynamic_ARS_6', name: 'Dynamic_ARS_6'},
{ data: 'Away_score', name: 'Away_score'},
{ data: 'Away_strength', name: 'Away_strength'},
{ data: 'staticRank', name: 'staticRank'},
{ data: 'DynamicRank_8', name: 'DynamicRank_8'},
{ data: 'DynamicRank_6', name: 'DynamicRank_6'},
{ data: 'highest_Home', name: 'highest_Home'},
{ data: 'highest_Draw', name: 'highest_Draw'},
{ data: 'highest_Away', name: 'highest_Away'},
{ data: 'Real_Home', name: 'Real_Home'},
{ data: 'Real_Draw', name: 'Real_Draw'},
{ data: 'Real_Away', name: 'Real_Away'},
],
}
);
So sorting and Searching are not working on Front too.
Please help me,I am a Laravel beginner.
Best Regards.
question from:
https://stackoverflow.com/questions/65923510/how-to-fix-low-speed-using-laravel-yajra-datatable 
