Skip to content

Instantly share code, notes, and snippets.

@peterknolle
Created May 7, 2014 02:20
Show Gist options
  • Save peterknolle/8a14ca005cbf2d2b7247 to your computer and use it in GitHub Desktop.
Save peterknolle/8a14ca005cbf2d2b7247 to your computer and use it in GitHub Desktop.
Google Visualization API Joins in Visualforce
<apex:component >
<apex:attribute name="ident" type="String" required="true"
description="Uniquely identifies this component within a page. Should not contain spaces."/>
<!-- data table 1 -->
<apex:attribute name="dt1DataJson" type="String" required="true"
description="JSON specifying data for the first data table"/>
<apex:attribute name="dt1ConfigJson" type="String" required="true"
description="JSON specifying config for the first data table"/>
<!-- data table 2 -->
<apex:attribute name="dt2DataJson" type="String" required="true"
description="JSON specifying data for the second data table"/>
<apex:attribute name="dt2ConfigJson" type="String" required="true"
description="JSON specifying config for the second data table"/>
<apex:attribute name="keys" type="String" required="true"
description="JSON specifying keys for join"/>
<apex:attribute name="joinType" type="String" default="full"
description="The initial join type to use"/>
<apex:attribute name="dt1Cols" type="String" required="true"
description="JSON specifying columns from dt1 that should appear in result, in addition to keys"/>
<apex:attribute name="dt2Cols" type="String" required="true"
description="JSON specifying columns from dt2 that should appear in result, in addition to keys"/>
<apex:attribute name="drawChart" type="Boolean" required="false" default="true"
description="Specifies whether or not to draw the chart"/>
<apex:attribute name="drawTable" type="Boolean" required="false" default="true"
description="Specifies whether or not to draw the table"/>
<script>
(function() {
var $j = jQuery.noConflict();
var dataTable1, dataTable2;
function drawInitJoin() {
dataTable1 = createTable({!dt1DataJson}, {!dt1ConfigJson});
dataTable2 = createTable({!dt2DataJson}, {!dt2ConfigJson});
drawJoin('{!joinType}');
}
function createTable(dtData, dtConfig) {
var dt = new google.visualization.DataTable();
var colDefs = dtConfig.cols;
// Set up the column headers
for (var i = 0; i < colDefs.length; i++) {
dt.addColumn(colDefs[i].dataType, colDefs[i].label);
}
// Populate the rows
for (var i = 0; i < dtData.length; i++) {
dt.addRow( createRow(dtData[i], colDefs) );
}
return dt;
}
function createRow(rowData, colDefs) {
var rowCells = [];
for (var i = 0; i < colDefs.length; i++) {
var colDef = colDefs[i];
rowCells[i] = rowData[colDef.name];
if (colDef.dataType === 'date') {
rowCells[i] = new Date(rowCells[i]);
}
}
return rowCells;
}
function drawJoin(joinType) {
// Join the tables on the date column (0) and show both "Number Of" columns (1).
var joinedDataTable = google.visualization.data.join(dataTable1, dataTable2, joinType, {!keys}, {!dt1Cols}, {!dt2Cols});
if ({!drawChart}) {
// Draw the chart
var joinedChart = new google.visualization.LineChart(document.getElementById('{!ident}-joinedChart'));
joinedChart.draw(joinedDataTable, null);
}
if ({!drawTable}) {
// Draw the table
var joinedTable = new google.visualization.Table(document.getElementById('{!ident}-joinedTable'));
joinedTable.draw(joinedDataTable, null);
}
}
google.setOnLoadCallback(drawInitJoin);
$j(document).ready(function() {
$j('#{!ident}-joinButton').click(function(e) {
e.preventDefault();
var selectedJoin = $j('select#{!ident}-joinType option:selected').val();
drawJoin(selectedJoin);
});
});
})();
</script>
<form>
Join Type:
<select id="{!ident}-joinType">
<option value="full">full</option>
<option value="inner">inner</option>
<option value="left">left</option>
<option value="right">right</option>
</select>
<button id="{!ident}-joinButton">Apply Join</button>
<div id="{!ident}-joinedChart"></div>
<div id="{!ident}-joinedTable"></div>
</form>
</apex:component>
<apex:page controller="JoinController" readOnly="true">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="//www.google.com/jsapi"></script>
<script>
google.load('visualization', '1', {packages:['corechart','table']});
</script>
<c:join ident="pvLeads1"
dt1DataJson="{!pageViewsJson}"
dt1ConfigJson="{cols: [{label: 'Date', name: 'Date__c', dataType: 'date'},
{label: 'Number of Views', name: 'Number_of_Views__c', dataType: 'number'}]}"
dt2DataJson="{!leadsJson}"
dt2ConfigJson="{cols: [{label: 'Date', name: 'createdDate', dataType: 'date'},
{label: 'Number of Leads', name: 'numberOfLeads', dataType: 'number'}]}"
keys="[[0,0]]"
dt1Cols="[1]"
dt2Cols="[1]"
/>
</apex:page>
public with sharing class JoinController {
// The from and through dates are hard-coded for simplicity's sake.
// Should change to parameters or input or something else.
public String getPageViewsJson() {
List<Page_View_Summary__c> pvs = [
SELECT Date__c, Number_of_Views__c
FROM Page_View_Summary__c
WHERE Date__c >= 2014-04-01
AND Date__c <= 2014-04-30
ORDER BY Date__c
];
return JSON.serialize(pvs);
}
public String getLeadsJson() {
List<AggregateResult> aggRes = [
SELECT Count(Id) numberOfLeads, Created_Date__c createdDate
FROM Lead
WHERE Created_Date__c >= 2014-04-01
AND Created_Date__c <= 2014-04-30
GROUP BY Created_Date__c
ORDER BY Created_Date__c
];
return JSON.serialize(aggRes);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment