Re: Order by database question

[Henrik Sarvell]

Hi Alex thanks,

I suspected that the fact that I was scanning two separate indexes
prevented proper ordering - from reading the select tutorial (very good
piece that one) - but I wanted to confirm.

Added Aux and just replaced the select with your db line and it works.




On Mon, May 1, 2017 at 9:58 AM, Alexander Burger 
wrote:

> Hi Henrik,
>
> > (class +UrlTag +Macropisobj)
> > (rel id(+Key +Number))
> > (rel url   (+Ref +Link) NIL (+Url))
> > (rel usr   (+Ref +Link) NIL (+User))
> > (rel tag   (+Aux +Ref +Link) (usr) NIL (+Tag))
> > ...
> > SELECT * FROM UrlTag WHERE usr = Usr ORDER BY id DESC LIMIT 0,50
> >
> > I manage everything apart from the ordering of the result correctly.
> >
> > I thought this select would order things in descending order:
> >
> > (select (@Obj)
> > ((id +UrlTag (T . NIL)) (usr +UrlTag @Usr))
> > (same @Usr @Obj usr) )
>
> No. If you 'select' by more than one index (here 'id' and 'usr'), the
> order of
> the results is not predictable, because both indexes are searched in
> parallel.
>
>
> We have two possibilities:
>
> 1. If there are not too many expected results, you can first 'select' or
>'collect' all of them, and sort them in memory, e.g.
>
>   (by '((This) (- (: id))) sort (collect 'usr '+UrlTag Usr))
>
> or
>
>   (flip (by '((This) (: id)) sort (collect 'usr '+UrlTag Usr)))
>
>(I usually use 'flip' instead of the negative key)
>
>
> 2. If you expect too many results, so that you don't want to load them all
> into
>memory at once, you need a dedicated index with the desired ordering.
> You
>have already an '+Aux' in your model, but then you need also 'id' for
> 'usr':
>
>   (rel usr (+Aux +Ref +Link) (id) NIL (+User))
>
>Here you can also 'collect' (if there are not many results) from the
>index of 'Usr'
>
>   (collect 'usr '+UrlTag Usr)
>
>to get increasing id's, or - for your case -
>
>   (collect 'usr '+UrlTag (list Usr T) (list Usr))
>
>for decreasing id's. Again, for many results, you 'select' or simply
> 'db'
>
>   (db usr +UrlTag ((@Usr T) @Usr) @Obj)
>
> Not tested, I hope there are not too many errors.
>
> ♪♫ Alex
>
> --
> UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe
>

Re: Order by database question

[Alexander Burger]

Hi Henrik,

> (class +UrlTag +Macropisobj)
> (rel id(+Key +Number))
> (rel url   (+Ref +Link) NIL (+Url))
> (rel usr   (+Ref +Link) NIL (+User))
> (rel tag   (+Aux +Ref +Link) (usr) NIL (+Tag))
> ...
> SELECT * FROM UrlTag WHERE usr = Usr ORDER BY id DESC LIMIT 0,50
> 
> I manage everything apart from the ordering of the result correctly.
> 
> I thought this select would order things in descending order:
> 
> (select (@Obj)
> ((id +UrlTag (T . NIL)) (usr +UrlTag @Usr))
> (same @Usr @Obj usr) )

No. If you 'select' by more than one index (here 'id' and 'usr'), the order of
the results is not predictable, because both indexes are searched in parallel.


We have two possibilities:

1. If there are not too many expected results, you can first 'select' or
   'collect' all of them, and sort them in memory, e.g.

  (by '((This) (- (: id))) sort (collect 'usr '+UrlTag Usr))

or

  (flip (by '((This) (: id)) sort (collect 'usr '+UrlTag Usr)))

   (I usually use 'flip' instead of the negative key)


2. If you expect too many results, so that you don't want to load them all into
   memory at once, you need a dedicated index with the desired ordering. You
   have already an '+Aux' in your model, but then you need also 'id' for 'usr':

  (rel usr (+Aux +Ref +Link) (id) NIL (+User))

   Here you can also 'collect' (if there are not many results) from the
   index of 'Usr'

  (collect 'usr '+UrlTag Usr)

   to get increasing id's, or - for your case -

  (collect 'usr '+UrlTag (list Usr T) (list Usr))

   for decreasing id's. Again, for many results, you 'select' or simply 'db'

  (db usr +UrlTag ((@Usr T) @Usr) @Obj)

Not tested, I hope there are not too many errors.

♪♫ Alex

-- 
UNSUBSCRIBE: mailto:picolisp@software-lab.de?subject=Unsubscribe

Re: Order by database question

[Joh-Tob Schäg]

That could be because of +Key.
Am 01.05.2017 02:18 schrieb "Henrik Sarvell" :

> Hi list,
>
> I've been looking through the docs and all my notes but it looks like I'm
> not managing to get the result I want no matter how I try.
>
> I've got this E/R:
>
> (class +UrlTag +Macropisobj)
> (rel id(+Key +Number))
> (rel url   (+Ref +Link) NIL (+Url))
> (rel usr   (+Ref +Link) NIL (+User))
> (rel tag   (+Aux +Ref +Link) (usr) NIL (+Tag))
>
> Note that id is auto incrementing on every insert.
>
> And I'm using goal, prove and select to try and achieve the Picolisp DB
> version of this SQL:
>
> SELECT * FROM UrlTag WHERE usr = Usr ORDER BY id DESC LIMIT 0,50
>
> I manage everything apart from the ordering of the result correctly.
>
> I thought this select would order things in descending order:
>
> (select (@Obj)
> ((id +UrlTag (T . NIL)) (usr +UrlTag @Usr))
> (same @Usr @Obj usr) )
>
> But it seems I was wrong?
>
>
>