I want to use the python peewee prefetch functionality.
I have three tables: A, B, and C.
B has a foreign key in its table to A, and table C has a foreign key to B.
I want to select all the rows in B, while also prefetching the rows in C that belong to those in B. However, I also want to grab the rows in A that B belongs to so I don't launch new queries every time I try accessing that property from the results.
This is the code I have currently:
b_query = B.select(B, A).join(A).where(B.user_id == user_id)
c_query = C.select()
prefetch(b_query, c_query)
However, this code fails with the error: OperationalError: (1241, 'Operand should contain 1 column(s)')
When I change the code to this it works:
b_query = B.select().join(A).where(B.user_id == user_id)
c_query = C.select()
But, since I am no longer selecting A in b_query, I believe every time I access b.a it will launch a new query, which I am trying to avoid. Any suggestions?
EDIT:
I did some more digging into this, and it seems that trying to do it the way I initially proposed might be impossible. The successful mechanism results in this query as one of the two queries resulting from the prefetch:
SELECT C.* from C WHERE C.B_id IN (SELECT B.id FROM B WHERE B.user_id == <user_id>)
Trying to add A into the select for b_query results in the following query:
SELECT C.* from C WHERE C.B_id IN (SELECT B.*, A.* WHERE B.user_id == <user_id>)
Obviously this won't work.
This was the result of a bug in the prefetch function. I opened an issue on peewee's issue tracker and the bug is fixed in master (https://github.com/coleifer/peewee/issues/324). Commit e9a38d24b.