user

AKY King of Life

5 Mar 2022

Select from a query with peewee

General

have some troubles implementing the following query with peewee:

SELECT *
FROM (
 SELECT datas.*, (rank() over(partition by tracking_id order by date_of_data DESC)) as rank_result
 FROM datas
 WHERE tracking_id in (1, 2, 3, 4, 5, 6)
)
WHERE rank_result < 3;

I have tried to do the following:

subquery = (Datas.select(Datas.tracking, Datas.value, Datas.date_of_data,
 fn.rank().over(partition_by=[Datas.tracking],
 order_by=[Datas.date_of_data.desc()]).alias('rank'))
 .where(Datas.tracking.in_([1, 2, 3, 4, 5, 6])))
result = (Datas.select()
 .from_(subquery)
 .where(SQL('rank') < 3))

but since I'm doing "Model.select()" i'm getting all the fields in the SQL SELECT which i don't want and which doesn't make my query work. Here is the schema of my table:

CREATE TABLE IF NOT EXISTS "datas" 
(
 "id" INTEGER NOT NULL PRIMARY KEY, 
 "tracking_id" INTEGER NOT NULL, 
 "value" INTEGER NOT NULL, 
 "date_of_data" DATETIME NOT NULL, 
 FOREIGN KEY ("tracking_id") REFERENCES "follower" ("id")
);
CREATE INDEX "datas_tracking_id" ON "datas" ("tracking_id");

Thanks!

Comments

No Comments to show

© 2024 Copyrights reserved for web-brackets.com