Databases
StackOverflow2013
Postsdbo
POmySQL: How do I do multiple subqueries and group by for use with datatables?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmySQL: How do I do multiple subqueries and group by for use with datatables?
    primarykey
    data
    text
    <p>I want to generate the following data set for use with DataTables. For the filtering and sorting to work properly, using JOINS instead of subqueries is difficult, if not impossible (as far as I can tell).</p> <p>Applicant Name, Position, Interviewer, Interview 1 Score, Interview 2 Score, Demo. Score, Interview Date</p> <p>Here is how the data is stored in mySQL (simplified version):</p> <p>applicant_scores table</p> <pre><code>id applicant_id interviewer_id score_type score datetime_recorded 1 2 3 Interview 1 80 2013-04-23 09:35:48 2 2 45 Interview 1 70 2013-04-23 10:14:23 3 2 3 Interview 2 85 2013-04-23 09:35:48 4 2 45 Interview 2 77 2013-04-23 10:14:23 5 2 3 Demonstration 76 2013-04-23 09:35:48 6 3 45 Interview 1 79 2013-04-23 10:14:23 7 3 3 Interview 1 86 2013-04-23 09:35:48 8 3 45 Interview 2 92 2013-04-23 10:14:23 </code></pre> <p>applicants table </p> <pre><code>id first_name last_name 2 John Doe 3 Jane Doe </code></pre> <p>interviewer table</p> <pre><code>id first_name last_name 3 Santa Claus 45 Fred Flintstone </code></pre> <p>applicant_positions table </p> <pre><code>position_id applicant_id 1 2 1 3 </code></pre> <p>positions table</p> <pre><code>id title 1 Advanced mySQL Programmer </code></pre> <p>I've tried everything that I can think of, but I'm having trouble grouping scores by interviewer and applicant correctly. What I expect to see is:</p> <pre><code>Applicant Name Position Interviewer Interview 1 Score Interview 2 Score Demo. Score Interview Date John Doe Advanced mySQL Programmer Santa Claus 80 85 76 2013-04-23 John Doe Advanced mySQL Programmer Fred Flintstone 70 77 2013-04-23 Jane Doe Advanced mySQL Programmer Santa Claus 86 2013-04-23 Jane Doe Advanced mySQL Programmer Fred Flintstone 79 92 2013-04-23 </code></pre> <p>Here is my current query which gives zero results:</p> <pre><code>SELECT SQL_CALC_FOUND_ROWS CONCAT( a.first_name, ' ', a.last_name), p.title, CONCAT( i.first_name, ' ', i.last_name), (SELECT score FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id AND s.score_type = 'Interview 1') as score_1, (SELECT score FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id AND s.score_type = 'Interview 2') as score_2, (SELECT score FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id AND s.score_type = 'Demonstration') as score_3, (SELECT DATE_FORMAT(datetime_recorded, '%m-%d-%Y') FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id) as interview_date FROM applicants a, positions p, interviewers i, applicant_scores s WHERE s.applicant_id = a.id AND i.id = s.interviewer_id GROUP BY i.id </code></pre> <p>Here is the server side ajax code that is being used to feed DataTables... Here is info on how to create server side script for DataTables - <a href="http://www.datatables.net/release-datatables/examples/data_sources/server_side.html" rel="nofollow">http://www.datatables.net/release-datatables/examples/data_sources/server_side.html</a></p> <pre><code>&lt;?php /* Database connection information */ $gaSql['user'] = ""; $gaSql['password'] = ""; $gaSql['db'] = ""; $gaSql['server'] = ""; $q1="'"; $q2='"'; $name = "CONCAT( ".$q2."&lt;input type='hidden' id='name' value='".$q2.", LOWER(a.last_name), ' ', LOWER(a.first_name), ".$q2."'&gt;&lt;input type='hidden' id='applicant_id' value='".$q2.", a.id, ".$q2."'&gt;&lt;a href='applicant_details.php?id=".$q2.", a.id, ".$q2."'&gt;&lt;img src='img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", a.last_name, ', ', a.first_name )"; $interviewer = "CONCAT( ".$q2."&lt;input type='hidden' id='name' value='".$q2.", LOWER(u.lastname), ' ', LOWER(u.firstname), ".$q2."'&gt;&lt;input type='hidden' id='interviewer_id' value='".$q2.", i.id, ".$q2."'&gt;&lt;a href='interviewer_details.php?id=".$q2.", i.id, ".$q2."'&gt;&lt;img src='img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", u.lastname, ', ', u.firstname )"; $int_1_score = "(SELECT score FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id AND s.score_type = 'Interview 1' AND s.interviewer_id = i.id) as score_1"; $int_2_score = "(SELECT score FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id AND s.score_type = 'Interview 2' AND s.interviewer_id = i.id) as score_2"; $demo_score = "(SELECT score FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id AND s.score_type = 'Demonstration' AND s.interviewer_id = i.id) as score_3"; $interview_date = "(SELECT DATE_FORMAT(datetime_recorded, '%m-%d-%Y') FROM applicant_scores s, applicants a WHERE s.applicant_id = a.id) as interview_date"; $aColumns = array($name, 'p.title', $interviewer, $int_1_score, $int_2_score, $demo_score, $interview_date); /* Indexed column (used for fast and accurate table cardinality) */ $sIndexColumn = "id"; $sIndexTable = "formtemplates"; /* DB table to use */ $sTable = "applicants a, positions p, interviewers i, applicant_scores s, users u"; $sWhere = " WHERE s.applicant_id = a.id AND i.id = s.interviewer_id AND u.id = i.user_id"; $sGroupBy = " GROUP BY i.id"; /* * MySQL connection */ $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or die( 'Could not open connection to server' ); mysql_select_db( $gaSql['db'], $gaSql['link'] ) or die( 'Could not select database '. $gaSql['db'] ); /* * Paging */ $sLimit = ""; if ( isset( $_GET['iDisplayStart'] ) &amp;&amp; $_GET['iDisplayLength'] != '-1' ) { $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ". mysql_real_escape_string( $_GET['iDisplayLength'] ); } /* * Ordering */ if ( isset( $_GET['iSortCol_0'] ) ) { $sOrder = " ORDER BY "; for ( $i=0 ; $i&lt;intval( $_GET['iSortingCols'] ) ; $i++ ) { if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) { if(stripos($aColumns[ intval( $_GET['iSortCol_'.$i] ) ], "c.name", 1) &gt;= 1){ $sOrder .= "c.name ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ).", "; }elseif(stripos($aColumns[ intval( $_GET['iSortCol_'.$i] ) ], "t.firstName", 1) &gt;= 1){ $sOrder .= "t.firstName ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ).", "; }elseif(stripos($aColumns[ intval( $_GET['iSortCol_'.$i] ) ], "d.shortName", 1) &gt;= 1){ $sOrder .= "d.shortName ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ).", "; }else{ $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", "; } } } $sOrder = substr_replace( $sOrder, "", -2 ); if ( $sOrder == "ORDER BY" ) { $sOrder = ""; } } /* * Filtering * NOTE this does not match the built-in DataTables filtering which does it * word by word on any field. It's possible to do here, but concerned about efficiency * on very large tables, and MySQL's regex functionality is very limited */ //define columns to filter on $aFcolumns = array( 'u.username', 'u.firstname', 'u.lastname', "REPLACE (g.status, 'OUT_FOR_SIGNATURE', 'WAITING')"); if ( $_GET['sSearch'] != "" ) { //$sWhere .= "WHERE ("; $sWhere .= " AND ("; for ( $i=0 ; $i&lt;count($aFcolumns) ; $i++ ) { $sWhere .= $aFcolumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR "; } $sWhere = substr_replace( $sWhere, "", -3 ); $sWhere .= ')'; } /* Individual column filtering */ for ( $i=0 ; $i&lt;count($aFcolumns) ; $i++ ) { if ( $_GET['bSearchable_'.$i] == "true" &amp;&amp; $_GET['sSearch_'.$i] != '' ) { if ( $sWhere == "" ) { $sWhere = "WHERE "; } else { $sWhere .= " AND "; } $sWhere .= $aFcolumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' "; } } /* * SQL queries * Get data to display */ $sQuery = " SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM $sTable $sWhere $sGroupBy $sOrder $sLimit "; //echo $sQuery; die(); //for firebug debugging $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); /* Data set length after filtering */ $sQuery = " SELECT FOUND_ROWS() "; $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal); $iFilteredTotal = $aResultFilterTotal[0]; /* Total data set length */ $sQuery = " SELECT COUNT(".$sIndexColumn.") FROM $sIndexTable "; $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); $aResultTotal = mysql_fetch_array($rResultTotal); $iTotal = $aResultTotal[0]; /* * Output */ $output = array( "sEcho" =&gt; intval($_GET['sEcho']), "iTotalRecords" =&gt; $iTotal, "iTotalDisplayRecords" =&gt; $iFilteredTotal, "aaData" =&gt; array() ); while ( $aRow = mysql_fetch_array( $rResult ) ) { $row = array(); for ( $i=0 ; $i&lt;count($aColumns) ; $i++ ) { if ( $aColumns[$i] != ' ' ) { /* General output */ $row[] = $aRow[$i]; } } //add another row for buttons //$row[] = "&lt;div style='float:right;'&gt;&lt;span style='padding-right:2px;'&gt;&lt;a href=''&gt;&lt;img src='global_img\user_info.png' border='0'&gt;&lt;/a&gt;&lt;/span&gt;&lt;span style='padding-right:2px;'&gt;&lt;a href=''&gt;&lt;img src='global_img\user_edit.png' border='0'&gt;&lt;/a&gt;&lt;/span&gt;&lt;span style='padding-right:0px;'&gt;&lt;a href=''&gt;&lt;img src='global_img\user_delete.png' border='0'&gt;&lt;/a&gt;&lt;/span&gt;&lt;/div&gt;"; $output['aaData'][] = $row; } echo json_encode( $output ); ?&gt; </code></pre> <p>I modified the server side script to attempt to use the solution provided by bluefeet and the following is the generated query:</p> <pre><code> SELECT SQL_CALC_FOUND_ROWS concat(a.first_name, ' ', a.last_name), p.title, CONCAT(i1.first_name, ' ', i1.last_name), max(case when ac.score_type='Interview 1' then ac.score else '' end), max(case when ac.score_type='Interview 2' then ac.score else '' end), max(case when ac.score_type='Demonstration' then ac.score else '' end), DATE_FORMAT(ac.datetime_recorded, '%m-%d-%Y') FROM applicants a inner join applicant_positions ap on a.id = ap.applicant_id inner join positions p on ap.position_id = p.id inner join applicant_scores ac on a.id = ac.applicant_id inner join interviewers i1 on ac.interviewer_id = i1.id GROUP BY concat(a.first_name, ' ', a.last_name), p.title, CONCAT(i1.first_name, ' ', i1.last_name), DATE_FORMAT(ac.datetime_recorded, '%m-%d-%Y') ORDER BY concat(a.first_name, ' ', a.last_name) asc LIMIT 0, 500 </code></pre> <p>This gives me an empty result set though. HELP?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScompcentral
    1. UScompcentral
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmySQL: How do I do multiple subqueries and group by for use with datatables?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYour first query is not valid (maybe a typo?). It has `AND u.id = i.user_id` but no table `u` defined.
      singulars
      1. POmySQL: How do I do multiple subqueries and group by for use with datatables?
      1. USypercubeᵀᴹ
    2. COYeah. That was an older version of the query. I made the correction.
      singulars
      1. POmySQL: How do I do multiple subqueries and group by for use with datatables?
      1. UScompcentral
    3. COAnd now you are missing a join condition on `positions p`
      singulars
      1. POmySQL: How do I do multiple subqueries and group by for use with datatables?
      1. USypercubeᵀᴹ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload