Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Inner query problem #665

Closed
dneyirp opened this issue Jul 22, 2015 · 1 comment
Closed

Inner query problem #665

dneyirp opened this issue Jul 22, 2015 · 1 comment

Comments

@dneyirp
Copy link

dneyirp commented Jul 22, 2015

Hi. I need to execute the following SQL statement but use Peewee's library to do it and prefer not to use raw sql.
Table "Log" has has this structure:

CREATE TABLE "Log" ("Id" INTEGER NOT NULL PRIMARY KEY, "DateTimeUTC" DATETIME NOT NULL,  "Value" INTEGER NOT NULL)

SQL query needing translation:

select * from
    (select * from Log
     order by DateTimeUTC desc
     limit 20
    )
    order by DateTimeUTC

I have been helped by the author of Peewee ORM, Charles, with the following code:

inner_query = Log.select().order_by(Log.DateTimeUTC.desc()).limit(20)
query = Log.select().from_(inner_query).order_by(Log.DateTimeUTC)

This generates an error:
OperationalError: no such column: t1.Id

I was then given a modification to try:

inner_query = Log.select().order_by(Log.DateTimeUTC.desc()).limit(20)
inner_query = inner_query.alias('t1')
query = Log.select().from_(inner_query).order_by(Log.DateTimeUTC)

However I get another error:
OperationalError: no such column: t1.DateTimeUTC

Looking at the debug code Peewee provides:
[2015-07-22 12:04:23,822] [peewee] DEBUG]: ('SELECT "t1"."Id", "t1"."DateTimeUTC", "t1"."Value" FROM (SELECT "t2"."Id" FROM "Log" AS t2 ORDER BY "t2"."DateTimeUTC" DESC LIMIT 20) AS t1 ORDER BY "t1"."DateTimeUTC" ASC', [])
It seems that when Peewee is executing the first line of the last snippet inner_query = Log.select()..., the conversion to SQL is incorrect and only using the ".Id" field instead of all fields in the select() and result in the "no such column" error.

Firstly it seems that the need to add the line inner_query = inner_query.alias('t1') looks like a bug as "t1" is not used in my query.

Secondly there seems to be a bug somewhere in translating select() to SQL when you have an inner query as only one field is being used when all should be used.

Many thanks
P

@coleifer
Copy link
Owner

Change:

inner_query = Log.select().order_by(Log.DateTimeUTC.desc()).limit(20)

To:

inner_query = Log.select(Log).order_by(Log.DateTimeUTC.desc()).limit(20)

If a subquery does not have an explicit selection, peewee assumes you just want the primary key, hence the missing column.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants