This week we had a request come in from a client that involved creating a new database column and then performing a search across multiple columns to collect and display the data to end users of the system. While completing this task we learned the following great information:
1.) DD in the new google chrome. In the latest version of google chrome we were not able to dump and die from our controller. To get around this we added the following command to our controller:
#When using dd() in an ajax call, chrome needs a response code set
http_response_code(500);
dd($results);
The code above was added in place of the normal return results that sends results data from our DB query to the view. In total for testing purposes our public function now looks like:
public function datatables()
// do stuff
#When using dd() in an ajax call, chrome needs a response code set
http_response_code(500);
dd($results);
#return $results;
Now that our controller will spit out information that we can use to test/troubleshoot we will then fire up our local environment, navigate to the view for the page we are working on and then do the following:
1.) Hit F12 to pull up the chrome developer tools.
2.) Click on Network > XHR > Preview. This should take us to a page that looks like:
3.) Now when we update code in our controller we can save the file then go over to chrome dev tools right click on the results link and reload to see the result of our updated code. Example screenshot is below:
2.) Now we are onto the actual query that allows us to search through multiple DB tables and organize the results. I’ve copied the query below and bolded the two new lines that allow us to get the results the client wanted.
$datatable->set_query(function($search_sql, $order_sql, $having_sql, $limit_sql, $values) use ($form_id) {
$values['form_id'] = $form_id;
$results = DB::select("
SELECT SQL_CALC_FOUND_ROWS
u.id AS user_id,
CONCAT(u.first_name, ' ', u.last_name) AS name,
ec.cell_phone,
ec.home_phone,
shift_code,
DATE_FORMAT(u.employment_began_at, '%m/%d/%Y') AS start_date,
SUM(IF(YEAR(date_in) = YEAR(CURDATE()), total_hours, 0)) AS ytd_hours,
SUM(total_hours + historical_overtime) AS total_hours
FROM
form_submissions fs
JOIN forms f ON fs.form_id = f.id
LEFT JOIN users u ON fs.user_id = u.id
JOIN fs_overtime_documentation a ON a.form_submission_id = fs.id
LEFT JOIN forms ec_form ON ec_form.slug = 'emergency_contact'
LEFT JOIN form_submissions ec_form_submission ON ec_form_submission.form_id = ec_form.id AND ec_form_submission.user_id = u.id
LEFT JOIN fs_emergency_contact ec ON ec.form_submission_id = ec_form_submission.id
WHERE
u.active = 1 AND
f.id = :form_id AND
fs.status != 'denied'
{$search_sql}
GROUP BY u.id
HAVING 1=1 {$having_sql}
{$order_sql}
{$limit_sql}
", $values);
return $results;
Expanding on on the explanation above the first line we added is the easier of the two, it takes the Sum of the total hours column if the year = our current year:
SUM(IF(YEAR(date_in) = YEAR(CURDATE()), total_hours, 0)) AS ytd_hours,
The next thing we had to do was total up hours from two different database tables. We do this with:
SUM(total_hours + historical_overtime) AS total_hours
Its important to note the only reason this works is because of the LEFT JOIN a few lines below. This join takes total_hours and historical_overtime and adds them together if the particular user has hours in their historical_overtime DB column:
LEFT JOIN users u ON fs.user_id = u.id