Refactor Object Query

[ktehranchi]

I'm creating this ticket to highlight that we can refactor the object query and the plexos parser get_model_data to avoid redundant data being queries and brought into the parser. (Mentioned here: NREL/R2X#38)

Issue:

The object_query.sql file has multiple CTE's to handle nested objects within the main object query. The query is correct and successful right now however has one small bug. The bug is that when querying an object that has datafile tagged to it- IF that datafile tag points to datafiles which have multiple scenarios, then the object will duplicate all those scenarios. Within the R2X plexos parser I correct this issue to replace all the duplicates with the single right value, but ideally we shouldn't have these duplicate fields

While it is possible to do the scenario filtering in the main SQL query- I don't think it makes sense from a readability and debugging stance as the codebase on r2x evolves to necessitate more query that join different types of objects. Also, the scenario filtering is has multiple layers to it involving scenario activated, bands, and default values which I think are more easily handled in python.

Suggested Solution:

Remove all CTE's from the object query, and perform any joins in python by querying time slices, variables, and datafiles as distinct objects which are then joined according to their tags. I already implemented this correctly for the Variable query (https://github.com/NREL/R2X/blob/9ddc2093bab830cb5a2942c9de4f4c90db3a9561/src/r2x/parser/plexos.py#L1090). I suggest we would need to redo this for Timeslices and Datafiles- at the same time removing the CTEs from the object_query.

The challenge, is that datafiles and timeslices use the same text table, so there is some work there to ensure the queries are done correctly.

[ktehranchi]

#18 Closes this