In order to query the exact featured status of an article in a blog override (i.e. currently featured, not pending or expired or not featured at all) I have created a query to join two tables and create a new one in a Joomla 4 database. #__content and #__content_frontpage [this is where the articles' featured_up and featured_down details are held], retrieving and combining nearly all fields from both tables, like this:
CREATE TABLE #_content_with_featured SELECT * FROM #_content, LEFT JOIN #_content_frontpage ON #_content.id = #_content_frontpage.content_id;
I then made 'id' (taken from #__content) the primary key. BUT... the table is not dynamic. Any new articles or changes to existing articles are not reflected in the new table.
What do I need to do? I think I might need to create a VIEW rather than a TABLE. But can I query a VIEW in a Joomla override?