QLC sort in Mnesia by several fields?

Questions / Help
, , ,
1

Hi!

I’m trying to use Mnesia as the database for my small project and I’m currently trying to implement a cursor based pagination following this article: https://medium.com/@george_16060/cursor-based-pagination-with-arbitrary-ordering-b4af6d5e22db

At the moment, I’m stuck trying to get Mnesia to order a simple query by 2 fields, even though I got it working for one. My current implementation looks like this:

QH1 = qlc:q([Product || Product <- mnesia:table(product)]),
QH2 = qlc:sort(QH1, [{order,
                              fun(A, B) ->
                                 (product:get_quantity(A) >= product:get_quantity(B))
                                 and (product:get_id(A) >= product:get_id(B))
                              end}]),
Rows = qlc:eval(QH2)

The sorting function is literally the same I used in my tests to order a list of products, but that time using lists:sort, which seems to be working as expected.

Am I doing something wrong or there is a reason why qlc is not able to sort by several fields? Is this a limitation of Mnesia somehow?

Thank you all!

1 Like
2

I was finally able to solve the issue on my own. I saw a few examples of the sorting and it made me think that it was as easy as making a few boolean comparisons, but my use case actually requires more the logic we see in a compareTo from Java.

The final implementation in my case looks like this:

orderBy(Fields) ->
    fun(A, B) ->
       Res = lists:foldl(fun (Field, eq) ->
                                 case Field of
                                     id ->
                                         utils:compare(
                                             product:get_id(A), product:get_id(B));
                                     name ->
                                         utils:compare(
                                             product:get_name(A), product:get_name(B));
                                     price ->
                                         utils:compare(
                                             product:get_price(A), product:get_price(B));
                                     quantity ->
                                         utils:compare(
                                             product:get_quantity(A), product:get_quantity(B))
                                 end;
                             (_Field, Acc) -> Acc
                         end,
                         eq,
                         Fields ++ [id]),
       Res == gt
    end.

compare(A, B) when A > B ->
    gt;
compare(A, B) when A == B ->
    eq;
compare(A, B) when A < B ->
    lt.

This should work for a dynamic list of fields to use in the orderBy function. I still did not figure out why the sorting works somehow differently in the lists:sort, but I ended up changing it there too since some edge cases were not working as expected.

Regards :slight_smile: