1

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?

6
  • I'm wondering how close ChatGPT would get you. (No, I'm serious) If I understand your task, please start by implementing your logic as raw SQL in your phpMyAdmin or WorkBench or whatever you use. Then work toward translating that into PHP/Joomla4 query code. By presenting that progress, your question requirements will be smaller and your question may become more attractive to contributors. Feb 7, 2023 at 23:06
  • 1
    @mickmackusa I have rephrased my question, but I'm unable to implement my logic as raw SQL as you suggest as I don't know how... that's why I'm here asking the question! Similarly, I'm asking how I then translate that into PHP/Joomla4 query code.
    – Gillian
    Feb 8, 2023 at 9:58
  • Fair enough. I want your experience on JSE to be a good one, so I will intend to guide you toward crafting an attractive question. First, I will caution you to not write too much text in your question body -- long, wall-of-text questions can deter some volunteers from reading (and subsequently answering). Second, it is perfectly acceptable to break your task down into separate questions. Just refer to previous questions for context. Perhaps on this page, edit to ONLY ask for how to craft the raw SQL -- this is a good, narrow, attractive question for contributors. Feb 8, 2023 at 10:27
  • Thanks, @mickmackusa. I have started on phpMyAdmin and got a sort of a result but have come across a problem, so I'll do as you say and edit this question down to the phpMyAdmin query first.
    – Gillian
    Feb 8, 2023 at 15:10
  • I worked it out - mostly. 1: the join was on the incorrect columns, it should have been '#_content.id = #_content_frontpage.content_id' and not '#__content.asset_id...' 2: this code to create the table, leaving out 'ordering' in the second table: CREATE TABLE #_content_with_featured SELECT * FROM #_content, LEFT JOIN #_content_frontpage ON #_content.id = #_content_frontpage.content_id; 3: 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?
    – Gillian
    Feb 9, 2023 at 11:27

1 Answer 1

2

Unless you are magically updating the new table with fresh data then you will only see the data from the initial select statement. In this case you have a few options

  1. populate the new table with updated data via a cron or other automated process to load the new / updated data from your source tables
  2. create a user driven button to refresh the data periodically
  3. create a view in your mysql db that will dynamically show the results from your select when accessed.

Options 1 & 2 will allow you to query the results in the #_ format in Joomla as it is a genuine table as far as Joomla is concerned.

Option 3 should also allow you to use the #_ format to query data as long as you name your view with the table prefix as though it is a regular table, but you may need to create your view directly in the db initially.

1
  • Thank you, @sirobertson. That's very helpful. On reading your answer and the 'Selecting data using JDatabase' document, I now realise I should be able to compare the two tables directly from within my blog_item.php override. I'm struggling with that, now, so I'm going to start a new question on that topic (if I can't find the answer after more searching).
    – Gillian
    Feb 10, 2023 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.