jQWidgets Forums

Tagged: data adapter, dataadapter, getGroupedRecords, group, jqxDataAdapter, jqxtree, json, null, Tree

This topic contains 6 replies, has 2 voices, and was last updated by Dimitar 11 years, 6 months ago.

Viewing 7 posts - 1 through 7 (of 7 total)

Author

Grouping on two values Posts

October 9, 2013 at 8:25 pm Grouping on two values #30510

realtek

Participant

I have a dataAdapter that is using getGroupedRecords which I am using as my source for a Tree.

Currently, the dataAdapter is grouping on 1 value returned from json.

I would like the dataAdapter to group also on a second value, however… this value is most of the time null.

Is it possible to group on a value that could sometimes be null? Basically, if its null then I want the items to be added under the first and and not bother with the second group.

If the value is not null then I want the items to be grouped inside that.

Thanks

October 10, 2013 at 7:50 am Grouping on two values #30531

Dimitar

Participant

Hello realtek,

Please provide us with a sample code which illustrates your query.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

October 10, 2013 at 1:19 pm Grouping on two values #30559

realtek

Participant

Hi Dimitar,

OK, my code is below… but the database structure probably won’t make sense to you, so I will try to explain.

I basically have several rows in the database that contain bits of information, I want it to show the following structure in the treeview based on my columns:

-View_Name
–View_DisplayName
–FK_TABLE
–View_DisplayName
–View_DisplayName
-View_Name
–View_DisplayName
–FK_TABLE
–View_DisplayName
–View_DisplayName

However some of the rows have NULL in the database for FK_TABLE, therefore I don’t want them to try grouping on the second grouping field because it is not valid for that type of record.

I hope this all makes sense! If you need me to re-write the SQL to be a more clearer example then let me know and I will think of another scenario that may make sense.

json:

<?php
$conn = mssql_connect("test","test","test");
mssql_select_db("mydatabase",$conn);
$sth = mssql_query("SELECT b.Sequence as Field_Sequence, b.SEQ_Table as Table_Sequence, a.TABLENAME as Table_Name, a.VIEWNAME as View_Name, b.Display_Name as View_Display_Name, b.Column_Name as Table_Column_Name, b.DATA_TYPE as Data_Type, b.COLUMN_TYPE as Column_Type, b.FK_TABLE as FK_TABLE FROM SD_TABLES a left join SD_FIELDS b on a.Sequence = b.SEQ_TABLE");
$rows = array();
while($r = mssql_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);
?>

Treeview:

function loadTree() {
$.ajax(
{
   url: 'json/dba/dba_treeview.php',
   type: 'POST',
   dataType: "json",
   data:
 	{
 		name: 'View_Name',
		name: 'View_Display_Name', 
		name: 'Table_Sequence',
		name: 'FK_TABLE'
	},
	
   success: function (data) 
   {
	//Tree
		var dataAdapter;  
		var records;   
	
			
        dataAdapter = new $.jqx.dataAdapter(data);
                
                dataAdapter.dataBind();
                records = dataAdapter.getGroupedRecords(['View_Name', 'FK_TABLE'], 'items', 'label', [{  name: 'Table_Sequence', map: 'value'}, {name: 'View_Display_Name', map: 'label'}], 'row', 'value');
                $('#DBATree').jqxTree({allowDrag: false, allowDrop: false, source: records, height: '100%', width: '99%', theme: theme })
              
             	  var treeItems = $('#DBATree').jqxTree('getItems');
                var firstItem = treeItems[0];
                var firstItemElement = firstItem.element;
                
				$('#DBATree').jqxTree('selectItem',  $("#DBATree").find('li:first')[0]);
			
			
                }});
			} //End Load Tree Function

October 10, 2013 at 1:25 pm Grouping on two values #30560

realtek

Participant

Thinking about it, the same issue would occour if you wanted the following structure with a “People” table:

-Country
–City
—Address
—-Person
—Address
—-Person
-Country
–City
—Address
—-Person
—Address
—-Person

What if you had a record where there was no address for a homeless person for example? the value you would NULL and you would get the same issue – the TreeView fails to display and you get this error:

Uncaught TypeError: Cannot read property 'element' of undefined

October 11, 2013 at 6:26 am Grouping on two values #30589

Dimitar

Participant

Hi realtek,

Here is a modified version of the demo JSON Tree. You can see there can be a datafield with a null value in the JSON source but the tree initializes successfully.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/gettheme.js"></script>
    <script type="text/javascript" src="../../scripts/jquery-1.10.2.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxpanel.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxtree.js"></script>
</head>
<body>
    <div id='content'>
        <script type="text/javascript">
            $(document).ready(function () {
                var theme = "";
                var data = [
                { "id": "2",
                    "parentid": "1",
                    "text": "Hot Chocolate",
                    "value": "$2.3"
                }, {
                    "id": "3",
                    "parentid": "1",
                    "text": "Peppermint Hot Chocolate",
                    "value": "$2.3"
                }, {
                    "id": "4",
                    "parentid": "1",
                    "text": "Salted Caramel Hot Chocolate",
                    "value": "$2.3"
                }, {
                    "id": "5",
                    "parentid": "1",
                    "text": "White Hot Chocolate",
                    "value": "$2.3"
                }, {
                    "text": "Chocolate Beverage",
                    "id": "1",
                    "parentid": "-1",
                    "value": "$2.3"
                }, {
                    "id": "6",
                    "text": "Espresso Beverage",
                    "parentid": "-1",
                    "value": "$2.3"
                }, {
                    "id": "7",
                    "parentid": "6",
                    "text": null,
                    "value": "$2.3"
                }, {
                    "id": "8",
                    "text": "Caffe Latte",
                    "parentid": "6",
                    "value": "$2.3"
                }, {
                    "id": "9",
                    "text": null,
                    "parentid": "6",
                    "value": "$2.3"
                }, {
                    "id": "10",
                    "text": "Cappuccino",
                    "parentid": "6",
                    "value": "$2.3"
                }, {
                    "id": "11",
                    "text": "Pumpkin Spice Latte",
                    "parentid": "6",
                    "value": "$2.3"
                }, {
                    "id": "12",
                    "text": "Frappuccino",
                    "parentid": "-1"
                }, {
                    "id": "13",
                    "text": "Caffe Vanilla Frappuccino",
                    "parentid": "12",
                    "value": "$2.3"
                }, {
                    "id": "15",
                    "text": "450 calories",
                    "parentid": "13",
                    "value": "$2.3"
                }, {
                    "id": "16",
                    "text": "16g fat",
                    "parentid": "13",
                    "value": "$2.3"
                }, {
                    "id": "17",
                    "text": "13g protein",
                    "parentid": "13",
                    "value": "$2.3"
                }, {
                    "id": "14",
                    "text": "Caffe Vanilla Frappuccino Light",
                    "parentid": "12",
                    "value": "$2.3"
                }]
                // prepare the data
                var source =
                {
                    datatype: "json",
                    datafields: [
                        { name: 'id' },
                        { name: 'parentid' },
                        { name: 'text' },
                        { name: 'value' }
                    ],
                    id: 'id',
                    localdata: data
                };
                // create data adapter.
                var dataAdapter = new $.jqx.dataAdapter(source);
                // perform Data Binding.
                dataAdapter.dataBind();
                // get the tree items. The first parameter is the item's id. The second parameter is the parent item's id. The 'items' parameter represents 
                // the sub items collection name. Each jqxTree item has a 'label' property, but in the JSON data, we have a 'text' field. The last parameter 
                // specifies the mapping between the 'text' and 'label' fields.  
                var records = dataAdapter.getRecordsHierarchy('id', 'parentid', 'items', [{ name: 'text', map: 'label'}]);
                $('#jqxWidget').jqxTree({ source: records, width: '300px', theme: theme });
            });
        </script>
        <div id='jqxWidget'>
        </div>
    </div>
</body>
</html>

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

October 11, 2013 at 12:02 pm Grouping on two values #30602

realtek

Participant

That is excellent, thank you so much Dimitar!

I have re-written my SQL Statement into a stored procedure which outputs the correct format from a table variable which is turned into JSON by the PHP.

The code works excellently and has resolved by issue.

Is this a defect in the getGroupedRecords that null values stop the tree from loading?

Thanks again

October 14, 2013 at 5:50 am Grouping on two values #30671

Dimitar

Participant

Hi realtek,

There are no issues with getGroupedRecords as you can see in the example XML Tree if you set an item value to null in the file customers.xml.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

Author

Posts

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.