For instance, if we consider the General Ledger report, and the simple
table scan on account_move:
clause = [
...,
('period.fiscalyear.company', '=', data['company']),
]
return Move.search(clause,
order=[('date', 'ASC'), ('id', 'ASC')])
The extra AND clause generated by 'period.fiscalyear.company', '=',
data['company'] seems to be:
AND ("account_move"."period" IN (SELECT "account_period".id AS id FROM
"account_period" WHERE (("account_period"."fiscalyear" IN (SELECT
"account_fiscalyear".id AS id FROM "account_fiscalyear" WHERE
(("account_fiscalyear"."company" = 1)) AND ("account_fiscalyear".id IN
(SELECT "account_fiscalyear".id AS id FROM "account_fiscalyear" WHERE
(((("account_fiscalyear"."company" = 1))) AND true)))))) AND
("account_period".id IN (SELECT "account_period".id AS id FROM
"account_period" WHERE (((("account_period"."fiscalyear" IN (SELECT
"account_fiscalyear".id AS id FROM "account_fiscalyear" WHERE
(("account_fiscalyear"."company" = 1)))))) AND true))))))
I am not sure this is efficient, but for sure it is unreadable.
Not to talk about the case when such query needs to be related to other
tables (that could also requires such indirection).
Having company field in the move table would probably zap this extra clause
wouldn't it ?
This is the complete clause should you want to EXPLAIN:
SELECT "account_move".id AS id,"account_move"."create_uid" AS
"create_uid","account_move"."create_date" AS
"create_date","account_move"."centralised_line" AS
"centralised_line","account_move"."post_number" AS
"post_number","account_move"."write_uid" AS
"write_uid","account_move"."journal" AS "journal","account_move"."state" AS
"state","account_move"."period" AS "period","account_move"."write_date" AS
"write_date","account_move"."date" AS "date","account_move"."number" AS
"number","account_move"."description" AS "description",CAST(EXTRACT(EPOCH
FROM (COALESCE("account_move".write_date, "account_move".create_date))) AS
VARCHAR) AS _timestamp FROM "account_move" WHERE (("account_move"."date" >=
'2012-01-01'::date) AND ("account_move"."date" <= '2013-04-09'::date) AND
("account_move"."period" IN (SELECT "account_period".id AS id FROM
"account_period" WHERE (("account_period"."fiscalyear" IN (SELECT
"account_fiscalyear".id AS id FROM "account_fiscalyear" WHERE
(("account_fiscalyear"."company" = 1)) AND ("account_fiscalyear".id IN
(SELECT "account_fiscalyear".id AS id FROM "account_fiscalyear" WHERE
(((("account_fiscalyear"."company" = 1))) AND true)))))) AND
("account_period".id IN (SELECT "account_period".id AS id FROM
"account_period" WHERE (((("account_period"."fiscalyear" IN (SELECT
"account_fiscalyear".id AS id FROM "account_fiscalyear" WHERE
(("account_fiscalyear"."company" = 1)))))) AND true)))))) ORDER BY
"account_move"."date" ASC,"account_move"."id" ASC
