I have 2 related models, model_A and model_B. There's a 1:n relationship among them, one row in model_A can have several related rows in model_B.

model_A is a view, created with CREATE OR REPLACE VIEW view_name AS (...
model_B is an ordinary table.

I'd like one of the columns of model_A to contain the number of related records in model_B. How do I achieve that?

Definition of model_A:

CREATE OR REPLACE VIEW prestazioni_preventivate AS (
row_number() OVER (ORDER BY res_valutazione.id, prestazione_preventivata) as id,
generate_series(1,res_prestazione.occorrenze_totali) as prestazione_preventivata,
res_valutazione.id AS valutazione_id,
res_braccio.id AS braccio_id,
res_prestazione.id as prestazione_id,
tipo_prestazione.name AS prestazione,
tipo_prestazione.id AS tipo_prestazione_id,
res_week.id AS week,
LEFT OUTER JOIN res_braccio ON res_braccio.valutazione_id = res_valutazione.id
LEFT OUTER JOIN res_prestazione ON res_prestazione.braccio_id = res_braccio.id
LEFT OUTER JOIN tipo_prestazione ON res_prestazione.tipo_prestazione_ids = tipo_prestazione.id
LEFT OUTER JOIN res_week ON res_valutazione.id = res_week.valutazione_id AND res_braccio.id = res_week.braccio_id AND res_prestazione.id = res_week.prestazione_id
LEFT OUTER JOIN generate_series(1,res_prestazione.occorrenze_totali) as prestazione_preventivata on true
WHERE res_valutazione.studio_id is not  null
res_valutazione.accepted_overhead is TRUE
ORDER BY res_valutazione.studio_id, res_braccio.seleziona_braccio         )

Definition of model_B:

> describe eroga_prestazione;
| Column                      | Type                        | Modifiers                                                       |
| id                          | integer                     |  not null default nextval('eroga_prestazione_id_seq'::regclass) |
| data                        | timestamp without time zone |                                                                 |
| paziente_id                 | integer                     |                                                                 |
| studio_id                   | integer                     |                                                                 |
| prestazione_ids             | integer                     |                                                                 |
| costo_tariffario            | double precision            |                                                                 |
| onorario                    | double precision            |                                                                 |
| company_id                  | integer                     |                                                                 |
| currency_id                 | integer                     |  not null                                                       |
| create_uid                  | integer                     |                                                                 |
| create_date                 | timestamp without time zone |                                                                 |
| write_uid                   | integer                     |                                                                 |
| write_date                  | timestamp without time zone |                                                                 |
| supporto_sanitario_diretto  | double precision            |                                                                 |
| medico_id                   | integer                     |                                                                 |
| infermiere_id               | integer                     |                                                                 |
| tecnico_id                  | integer                     |                                                                 |
| percentuale_infermiere      | integer                     |                                                                 |
| quota_infermiere            | double precision            |                                                                 |
| percentuale_tecnico         | integer                     |                                                                 |
| quota_tecnico               | double precision            |                                                                 |
| prestazione_preventivata_id | integer                     |                                                                 |
    "eroga_prestazione_pkey" PRIMARY KEY, btree (id)
    "eroga_prestazione_company_id_index" btree (company_id)
Foreign-key constraints:
    "eroga_prestazione_company_id_fkey" FOREIGN KEY (company_id) REFERENCES res_company(id) ON DELETE SET NULL
    "eroga_prestazione_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL
    "eroga_prestazione_currency_id_fkey" FOREIGN KEY (currency_id) REFERENCES res_currency(id) ON DELETE RESTRICT
    "eroga_prestazione_infermiere_id_fkey" FOREIGN KEY (infermiere_id) REFERENCES sc_personale(id) ON DELETE SET NULL
    "eroga_prestazione_medico_id_fkey" FOREIGN KEY (medico_id) REFERENCES sc_personale(id) ON DELETE SET NULL
    "eroga_prestazione_paziente_id_fkey" FOREIGN KEY (paziente_id) REFERENCES res_partner(id) ON DELETE SET NULL
    "eroga_prestazione_prestazione_ids_fkey" FOREIGN KEY (prestazione_ids) REFERENCES tipo_prestazione(id) ON DELETE SET NULL
    "eroga_prestazione_studio_id_fkey" FOREIGN KEY (studio_id) REFERENCES res_studio(id) ON DELETE SET NULL
    "eroga_prestazione_tecnico_id_fkey" FOREIGN KEY (tecnico_id) REFERENCES sc_personale(id) ON DELETE SET NULL
    "eroga_prestazione_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL
Referenced by:
    TABLE "prestazione_personale_rel" CONSTRAINT "prestazione_personale_rel_eroga_prestazione_id_fkey" FOREIGN KEY (eroga_prestazione_id) REFERENCES eroga_prestazione(id) ON DELETE CASCADE

In model_B (eroga_prestazione), the reference to model_A (prestazioni_preventivate) is prestazione_preventivata_id

Much of this is created through the Odoo ORM, I don't know why prestazione_preventivata_id is not a foreign key in model_B (eroga_prestazione).

  • maybe model_A could be a materialized view and there could be a bunch of triggers to update it everytime that's needed ? Commented Nov 3, 2023 at 12:35
  • 1
    You will minimally need to provide the View definition for model_A and show which fields relate it to model_B, in order for anyone to be able to answer you.
    – J.D.
    Commented Nov 3, 2023 at 13:08
  • 1
    @J.D.thank you, I edited the question and added some context Commented Nov 5, 2023 at 10:15
  • In the view, prestazione_preventivata is generated (thus multiplying rows), but not included in the output. Doesn't seem to make sense. Also prestazioni_preventivate.id is generated with row_number() without ORDER BY, so it's an arbitrary number. Also doesn't seem to make sense. Commented Nov 6, 2023 at 2:56
  • As for prestazione_preventivata not being included in the output, I'm not sure what that means. Does it mean you want a describe on it too ? @ErwinBrandstetter Commented Nov 6, 2023 at 8:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.