different results by all() vs fetchall()

[Mehdi]

Hi

This is making me mad :( 

I have two tables without defined relations in their models(i have to make my app works with given db) and this is how i make a join query:

subq = DBSession.query(models.ZT_420_001.ma_master_code, models.ZT_420_001.ma_region,

                           models.ZT_420_001.ma_subs, models.ZT_420_001.ma_grid,

                           models.ZT_420_001.ma_xutm, models.ZT_420_001.ma_yutm).subquery()

query = DBSession.query(models.StatSheet).\

    join(subq, and_(models.StatSheet.ma_region_id == subq.c.ma_region,

                    models.StatSheet.ma_utm_x == subq.c.ma_xutm,

                    models.StatSheet.ma_utm_y == subq.c.ma_yutm,

                    models.StatSheet.ma_unique_code == func.TO_CHAR(subq.c.ma_subs))

    ).filter(models.StatSheet.ma_resource_type_id == 1)

Now when i run query by query.all() it gives me 1803 records but when i use execute and fetchall, i'll have 1810 rows!

results = query.all() #=> returns 1803 rows
results = DBSession.execute(query).fetchall() #=> returns 1810 rows

I checked the result by running the same query in oracle sqldeveloper and the correct result was 1810.

So why is this happening?

[Michael Bayer]

the ORM when asked to return full entities (e.g. mapped Python objects) will only return one object per primary key identity in the underlying result set.   This is so that eager loading such as joined-eager loading doesn’t result in the same object returned many times for each item in a related collection.

[Mehdi]

Now i get it :)

Thanks.