Issue with query with join and id in select field list

[Ricardo Oliveira]

Hi,

I've discovered what I believe to be a strange behaviour, but it might be my mistake.

So, given two tables, for example:

db.define_table('group_of_people',

    Field('name', 'string'),

)

db.define_table('person',

    Field('name', 'string'),

    Field('age', 'integer'),

    Field('group_of_people', 'reference group_of_people'),

)

If I do a select with a join and not include the "id" field in the query and then convert to a dict like so:

def data():

    persons = db((db.person.id > 0) & (db.group_of_people.id == db.person.group_of_people)).select(db.person.name, db.person.age, db.group_of_people.name)

    parsed_people = {'data': []}
    for person in persons:
        p = {}
        logger.debug("tests::data - person: %s" % pformat(person))

        for table in person:
            logger.debug("tests::data - table: %s" % pformat(table))
            logger.debug("tests::data - table data: %s" % pformat(person[table]))

            for field in person[table]:
                logger.debug("tests::data - %s: %s" % (field, pformat(person[table][field])))

                p[field] = person[table][field] if person[table][field] is not None else ''

        logger.debug("tests::data - p: %s" % pformat(p))

        parsed_people['data'].append(p)

    return json.dumps(parsed_people)

I get a normal row with only those fields in the group, something like:

{"data": [{"age": 1, "name": "person 1"}, {"age": 2, "name": "person 2"}, ... }

And in the log, everything is normal:

DEBUG:tests:tests::data - person: <Row {'group_of_people': {'name': 'group2'}, 'person': {'age': 1L, 'name': 'person 1'}}>

DEBUG:tests:tests::data - table: 'group_of_people'

DEBUG:tests:tests::data - table data: <Row {'name': 'group2'}>

DEBUG:tests:tests::data - name: 'group2'

DEBUG:tests:tests::data - table: 'person'

DEBUG:tests:tests::data - table data: <Row {'age': 1L, 'name': 'person 1'}>

DEBUG:tests:tests::data - age: 1L

DEBUG:tests:tests::data - name: 'person 1'

DEBUG:tests:tests::data - p: {'age': 1L, 'name': 'person 1'}

But if I add the db.person.id field in the query, this doesn't work, because suddenly I get a bunch of other records in the row, like update_record, and even references to rows that reference the table and the json part fails as it's including Row objects which are not serializable (does not show up in this simple example but it shows in more complex joins, with multiple tables - I can try to reproduce that error in a test to include here if necessary).

The log becomes:

DEBUG:tests:tests::data - person: <Row {'group_of_people': {'name': 'group2'}, 'person': {'age': 1L, 'id': 1L, 'name': 'person 1'}}>

DEBUG:tests:tests::data - table: 'group_of_people'

DEBUG:tests:tests::data - table data: <Row {'name': 'group2'}>

DEBUG:tests:tests::data - name: 'group2'

DEBUG:tests:tests::data - table: 'person'

DEBUG:tests:tests::data - table data: <Row {'age': 1L, 'id': 1L, 'name': 'person 1'}>

DEBUG:tests:tests::data - update_record: <pydal.helpers.classes.RecordUpdater object at 0x10e8e3810>

DEBUG:tests:tests::data - age: 1L

DEBUG:tests:tests::data - id: 1L

DEBUG:tests:tests::data - delete_record: <pydal.helpers.classes.RecordDeleter object at 0x10e8e3850>

DEBUG:tests:tests::data - name: 'person 1'

DEBUG:tests:tests::data - p: {'age': 1L,

 'delete_record': <pydal.helpers.classes.RecordDeleter object at 0x10e8e3850>,

 'id': 1L,

 'name': 'person 1',

 'update_record': <pydal.helpers.classes.RecordUpdater object at 0x10e8e3810>}

Is this expected? Do I have to force my loops to only go after the columns I need, despite having already requested them in the select?

This was tested with Version 2.14.6-stable+timestamp.2016.05.10.00.21.47 on Python 2.7.13.

TIA,

Ricardo.

[Anthony]

The problem is here:

 

            for field in person[table]:

person[table] is a Row object, and when the Row object includes the record ID, it also includes the special .update_record and .delete_record attributes. So, you'll either have to specify the fields explicitly, or check the types (the Row.as_dict method takes the latter approach).

Anthony

[Ricardo Oliveira]

Hi Anthony,

Thanks for your reply.

That was also my initial solution.

Problem is I need to use render() in order to force the lambdas of the table fields to be executed, and I can't seem to find a way to combine render() and as_dict(), that's why I used the approach of iterating through the Rows.

Is there some way to do this? That would also work.

Thanks again.

Ricardo.

[Anthony]

You can call .as_dict() on a row object inside an iteration through .render(). Here is a one-liner to replace all your code:

json.dumps(dict(data=[{k: v for sub_row in row.as_dict().values() for (k, v) in sub_row.iteritems()}
                      for row in persons.render()]))

The list comprehension iterates through persons.render(), which yields a Row object for each record (with the represent functions applied to each field). For each Row object, there is then a dictionary comprehension with two "for" clauses -- the first converts the Row to a nested dict via .as_dict and then uses .values() to extract the two nested dicts into a list containing the two dicts, and the second then calls .iteritems() on each of the dicts in order to generate a new dictionary.

Note, you still have a problem -- you have two fields with the same name (i.e., "name"), so in the combined dictionary, the second will overwrite the first.

Anthony

[Ricardo Oliveira]

Hi Anthony,

Thanks for your excellent example and explanation.

You're right about the problem with the field names, luckily the problem occurs only in this example, not in production code, but just out of curiosity, would using "with_alias()" help?

Thanks again for all your help. It made a huge difference!

Ricardo.

[Anthony]

On Thursday, October 12, 2017 at 12:45:05 PM UTC-4, Ricardo Oliveira wrote:

Hi Anthony,

Thanks for your excellent example and explanation.

You're right about the problem with the field names, luckily the problem occurs only in this example, not in production code, but just out of curiosity, would using "with_alias()" help?

The results of .as_dict() get messy with .with_alias(), so you would need a slightly different approach.

Anthony