Accessing attributes of reference fields

[Quint]

Hello web2py users,

 

I have a question about reference fields.

 

When i access a attribute of a reference Field, i notice that the reference field is represented by an instance of the "Reference" class.

When i access attributes on that reference , will it perform a new query each time or does it cache previous queries somehow?

 

BTW, I'm using GAE

 

Example:

 

I have a table "comment" which has a reference field "author auth_user"

Now i perform a query and i retrieve a list of comments which contains a lot of comments from the same user.

When i access a attribute on those comments will it query the auth_user table for all comments of the same author?

 

Thanks and regards.

[Anthony]

Yes, the reference fields execute a query each time when you access their attributes -- there is no caching. I think it might be a good idea to implement caching as an option. Even better, there should be an option to fetch the related records for all rows along with the initial select, either via a join or a single additional select -- this would be much more efficient than running a separate select for each row when you've got lots of rows.

Anthony

[Quint]

like this?

 

You run a query with an extra option specify the ref fields to pre-fetch. and then it would query all the referenced rows in a smart way (only fetch a record 1x).

 

Is there a  generally accepted way to handle this (in the absence of  the mentioned feature)?

 

Right now I'm fetching my rows and loop over it and store the referenced rows in a dictionary and at the end use the dict to store the fetched rows on the parent rows.

Then a pass the composite thing to my view.

[Anthony]

On Thursday, August 15, 2013 6:57:23 AM UTC-4, Quint wrote:

like this?

 

You run a query with an extra option specify the ref fields to pre-fetch. and then it would query all the referenced rows in a smart way (only fetch a record 1x).

 

Is there a  generally accepted way to handle this (in the absence of  the mentioned feature)?

Well, the most efficient way is probably to do a join, but that results in a somewhat different type of object.

 

 

Right now I'm fetching my rows and loop over it and store the referenced rows in a dictionary and at the end use the dict to store the fetched rows on the parent rows.

Then a pass the composite thing to my view.

I suppose you could also do something like that using Field.Virtual:

db.define_table('person',
    Field('name'),
    Field.Virtual('dogs', lambda r: r.person.dog.select()))

db.define_table('dog',
    Field('name'),
    Field('owner', 'reference person'))

bob = db(db.person.name == 'Bob').select().first()
print bob.dogs

Or going in the other direction:

db.define_table('person',
    Field('name'))

db.define_table('dog',
    Field('name'),
    Field('owner', 'reference person'),
    Field.Virtual('owner_record', lambda r: db.person(r.dog.owner)))

spot = db(db.dog.name == 'spot').select().first()
print spot.owner_record.name

Note, don't define these virtual fields in both tables at the same time, as that will lead to infinite recursion when attempting a select from either one.

Anthony

[Quint]

thnx,

 

but my goal is to prevent repeated queries for the same record when accessing ref fields on rows from the same result set.

 

I'm missing something, how do those Virtual Fields achieve that?

 

And about the join, wouldn't that mean it won't work on GAE?

[Anthony]

On Thursday, August 15, 2013 10:04:45 AM UTC-4, Quint wrote:

thnx,

 

but my goal is to prevent repeated queries for the same record when accessing ref fields on rows from the same result set.

 

I'm missing something, how do those Virtual Fields achieve that?

Yes, that's exactly what the virtual field does -- when the the Rows object is created from the initial select, the value of the virtual field is calculated for each Row, and it remains in the Row (i.e., it is not re-calculated on each access). There is also Field.Method(), which does re-calculate on every access (the advantage is that it is lazy, so nothing is calculated until accessed -- and because of that, it can take arguments at the time it is called).

 And about the join, wouldn't that mean it won't work on GAE?

Oh, yeah, forgot you said you are on GAE.

Anthony

[Quint van den Muijsenberg]

But when when a row is calculating it's virtual field, it will fetch the referenced row even when some other row from the initial set has already fetched this same referenced row (when they are referencing the same thing). So they will both perform the same fetch.

?

I want to do this fetch 1x and use that record for all rows from the initial set that reference it.

--
 
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/IrAe-AGpiMU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Anthony]

Good point. I suppose you can adapt your method to work with a virtual field:

owners = {}
db.define_table('dog',
    ...
    Field.Virtual('owner_record',
        lambda r: owners.setdefault(r.dog.owner, db.person(r.dog.owner)))

That will store each fetched owner record in the owners dict, so the record will be copied from the dict if it is already there rather than pulled from the db.

Anthony

[Quint]

Thanks!

Got it woking now but i got strange results:

("Not working" means that the same referenced record is being fetched every time and not read from the dict)

This is not working:

Field.Virtual('created_by_record',
                    lambda r: users.setdefault(r.comment.created_by.id, db.auth_user(r.comment.created_by))),

This is also not working:

users = {}
def created_by_record(r):
    k = r.comment.created_by.id
    return users.setdefault(k, db.auth_user(k))


Field.Virtual('created_by_record', created_by_record),

But this is working:

users = {}
def created_by_record(r):
    k = r.comment.created_by.id
    if k in users:
        return users[k]
    users[k] = db.auth_user(k)
    return users[k]


Field.Virtual('created_by_record', created_by_record),

[Quint van den Muijsenberg]

Appears that with set_default, the default param is always evaluated and this will perform the query every time guess..

[Anthony]

Oops, of course, the select is done within setdefault. Your if statement version is the way to go.

Anthony