Combining multiple DB rows into one

jimbob72

I am server-side processing two DB tables (same DB) joined through one common value (table1.id = table2.otherid)

Table 1 contains some basic data (e.g. id, title, date etc.). The id is unique.
Table 2 contains multiple rows for each otherid (non-unique) with different content types and values:
eg. otherid=1, content=1, value=foo
otherid=1, content=2, value=bar
otherid=1, content=3, value=shoe
otherid=2, content=1, value=foofoo
otherid=2, content=2, value=barbar
etc.

I have my script returning each combined row (e.g. id/otherid, title, date, content, value) quite happily and can filter by id/otherid or content etc. But what I need to do is combine all table2 rows into one for each unique id/otherid value, so I can output:

id/otherid, title, date, value[content=1], value[content=2], value[content=3] in a single row.

That would give me: 1, title, date, foo, bar, shoe instead of having three separate rows.

To cut a long story short, does anyone know how to do this? I can imagine I need to combine the value fields for each id/otherid into an array and probably use the mDataProp to derive the individual values at the browser-end, but not sure how to go about this. Help appreciated greatly.

allan

Hi jimbob72,

Do you have the data in a structure that looks something like this on the server:

[code]
{
"id": 1,
"title": "title",
"date": "date",
"value": [
"foo",
"bar",
"shoe"
]
},
...
[/code]

If so, then I think all you need to do is on the server do a 'join' to concatenate the values together into a single value. Equally this could be done using mDataProp as you suggest. Something like:

[code]
"mDataProp": function (o) {
return o.value.join(', ');
}
[/code]

would do it.

But it depends a little bit on what the data structure looks like :-)

Regards,
Allan

fbas

Easiest way is to modify/customize your server-processing file to run whatever query you need that combines the tables.

your client program doesn't need to know anything about the schema on your db side. if you output things as a single row/array (either using mDataProp values or simply as a numeric array) the client side table will not know the difference.

jimbob72

Thanks for the comments guys. My data is basically multiple rows in a mySQL table (so, no, in answer to your question Allan). For each id in table1 there are upwards of 10 rows in table2 with the corresponding otherid.

The issue is how do I combine these rows (each of which has a different content value) into one for each id? I need all the content values to be part of the same single row.

allan

Ah okay - so two options:

1. Modify the SQL to do an inner SELECT when getting the table data. While generally easy enough to do, I suspect it will be horribly complicated when working with a dynamic SQL build like my demo server-side processing script, so:

2. If we use this as a base: http://datatables.net/development/server-side/php_mysql - around line 158, add another SQL query to get the data from table 2 for the row in question. Then just add it into the output array. The downside is that it does mean you will be doing 10 extra SQL queries on each page load, so this won't scale massively well if you are looking at a very high traffic site.

Allan

jimbob72

I'm getting close on this (option 2). I'm able to output the data in a nice JSON structure, but i'm getting a 'data[a[0]] is undefined' error in the browser.

Relevant bits of the Server side code:
[code]/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS $sSelect
FROM $sTable
$sWhere
$sOrder
$sLimit
";
//echo $sQuery;
$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 $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];


/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i[/code]
And my initialisation code:
[code]// <![CDATA[
$(document).ready(function() {
$('#documents').dataTable( {
"oLanguage": {"sLengthMenu": 'Show '+
'10'+
'25'+
'50'+
'100'+
'All'+
' items'

},
"bProcessing": true,
"bServerSide": true,
"bAutoWidth": false,
"sAjaxSource": "[[++site_url]]resources/php/dbtable_parse_documents2.php",
"fnServerParams": function ( aoData ) {
aoData.push( { "name": "template", "value": "1" } );

},
"aaSorting": [ [ 0, "desc" ] ],
"sPaginationType": "full_numbers",
"aoColumns" : [


{ "mDataProp": "modx_site_content.pagetitle" },
{ "mDataProp": "modx_site_content_publishedon" },
{ "mDataProp": "modx_site_content_template" }
]


} );
} ) ;
// ]]>[/code]
This is what I get back in the data:
[quote]{"sEcho":1,"iTotalRecords":"910","iTotalDisplayRecords":"698","aaData":[{"values":["Commodities","commodities\/video\/Commodity-Update-November-2011.flv","Name","North America","video","commodities\/video\/thumbs\/mcglone_071211.jpg","Commentary","FA Solutions||Real Asset Solutions"],"modx_site_content.pagetitle":"November 2011 S&P GSCI Commodity Update<\/a>","modx_site_content.publishedon":"12\/07\/11","modx_site_content.template":"1","modx_site_content.published":"1"},{"values":["Commodities","commodities\/video\/Commodity-Update-November-2011.flv","Name","North America","video","commodities\/video\/thumbs\/mcglone_071211.jpg","Commentary","FA Solutions||Real Asset Solutions","SPIVA","portal\/PersistenceScorecard_Nov2011_Final.pdf","North America","Featured on Portal","Data and Analysis||SPIVA"],"modx_site_content.pagetitle":"S&P Persistence Scorecard: November 2011<\/a>","modx_site_content.publishedon":"11\/29\/11","modx_site_content.template":"1","modx_site_content.published":"1"},... etc.[/quote]

allan

What you've got above look stood to me - what I'm not quite clear on is where you are using "data[a[0]]". Is that in the code you are using to do the row open/close?

Thanks,
Allan

jimbob72

This is a different project to my previous one. In this one the rows don't expand. I'm not using data[a[0]] anywhere as far as I know. Hmm. odd.

allan

Does Firebug or Inspector tell you what line of code that error is occurring on then?

Allan

jimbob72

It's occurring on line 6746 of the DataTables.js

[code]else if ( typeof mSource == 'string' && mSource.indexOf('.') != -1 )
{
/* If there is a . in the source string then the data source is in a nested object
* we provide two 'quick' functions for the look up to speed up the most common
* operation, and a generalised one for when it is needed
*/
var a = mSource.split('.');
if ( a.length == 2 )
{
return function (data) {
return data[ a[0] ][ a[1] ];
};
}
else if ( a.length == 3 )
{
return function (data) {
return data[ a[0] ][ a[1] ][ a[2] ];
};
} [/code]

If I call "mDataProp" : "values.0" i get the error in Firebug but I don't get pop-up. If I call "mDataProp" : "values.1" I get the error and a pop-up saying that value doesn't exist

allan

Given your DataTables initialisation above, I don't understand why it would be looking at the values array at all. Is there any chance you can give me a link to your page?

Thanks,
Allan

jimbob72

Sure - I will send to you privately as it's confidential.

jimbob72

Sorry - I tweaked my initialisation to include the values array. But it's not working for some reason.

allan

Thanks for the link - really helpful! So I think I see the problem. In some rows you supply the values.Type property - and in some others you don't. DataTables assumes that if you have specified that a data property is going to be included in the output, then it is always going to be available - hence the problem.

So, the way you can address this is to replace the mDataProp that you currently have for value.Type with:

[code]
"mDataProp": function (o) {
return (typeof o.value.Type != 'undefined') ?
o.value.Type : "";
}
[/code]

Basically what this is doing is checking that the property is available - if not then an empty string is used - otherwise the value that is given is used.

And hopefully that will do it!

Regards,
Allan

jimbob72

That's got rid of the error. But now I have a different one:

o.value is undefined
return (typeof o.value.Type != 'undefined') ?

allan

Heh - so if value is not always returned you need to expand the condition a little bit more:

[code]
return (typeof o.value != 'undefined' && typeof o.value.Type != 'undefined') ?
[/code]

Allan

jimbob72

Now the original error has come back

data[a[0]] is undefined
[Break On This Error] return data[ a[0] ][ a[1] ];

allan

Okay, I think I understand fully the issue now. Your JSON object looks like this:

[code]
{
"values": {
"File": "..."
},
"modx_site_content.pagetitle": "...",
"modx_site_content.publishedon": "01/12/11",
"modx_site_content.template": "1",
"modx_site_content.published": "1"
},
[/code]

But using mDataProp "modx_site_content.pagetitle", DataTables expects an object with the property "page title" - i.e.:

[code]
{
"values": {
"File": "..."
},
"modx_site_content": {
"pagetitle": "...",
"publishedon": "01/12/11",
"template": "1",
"published": "1"
}
},
[/code]

because DataTables is trying to expand the donated notation. Sorry I didn't catch that before - first time I've hit this.

So two options:

1. You could change your returned object format
2. Remove the dot

The third option would be to modify DataTables to not expand the dotted notation, but you are using that for the values... :-)

Allan

jimbob72

Ah - yes. I see that now. Thanks. I will amend the output to drop the column prefix 'modx_site_content' - that should do the trick.

Have a nice evening!

shark69

Hi jimbob72, this is my first comment sorry for my bad english.
I'm interesting about your solution, but I've some problem with the code. For example, how declare you the array Columns for this application? Could you expose the code a little 'more detail' if it's possible.
Thank's for your time