At Geoblink, we often find ourselves moving a lot of data from our database to the frontend, to display in useful charts and graphs for our users. Although restructuring data from the backend is usually necessary, it is not especially challenging, as the queries to the database are crafted to return the data how we need it for each purpose. For these cases, Lodash is more than enough to filter and fit the data to needs of the front-end. But, what happens when we do not query the data ourselves? Sometimes, when using third-party APIs, the data may not be structured how we want it. We need a way to organize this data quickly and easily.
alasql("CREATE TABLE stores (name string, sales number)");
alasql("INSERT INTO stores VALUES('Sol', 10000), ('Gran Vía', 221345), ('Preciados', 234562), (‘Callao', 123098)");
var res = alasql("SELECT * FROM store WHERE sales < 150000 ORDER BY sales DESC");
var stores = [
name: ‘Gran Vía’,
var res = alasql("SELECT * FROM ? WHERE sales < 150000 ORDER BY sales DESC", [stores]);
Let’s take a look at how AlaSQL can make data manipulation easy, fast, and maintainable. In the following scenario, we want to structure an array of objects containing stores, each belonging to a different category, and having different amount of sales.
We want to group these stores by category, ignoring ‘Unknown’, and obtain the total sales per category, as well as the percentage of those sales with respect to the global total. Performing this task manipulating the original object can be challenging. Even with libraries such as Underscore.js or Lodash, we would need to perform at least one loop to compute the total, and then a more complex reduction with if statements to group stores and compute sales. This kind of operation however, is relatively easy with an SQL query, so we can take advantage of this with AlaSQL.
const query = `
WITH total_sales AS (
sum(sales) as total
SUM(d.sales / t.total) as sales_percentage,
SUM(d.sales) as sales
d.category != ‘Unknown’
const res = alasql(query, [data, data])
The result is a piece of code that is fast to come up with, and easily understandable.
This of course is just scratching the surface of what you can do with the library. Beyond this basic introduction, it is possible to create you own functions, create compiled statements and functions and much more. Also particularly interesting is how well it integrates with popular libraries and services such as d3.js and Google Maps.
And last but not least: AlaSQL is an open source tool backed by a great community and fantastic documentation. Be sure to check out their page here for a thorough guide on getting started and some great ideas on how to manipulate your data with AlaSQL.
http://tech.geoblink.com/wp-content/uploads/2016/10/GeoblinkTech.png00Ricardo Boludahttp://tech.geoblink.com/wp-content/uploads/2016/10/GeoblinkTech.pngRicardo Boluda2017-09-13 12:00:172017-10-03 12:49:42JS meets SQL. Say hi to AlaSQL!