How can I assign Global variable for all row.

[ruchisheth]

Summary of problem or feature request

I need to set one variable at each iteration of data table in use it all addColumn() and editColumn() function.

Code snippet of problem

Something like

return Datatables::of($users)
->setGlobal(function ($user){
**$status** = **getUserStatus($user->id)**;
})
->addColumn('status', function ($user) {
// return getUserStatus($user->id);
return $status;
});
->editColumn('is_approved', function ($user) {
//return getUserStatus($user->id) ? true : false;
return ($status) ? true : false;

so, each time I don't need to call this getUserStatus($user->id) function.

System details

• Operating System
• PHP Version
• Laravel Version
• Laravel-Datatables Version

[yajra]

global? just pass the variable on closure?

$status = getUserStatus($user->id);

return Datatables::of($users)
->addColumn('status', function ($user) use ($status) {
  return $status;
});

[ruchisheth]

I want the status to be change in each iteration
For each user it should change.

->setGlobal(function ($user){
//for $user->id = 1 //status = true
//for $user->id = 2 // status = false
**$status** = **getUserStatus($user->id)**;
})
->addColumn('status', function ($user) {
// return getUserStatus($user->id);
return $status;
});
->editColumn('is_approved', function ($user) {
//return getUserStatus($user->id) ? true : false;
return ($status) ? true : false;

please don't close the issue before developer's review.

[yajra]

If you want it on each iteration then it's not GLOBAL.

->addColumn('status', function ($user) {
    return getUserStatus($user->id);
});

[ruchisheth]

->addColumn('status', function ($user) {
    return getUserStatus($user->id);
})
->editColumn('xyz', function ($user) {
    return getUserStatus($user->id);
});

For each add or edit column I need to call the same function. that will make call to the MySql query so if I have 5 column using this same function then In one iteration same MySql query call for 5 times and for 1000 records it will be 1000 * 5 = 5000 duplicate query.

If I use paging then also for minimum of 20 records 20 * 5 = 100 duplicate queries.

[yajra]

Use eager loading if you want to solve N+1 issues.

See this demo for ref: https://datatables.yajrabox.com/relation/belongs-to

[yajra]

Or use join statements and include the status on your query.

https://datatables.yajrabox.com/fluent/joins

[ruchisheth]

$assignments = DB::table('assignments as a')
		->when($round_id != "" || $round_id != NULL, function ($query) use ($round_id) {
			return $query->where('a.round_id', '=', $round_id);
		})
		->leftJoin('rounds as r', 'a.round_id', '=', 'r.id')
		->leftJoin('surveys as su', 'a.id', '=', 'su.assignment_id')
		->leftJoin('projects as p', 'r.project_id', '=', 'p.id')
		->leftJoin('sites as s',  'a.site_id', '=', 's.id')
		->leftJoin('fieldreps as f', 'a.fieldrep_id', '=', 'f.id')
		->leftJoin('chains as ch','p.chain_id','=','ch.id')
		->leftJoin('clients as c','ch.client_id','=','c.id')
		->when(is_string($status) && $status != "", function ($query) use ($status, $current_date) {
			if($status == 'late'){
				return $query->where(function ($query) use ($current_date) {
					$query->where(function ($query)  {
						$query->where(DB::raw('IFNULL( DATE_FORMAT(a.deadline_date,"%Y-%c-%e"), DATE_FORMAT(r.deadline_date,"%Y-%c-%e"))'), '<', DB::raw('CURDATE()'));
					})
					->where(function ($query) use ($current_date) {
						$query->where('a.is_scheduled', '=', true)
						->where('a.is_reported', '=', false)
						->where('a.is_partial', '=', false);
					});
				});
			}else if($status == 'scheduled'){
				return $query->where(function ($query) use ($current_date) {
					$query->where(function ($query)  {
						$query->where(DB::raw('IFNULL( DATE_FORMAT(a.deadline_date,"%Y-%c-%e"), DATE_FORMAT(r.deadline_date,"%Y-%c-%e"))'), '>=', DB::raw('CURDATE()'));
					})
					->where(function ($query) use ($current_date) {						
						$query->where('a.is_scheduled', '=', true)
						->where('a.is_reported', '=', false)
						->where('a.is_partial', '=', false);
					});
				});
			}
			else if($status == 'pending'){
				return $query->leftJoin('assignments_offers as ao', function($join)
				{
					$join->on('a.id', '=', 'ao.assignment_id');
				})
				->where('a.is_scheduled', '=', false)
				->where(DB::raw('(SELECT count(id) as offer_count FROM assignments_offers where assignment_id = a.id and is_accepted is null)'), '<=', '0')
				->groupBy('a.id');
			}else if($status == 'offered'){
				return $query->leftJoin('assignments_offers as ao', function($join)
				{
					$join->on('a.id', '=', 'ao.assignment_id');
				})
				->where(['a.is_scheduled' => false, 'a.is_offered' => true])->where(['ao.is_accepted' => NULL])
				->groupBy('ao.assignment_id');
			}else if($status == 'reported'){
				return $query->where(['a.is_reported' => true, 'is_approved' => false]);
			}else if($status == 'partial'){
				return $query->where('a.is_partial', true);
			}else if($status == 'completed' || $status == 'approved'){
				return $query->where('a.is_approved', true);
			}
		})
		->select([
			'a.id',
			'a.fieldrep_id',
			'a.deadline_date',
			'a.is_scheduled',
			'a.is_reported',
			'a.is_partial',
			'a.is_offered',
			'a.is_approved',
			'c.client_logo',
			'p.id as project_id',
			'p.project_name',
			'r.id as round_id',
			'r.round_name',
			's.site_code',
			's.site_name',
			's.city',
			's.state',
			's.zipcode',
			'su.id as survey_id',
			DB::raw("CONCAT(IFNULL( DATE_FORMAT(a.schedule_date,'%d %b %Y'), DATE_FORMAT(r.schedule_date,'%d %b %Y')), ' ' , IFNULL(TIME_FORMAT(a.start_time, '%h:%i %p'), TIME_FORMAT(r.start_time,'%h:%i %p'))) as assignment_scheduled"),
			DB::raw("CONCAT(IFNULL( DATE_FORMAT(a.deadline_date,'%d %b %Y'), DATE_FORMAT(r.deadline_date,'%d %b %Y')), ' ' , IFNULL(TIME_FORMAT(a.start_time, '%h:%i %p'), TIME_FORMAT(r.deadline_time,'%h:%i %p'))) as assignment_end"),
			DB::raw('CONCAT(f.first_name," ",f.last_name) as schedule_to'),
			DB::raw('(select COUNT(id) as offer_count from assignments_offers where assignment_id = a.id and is_accepted is null group by assignment_id) as offer_count'),
			]);

Query is this much complicate. Don't think eager loading will be possible

[ruchisheth]

To use getXYZ and setXYZ functions inside add and edit Column, we compulsory need an object of Eloquent.

[yajra]

Ok didn't expected that much complicated query. ^_^

May consider using cache then? If status doesn't change very often, then you can also set it for a longer period of time.

-- Edit --
Maybe something like:

function getUserStatus($id) {
  return Cache::remember('user.status.'.$id, 1, function($id) {
    return Status::find($id);
  });
}

[ruchisheth]

->setGlobal(function ($user){
//for $user->id = 1 //status = true
//for $user->id = 2 // status = false
$status = getUserStatus($user->id);
})

Hope to something like this would be there

[yajra]

Not really sure on your suggestion setGlobal since you would still need to iterate on each user to be able to fetch the status hence multiple function call will still be performed..

[ruchisheth]

I really look for a way out to get rid of 100s of duplicate queries. just because to call getXYZ and setXYZ in more than one column.

[rajanjain]

I have also the same issue and wants to use a global (common) variable so that i can use it for each iteration as a common variable
@ruchisheth If you got something then please share your answer

Thanks for the help in advance.. :)

[haratmalli]

same @ruchisheth

[ruchisheth]

@rajanjain and @haratmalli I haven't got any solution for this.

[ayoub123345]

hi did you try in the first column to put

data->statues = 4;

call it on the second callback?