Bug: erlsql: group_by, order_by multiple fields
Buggaboo
I have a table called archiveT.
SQL allows order_bys on multiple fields. Erlsql doesn't support this
syntax, as far as I can tell.
It has three fields (year:int, month:int, entry_id).
The following is my function to generate a list <a
href="/blog/archives/Year/Month">s for a navigator component.
<code>
...
%% TODO - Fix the query to sort ascending, sorted by desc year, month
archives (A) ->
Query =
{
esql,
{select,distinct,[year,month],{from,archive}
% [{order_by,{year,desc}}] %% correct, but not what I want
% [{order_by,{year,month,desc}}] %% sql syntax error
% [{order_by, {[year,month], desc}}] %% erlang error
}
}
, F = fun ( [Year, Month] ) ->
YStr = integer_to_list(Year)
, MStr = integer_to_list(Month)
, {
list_to_binary
([erlyweb:get_app_root(A),<<"blog/archives/">>,YStr,$/,MStr])
, list_to_binary([YStr,$/,MStr])
}
end
, {data,{mysql_result, _, Results, _, _}} =
erlydb_mysql:q(Query)
, Archives = [ F(R) || R <- Results ]
, [ {ewc, html_list, hyperlist_item, [A, L, Ar]} || {L, Ar} <-
Archives ].
...
</code>
Notice the {esql, select...} statement with the outcommented order_bys.
The syntax I suggest to allow multiple fields in order_by, group_by
sql-expressions:
select/whatever ... [{group_by, {[fieldi,...,fieldk], desc/asc}}]
I'll have a look at it in two weeks, maybe solve it myself and send the
patches... but I have to start packing for my vacation now.
Regards,
Bgb
Buggaboo
I should really be packing now but I could'nt resist hacking erlsql...
gawds, my gf is going to kill me.
I have no time to test, but I think it will work.
I think it's safe against injections... need someone to verify it for me
though.
Have a good one.
Buggaboo
Apparently this construction:
<erl>
Query =
{
esql,
{... % some select, delete, insert... statement
, [{order_by, [{field_one, desc}, {field_two, desc}]}]
}
}
</erl>
Is already supported. I should've tested thoroughly first.
Impressive work, Yariv.
