123Your first data view · Step 2 of 3
Ask with SQL
You'll learn
- Query a table with
DataHandle:query - Know when a query runs
- Look up SQL functions
- Time
- 10 min
- Builds on
- Step 1: Load a table
- File
view.lua
Which item sold most this week? In this step your app asks the sales table that question in SQL, and shows the answer beside the rows.
Ask a question
Add a query to view.lua, and print each row of its answer:
local ui = datumhue.ui
local data = datumhue.args.dirs.data
local sales = data:read("sales.csv"):csv()
New line: local totals = sales:query([[
New line: SELECT item, SUM(sold) AS total
New line: FROM data
New line: GROUP BY item
New line: ORDER BY total DESC
New line: ]])
New line: for _, row in totals:rows() do
New line: print(row.item, row.total)
New line: end
New line:
local page = ui.root:panel({
width = "100%",
height = "100%",
flex_direction = "column",
padding = 16,
gap = 12,
})
page:label({
text = "Bakery sales: " .. sales:count() .. " rows",
font_size = 20,
})
page:table({
data = sales,
columns = {
{ column = "day", label = "Day" },
{ column = "item", label = "Item" },
{ column = "sold", label = "Sold" },
},
flex_grow = 1,
})
In this example: datumhue.ui datumhue.args Dir:read Bytes:csv DataHandle:query DataHandle:rows datumhue.ui.root UiElement:panel UiElement:label DataHandle:count UiElement:table
Run it as before:
$ datumhue run view.lua --dir data:data
It prints the answer, the best seller first:
Bread 333
Buns 192
Cake 85
DataHandle:queryruns SQL against the table, which the query callsdata. It returns a newDataHandle, the answer, at once.SUM(sold)adds up each item's sales,GROUP BY itemmakes one row per item, andORDER BY total DESCputs the largest total first.DataHandle:rowssteps through the answer one row at a time. Each row is a Lua table keyed by column name.
Nothing runs when you call DataHandle:query. The query runs the first
time something reads its answer, here the for loop, and keeps the
result. A table, a chart or DataHandle:count that reads it later gets
the kept result without running it again.
Show the answer
The answer is a DataHandle like the sales, so a table can show it.
Put the two tables side by side, and drop the loop:
local ui = datumhue.ui
local data = datumhue.args.dirs.data
local sales = data:read("sales.csv"):csv()
local totals = sales:query([[
SELECT item, SUM(sold) AS total
FROM data
GROUP BY item
ORDER BY total DESC
]])
local page = ui.root:panel({
width = "100%",
height = "100%",
flex_direction = "column",
padding = 16,
gap = 12,
})
page:label({
text = "Bakery sales: " .. sales:count() .. " rows",
font_size = 20,
})
New line: local row = page:panel({
New line: flex_direction = "row",
New line: flex_grow = 1,
New line: gap = 12,
New line: })
New line: row:table({
data = sales,
columns = {
{ column = "day", label = "Day" },
{ column = "item", label = "Item" },
{ column = "sold", label = "Sold" },
},
New line: width = 300,
New line: })
New line: row:table({
New line: data = totals,
New line: columns = {
New line: { column = "item", label = "Item" },
New line: { column = "total", label = "Sold this week" },
New line: },
flex_grow = 1,
})
In this example: datumhue.ui datumhue.args Dir:read Bytes:csv DataHandle:query datumhue.ui.root UiElement:panel UiElement:label DataHandle:count UiElement:table
The week's rows sit on the left, and the three totals on the right.
rowis a panel that lays out what it holds side by side.- The rows' table keeps a width of 300 UI px; the answer's table grows into the rest.
- The answer's table reads
totalsitself, which runs the query.
SQL functions lists every function a query can call,
such as AVG, with what it takes and returns.
Try this
- Show the average sold per item instead of the total, with
AVG. - Order the answer by item name.
- Ask which day sold the most: group by
dayinstead ofitem.
Recap
DataHandle:queryreturns a newDataHandleat once. In the SQL, the table it asks isdata.- A query runs the first time something reads its answer, and keeps the result for every later reader.
- The answer is a
DataHandle, so anything that shows a table shows the answer too.
New in this step