| Author |
Message |
< Erlyweb mailing list ~ Bug: erlsql: group_by, order_by multiple fields |
| Guest |
 Posted: Tue Aug 12, 2008 4:42 pm |
 |
|
|
Guest
|
Hey,
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post received from mailinglist |
|
|
| Back to top |
|
| Guest |
| Posted: Wed Aug 13, 2008 1:57 pm |
|
|
|
Guest
|
Hey hey,
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.
On Aug 12, 3:07 pm, Buggaboo <buggas...@gmail.com> wrote:
> Hey,
>
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post received from mailinglist |
|
|
| Back to top |
|
| Guest |
| Posted: Wed Aug 27, 2008 10:22 am |
|
|
|
Guest
|
Consider this problem resolved.
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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post received from mailinglist |
|
|
| Back to top |
|
| Guest |
| Posted: Wed Aug 27, 2008 4:14 pm |
|
|
|
Guest
|
Oh yeah, I should have mentioned that earlier. My bad.
On Wed, Aug 27, 2008 at 3:22 AM, Buggaboo <buggasnoo@gmail.com> wrote:
>
> Consider this problem resolved.
>
> 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.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post received from mailinglist |
|
|
| Back to top |
|
|
|
All times are GMT
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum
|
|
|
Digg It
Del.icio.us
Reddit
Facebook
Stumble Upon
Technorati
