ERM - OpenAccess derived table for party 'openaccess_party'
Content
Overview
The purpose of this derived table is to show informations about the records in the table "folio_oa.party" and their linked tables and reference data.
Github Issue
https://github.com/folio-org/folio-analytics/issues/786 (ERM - Create derived table 'openaccess_party' for Metadb)
Attributes for the derived table
Folio Table | Folio Attribute | Description | Datatype | Sample Output | FK (From table) | PK (To table) | Notes |
|---|---|---|---|---|---|---|---|
folio_oa.party | p_id | UUID | ee25ff43-a800-4135-83a5-10ec374c0aa8 | Table: folio_oa.party Attribute: p_id | Table: folio_oa.request_party Attribute: rp_party_fk --------- Table: folio_oa.alternate_email_address Attribute: aea_owner_fk --------- Table: folio_oa.party_address Attribute: padd_owner_fk | ||
folio_oa.party | version | INTEGER | 0 | ||||
folio_oa.party | p_title | TEXT | |||||
folio_oa.party | p_family_name | TEXT | Kohler | ||||
folio_oa.party | p_given_names | TEXT | Lukas | ||||
folio_oa.party | p_full_name | TEXT | Lukas Kohler | ||||
folio_oa.party | p_orcid_id | UUID | |||||
folio_oa.party | p_main_email | TEXT | blubb@example.com | ||||
folio_oa.alternate_email_address | aea_id | UUID | 584deb6d-3abc-45be-be94-94e777e43052 | ||||
folio_oa.alternate_email_address | aea_version | INTEGER | 0 | ||||
folio_oa.alternate_email_address | aea_email | TEXT | blubb2@example.de | ||||
folio_oa.alternate_email_address | aea_owner_fk | UUID | 731adb4c-52ec-4ac8-9ac6-4ae0938aef6e | Table: folio_oa.alternate_email_address Attribute: aea_owner_fk | Table: folio_oa.party Attribute: p_id | ||
folio_oa.party | p_phone | TEXT | |||||
folio_oa.party | p_mobile | TEXT | |||||
folio_oa.party | p_street_address_fk | UUID | Table: folio_oa.party Attribute: p_street_address_fk | Table: folio_oa.party_address Attribute: padd_id | |||
folio_oa.party | p_institution_level_1_fk | UUID | 8aaa80aa85c4472e0185e3314006000c | Table: folio_oa.party Attribute: p_institution_level_1_fk | Table: folio_oa.refdata_value Attribute: rdv_id | ||
folio_oa.refdata_value | p_institution_level_1_value | TEXT | medizinische_fakultat | ||||
folio_oa.refdata_value | p_institution_level_1_label | TEXT | Medizinische Fakultät | ||||
folio_oa.party | p_institution_level_2 | TEXT |
Linking approach
folio_oa.party
LEFT JOIN folio_oa.alternate_email_address ON alternate_email_address.aea_owner_fk = party.p_id
LEFT JOIN folio_oa.refdata_value AS p_institution_level_1 ON p_institution_level_1.rdv_id = party.p_institution_level_1_fk
Explanation to special relations between tables
Relation derived tables "openaccess_publication_request" and "openaccess_party"
You can link the derived tables "openaccess_publication_request" and "openaccess_party" via the UUIDs
openaccess_publication_request.pr_request_contact_rp_party_fk = openaccess_party.p_id
openaccess_publication_request.pr_corresponding_author_rp_party_fk= openaccess_party.p_id
In practice, the link must be made twice from the derived table "openaccess_publication_request" to the derived table "openaccess_party".
openaccess_publication_request
LEFT JOIN openaccess_party AS openaccess_party_request_contact ON openaccess_party_request_contact.p_id = openaccess_publication_request.pr_request_contact_rp_party_fk
LEFT JOIN openaccess_party AS openaccess_party_corresponding_author ON openaccess_party_corresponding_author.p_id =
openaccess_publication_request.pr_corresponding_author_rp_party_fk