I have this table in my DB that is represented by multiple schemas with Ecto. In terms of structure they are all consistent, but they may at times draw data from different sources. For example, I have a schema Content that is the most generic of all, then CourseVideo and StandaloneVideo which are more specific to video contents.
On CourseVideo I get the author data by querying its association, i.e. what is the author in the Course schema that has a CourseVideo associated with.
On StandaloneVideo, on the other hand, I get this data from a different source; the information of the former would be on a, say, course_creators table and the latter on content_creators table. When queried, though returning different structs, the field authors has the exact same structure for both; a list of Creator.
My question is, if I use the more general schema Content, is there a way I can conditionally query these different formats? I have some very (for me at least) complex queries that I worked very hard on making them modular so it suits different scenarios in the application, I was wondering if it’s possible to, let’s say, have a field type on the Content and then conditionally direct which query pipeline it will follow, or the only way to achieve different result based on data within the query is on select using IF WHEN with fragment.
Can you show a bit of detail? Maybe the schemas in question and the data structure of the desired output.
If you know at the application level what sort of thing you’re querying for, can you add a function in your query pipeline that takes the type as an arg, and adds the type specific apart to the query?
If i understand correctly, based on example, you only have 2 distinct DB table
But on ecto schema, beside these two distinct table, you also have Content table (which i assume a kind of view on SQL? Or maybe just a schema without a source?) which provide kore generalized view on course video and standalone video.
If i am correct understanding the situation, then you could probably do conditional query pipeline on entity level, like:
def get_author(content) do
If content.type == standalone do
Get_author_standalone(content.id)
Else
Get_author_course(content.id)
End
end
But to do it on composable query level/ecto query level would probably hard, the reason is because on query level you would need to account for all possibility of Content type. So you would probabbly need the select if when (alternatively, you might consider lateral join).
However if you could guarantee the source of content to be only from a singular source (either from standalone or from course), and has information which source is it, you could probably also do that on query level (for example, you has named binding to indicate which source this content come from). For example:
def with_author(query) do
if has_named_binding?(query, :course_video) do
... Do ecto query for author for course video
else
... Do ecto query for author for standalone video
end
end
