zinternal_CreateExtendedMainJointTables: Create Extended Main Joint Tables
In N1h1l1sT/dbautojoinr: Automatic Joining of SQL Tables

View source: R/CreateExtendedJoinMainJointTables.R

Get a list of all the Main tables in the database joined with all their relationships, Renaming Columns in certain tables according to "db_ColumnsOldNamesToNewNames" and then joining the renamed columns according to the relationship that exists on "db_forced_rel" for original column name (IF it exists). This way, if, for instance, both lstTables[[DIM_Employee]] and lstTables[[FACT_Work]] reference lstTables[[DIM_Site]], then Site can be renamed to MainSite and Extended_Join on lstTables[[DIM_Employee]] as this table refers to the main/base Site of the Employee, whilst lstTables[[FACT_Work]] refers to the Site the employee worked on at that point of time (row).

zinternal_CreateExtendedMainJointTables(
  main_joint_tables,
  db_fields,
  db_forced_rel,
  db_ColumnsOldNamesToNewNames,
  con = db$con,
  DeselectKeysIfIncludeFalse = TRUE,
  Verbose = TRUE,
  get_sql_query = TRUE
)

`main_joint_tables`	A named list of tibbles/DFs (usually given by create_main_joint_tables() as a SQL DB Pointer containing all user-selected fields plus needed ones for joins
`db_fields`	A DF with columns: "Include, KeyType, Table, Column, Type, RelationshipWithTable, RelationshipWithColumn, Transformation, Comment" about the User Selected fields and Relationships
`db_forced_rel`	A Named String Vector. The vector names MUST point to the main table to be used for the 1-Joint-Table as its LHS
`db_ColumnsOldNamesToNewNames`	A named List. Names correspond to the Table names, and the vectors inside will be used to renamed SQL Columns starting with db_ColumnsOldNamesToNewNames[i][j] to db_ColumnsOldNamesToNewNames[i][j+1] with j going from 1 to length of db_ColumnsOldNamesToNewNames[i] by 2
`con`	is a dbConnect DBI connection object to a SQL Database
`Verbose`	A Boolean. Verbose = TRUE will output the consecutive joins as they happen
`get_sql_query`	A Boolean. get_sql_query = TRUE will create/edit the db$sql_main_joint_tables that output the SQL Code for the tables

extended_main_joint_tables <-
  create_main_joint_tables(db_fields, db_forced_rel, FALSE, db$con) %>%
  zinternal_CreateExtendedMainJointTables(db_fields, db$con,
                                db_forced_rel = c(Hours_SiteID = "Site_SiteID", Hours_EmployeeID = "Employee_ID")
                                db_ColumnsOldNamesToNewNames =
                                  list(
                                       DIM_Employee = c(
                                                        c("Site_", "MainSite_")
                                                       )
                                       )
                                )

print(joint_table_With_extended_joins)