Request Storage.Sqlite for last Loggued User

[Nicolas Hiblot]

Hello All,

I try to get the information of the last loggued user on my web2py application.

The goal is to have the user_id number to fix some HTML tags

I have got a SQLITE request

    #SELECT user_id FROM auth_event WHERE description LIKE '%connect%' AND time_stamp = (SELECT MAX(time_stamp) FROM auth_event)

This request works perfect

So I would like to put it on default.py

and I try this

   
    query = db.auth_event.description.contains('connect')
    query &=  db.auth_event.time_stamp.max()
    lastUserLogged = db(query).select(db.auth_event.user_id)

But I have a sqlite error with a misuse of max

Could you please help me to find my error

I have try something like that too ...

    queryTime = db.auth_event.time_stamp.max()

    query = db(queryTime).select(db.auth_time_stamp)

    query &= db.auth_event.description.contains('connect')
    lastUserLogged = db(query).select(db.auth_event.user_id)

But same error

Thanks in advance

[Anthony]

    query = db.auth_event.description.contains('connect')
    query &=  db.auth_event.time_stamp.max()
    lastUserLogged = db(query).select(db.auth_event.user_id)

First, db.auth_event.time_stamp.max() simply produces the following SQL:

MAX("auth_event"."time_stamp")

It is not a query, just an expression.

Second, your approach will not necessarily give you what you want, as there is no guarantee that the maximum timestamp value in the entire auth_event table happens to coincide with the most recent "connect" event (as opposed to some other type of event).

Instead, a better approach is to (reverse) order by timestamp and limit the select to the first record:

db(db.auth_event.description.contains('connect')).select(orderby=~db.auth_event.time_stamp,
                                                         limitby=(0, 1))

Anthony

[Nicolas Hiblot]

Dear Anthony,

Thank you so much.

Works perfectly and of Course Thanks for the explanation ...

Last Little Question :

If I would like to only extract auth_event.user_id of the result of

db(db.auth_event.description.contains('connect')).select(orderby=~db.auth_event.time_stamp,limitby=(0, 1))

because

db(db.auth_event.description.contains('connect')).select(orderby=~db.auth_event.time_stamp,limitby=(0, 1)).first().user_id works

but not

db(db.auth_event.description.contains('connect')).select(orderby=~db.auth_event.time_stamp,limitby=(0, 1)).first().auth_event.user_id

How can I do it very Quickly ?

Regards

Nicolas

[Nicolas Hiblot]

Just for Information

This request works perfectly but in 2 lines ....

    lastUserLogged  = db(db.auth_event.description.contains('connect')).select(orderby=~db.auth_event.time_stamp,limitby=(0, 1)).first().user_id
    lastUserName    = db(db.auth_user.id == lastUserLogged).select(db.auth_user.username).first().username

Regards

Nicolas HIBLOT

[Anthony]

The above can be achieved with a recursive select:

query = db.auth_event.description.contains('connect')

username = db(query).select(db.auth_event.user_id,
                            orderby=~db.auth_event.time_stamp,
                            limitby=(0, 1)).first().user_id.username

This is probably fine given that you are selecting only a single record. If you are selecting many records, recursive selects can become inefficient because you need an additional select for every single record. In that case, a more efficient alternative is a join.

Anthony