[5.1] Multiload of article associations

[Denitz]

Summary of Changes

Load language associations for multiple articles and categories in a single query per each ArticlesModel::getItems() call.

These methods now can accept array of IDs and return results grouped by ID in this case. If a single int is provided as ID, the results are returned as they were before, full BC.

CategoriesHelper::getAssociations() CategoryAssociationHelper::getCategoryAssociations() AssociationHelper::getAssociations() Associations::getAssociations

Also, CategoriesHelper::getAssociations() is optimized and an extra query for each loaded category is removed by using $advClause param of Associations::getAssociations().

ArticlesModel::getItems() now collects the article IDs to load the assocations for, next loads all associations in the single query. Similar to I have already done with tags load.

Testing Instructions

1. Install 5.0-beta2-dev
2. Install German language
3. Install Sample multingual data
4. Clone each English article "Article (en-gb)" (edit it and click Save As Copy 2 times), we have 3 en-GB articles now.
5. Clone each German article"Beitrag (de-de)" (edit it and click Save As Copy 2 times, we have 3 de-DE articles now.
6. Edit 2nd and 3rd copies of articles and associate them. Now we have 3 en-GB articles asociated with 3 de-DE articles.
7. Enable Joomla debug
8. Enable "Associations" in com_content options (they can be enabled in homepage menu item settings but it doesn't work for some reason, looks like another issuse, hence we need to enable display of associations globally).
9. Load frontend, see list of articles from a category with 37 SQL queries executed in debug bar, for each articles we load associated article in a separate query.
10. Apply patch, see a single query which loads associated articles for all displayed articles.

Actual result BEFORE applying this Pull Request

37 queries in total, see a separate SQL query for each displayed article, like:

SELECT `c2`.`language`,CONCAT_WS(':', `c2`.`id`, `c2`.`alias`) AS `id`,CONCAT_WS(':', `ca`.`id`, `ca`.`alias`) AS `catid`
FROM `jos_content` AS `c`
INNER JOIN `jos_associations` AS `a` ON `a`.`id` = `c`.`id` AND `a`.`context` = :context
INNER JOIN `jos_associations` AS `a2` ON `a`.`key` = `a2`.`key`
INNER JOIN `jos_content` AS `c2` ON `a2`.`id` = `c2`.`id`
INNER JOIN `jos_categories` AS `ca` ON `c2`.`catid` = `ca`.`id` AND `ca`.`extension` = :extension2
...

Expected result AFTER applying this Pull Request

34 queries in total, see a single query per each ArticlesModel::getItems() call.

A new single query is like:

SELECT `c2`.`language`,`c`.`id` AS `pk`,CONCAT_WS(':', `c2`.`id`, `c2`.`alias`) AS `id`,CONCAT_WS(':', `ca`.`id`, `ca`.`alias`) AS `catid`
FROM `jos_content` AS `c`
INNER JOIN `jos_associations` AS `a` ON `a`.`id` = `c`.`id` AND `a`.`context` = :context
INNER JOIN `jos_associations` AS `a2` ON `a`.`key` = `a2`.`key`
INNER JOIN `jos_content` AS `c2` ON `a2`.`id` = `c2`.`id`
INNER JOIN `jos_categories` AS `ca` ON `c2`.`catid` = `ca`.`id` AND `ca`.`extension` = :extension2
...

Link to documentations

No documentation changes for docs.joomla.org needed No documentation changes for manual.joomla.org needed

[richard67]

I don't see it as a bug fix but as an improvement, so it should be made for the 4.3-dev branch and not the 4.2-dev branch.

[Denitz]

@richard67 Sorry, rebased.

[richard67]

Thanks.

[HLeithner]

This pull request has been automatically rebased to 5.0-dev. No new features will be merged into Joomla! 4.3 series. Joomla! 4.4 series is a bridge release to make migration from Joomla! 4 to 5 as smooth as possible.

[ceford]

This does not work for me - perhaps I have misunderstood the instructions. I selected the testing data All Frontend Views / Content Component / Article Category List. The debug queries show there are 10 Select calls as shown above both with and without the patch applied (my list length). With the patch applied there are 50 queries in total, without the patch there are 49.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/39189.}

[Denitz]

@ceford Sorry, I've updated the test instructions for 5.0.0-bet2-dev, we need to enable display of associated articles.

[ceford]

I have tested this item :white_check_mark: successfully on f8faf0136197e8bcd967e638fef842a340465710

I left my Testing data installed before installing Multilingual Sample data. Using a Category Blog for the Language specific articles I saw the query count go down from 47 to 43. There were no problems switching language or accessing the already installed English only menu items.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/39189.}

[richard67]

@Denitz Could you fix the scss-cs error reported by Drone here? https://ci.joomla.org/joomla/joomla-cms/70244/1/16

build/media_source/templates/administrator/atum/scss/blocks/_quickicons.scss 133:11 ✖ Expected nesting depth to be no more than 4 max-nesting-depth

Thanks in advance.

[Denitz]

@richard67 It's strange because I didn't modify this file. Anyway, I've updated the current version from current 5.0-dev

[HLeithner]

This pull request has been automatically rebased to 5.1-dev.

[fgsw]

I have tested this item :white_check_mark: successfully on 292a79c1b458a6f807f22b27a1cd1431b9f0f47c

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/39189.}

[Nuyonuyonoina]

Tested successfully

[richard67]

Tested successfully

@Nuyonuyonoina Please submit your result in the issue tracker https://issues.joomla.org/tracker/joomla-cms/39189 by using the blue "Test this" button, otherwise it is not counted. Just a comment is not sufficient.

[Hackwar]

I'm sorry to bother you with this again @Denitz, but could you fix the codestyle issues in this PR?

[Denitz]

@Hackwar updated

[HLeithner]

This pull request has been automatically rebased to 5.2-dev.

[Hackwar]

@fgsw @Nuyonuyonoina Can you test this again?