Friday, January 30, 2015

Select2 Grouped Static Data With AJAX Search Fallback

GIST =>  Select2 Grouped Static Data With AJAX Fallback

I have a database table named Locations which contains hierarchical data which again grouped by a title. To take the confusion off, here's the sample JSON returned from my API.

{
"results":[
{
"text":"Country",
"children":[
{
"id":11082,
"text":"SRI LANKA (LK)"
},
{
"id":10627,
"text":"MAURITIUS (MU)"
}
]
},
{
"text":"Airport",
"children":[
{
"id":17269,
"text":"Katunayake Airport"
}
]
},
{
"text":"City",
"children":[
{
"id":25845,
"text":"Kandy"
},
{
"id":25829,
"text":"Galle"
}
]
}
]
}

As you can see, I have Cities, Airports, Countries, Provinces etc. In each JSON object, "text" is the group name, children are well, the children of that group.

My Locations table has thousands of rows so I need to auto-fill these grouped data on the fly from my API. I am using the awesome Select2 library, which has everything I need to search, format and display these data.

But, I have another requirement, which is,

I have another table called something like PreferredLocations which contains some of these locations which I would search most of the time. I need to display these data (<20 records) when I open select2 dropdown before searching anything.

Using default options and operations, you can either set static data or use ajax to query the API. I want to do the both. all we have to do is create a custom "QUERY" function in select2 initialization.

This is the code, commented.


var initLocationLookup = function (element, apiUrl, dataItems, opt) {
opt = opt || {};
// retrieve these from initial ajax query or somewhere
var dt = { results: [{
"text":"Country",
"children":[
{
"id":11082,
"text":"SRI LANKA (LK)"
},
{
"id":10627,
"text":"MAURITIUS (MU)"
}
]
}]};
$(element).select2({
minimumInputLength: 0, // important!!! => without setting to 0 the default data will not be shown
ajax: {
// Select2's ajax helper
url: apiUrl,
dataType: 'json',
data: function (term, page) {
return {
q: term, // search term
page_limit: 10
};
},
results: function (data, page) {
// data from server is something like
// {"Locations":[{"text":"Country","children":[{"id":11107,"text":"UNITED KINGDOM (GB)"}]}]}
return { results: data.Locations };
}
},
initSelection: function (element, callback) {
// if there's an initial value set in the input control, use it to query the data
var id = element.val();
if (id) {
$.ajax({
url: apiUrl + "?q=" + id
}).done(function (data) {
// data from server is something like
// {"Locations":[{"text":"Country","children":[{"id":11107,"text":"UNITED KINGDOM (GB)"}]}]}
if (data.Locations && data.Locations.length > 0 && data.Locations[0].children && data.Locations[0].children.length > 0) {
callback(data.Locations[0].children[0]);
}
});
}
},
multiple: false,
query: function (query) {
// here we check whether the user has entered some search term
// and enforce a min term length to 2 chars
if (query.term && query.term.length > 2)
$.ajax({
url: apiUrl + "?q=" + query.term
}).done(function (data) {
// data from server is something like
// {"Locations":[{"text":"Country","children":[{"id":11107,"text":"UNITED KINGDOM (GB)"}]}]}
if (data.Locations && data.Locations.length > 0) {
query.callback({ results: data.Locations });
}
});
else
query.callback(dt); // no search term, display default data
}
});
};

And the output,



Happy coding!


Thursday, January 29, 2015

MSSQL - Get Comma Separated String from multiple rows

I'm a big fan of Select2 library and there are instances where I have to use the multiple selections. like the ones below.
Select2 Tagging Support
I store them values in a table structure which looks like the following.
1:M relationship
This table maps the One-to-Many relationship between my service profiles and facilities. (One profile has many facilities). Using Select2, I can select many facilities at once.

Select2 serializes the Facility IDs as a comma separated string and I have to split it into individual facility IDs to store in the database. Storing the CSV string is a bad idea. How the hell are you going to query them?

-- delcare a new test table
DECLARE @Table1 TABLE (
                  ID    INT,
                  Value INT);

-- insert dummy values
INSERT  INTO @Table1
VALUES (1, 100),
       (1, 200),
       (1, 300),
       (1, 400);

Query To convert the values into a CSV string

SELECT   ID,
         STUFF((SELECT ', ' + CAST (Value AS VARCHAR (10)) AS [text()]
                FROM   @Table1
                WHERE  ID = t.ID
                FOR    XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') AS List_Output
FROM     @Table1 AS t
GROUP BY ID;

Result

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

Wednesday, January 7, 2015

Simple Wizard using Knockout.js

My scenario isn't all that simple tho. Imagine I have a customer type on my wizard and depending on this type, I have to choose the next step of the wizard. How would you do it with less code in a more reusable, easy to modify way?

To give you an idea, I have setup a jsfiddle for you.

Let me see if I can embed it here...



Note: I have used the following libs.

underscore.js  - http://jashkenas.github.io/underscore/

knockout - http://knockoutjs.com/

twitter-bootstrap  - http://getbootstrap.com/

The code should be easier to follow.
 
There is a "stepModels" array containing all the steps and its related templates. it also contains template type option which indicates the path it should take if the customer type is a business.

commented almost every step of the code so it should be easier to understand.