1

Update: Component Code (not normalized) can be found here: Link

For my First J4 Component i've now implemented Multi Cat Option by saving the selected categories into a comma separated list (text) into the database. On edit it works so far. In the List of Items i would also like to show the selected Categories for each item. The queries i've tried so far are all not working. Is it correct that for the IN operator the comma-separated list has to be exploded inside the query, right? Unfortunately it is not working as expected because of my mmisunderstanding on how to correctly build a subquery for this task: a table ==> #__extension b table ==> #__categories

a.categories contains comma separated list of id's from b table

a.id   | a.categories   |     | cats.id    | cats.title
------------------------      -------------------------
1      |1,2,3           |     | 1          | category A
2      |2,3             |     | 2          | category B
...                           | 3          | category C
                               ....

// inside my getListQuery function of my Model:

// Select the required fields from the table.
        $query->select(
            $db->quoteName(
                [
                    'a.id', 'a.name', 'a.alias', 'a.access',
                    'a.catid','a.categories','a.published', 'a.publish_up', 'a.publish_down',
                    'a.language'
                ]
            )
        );

...
$categoriesSubQuery = $db->getQuery(true)
 ->select($db->quoteName('cats.title'))
 ->from($db->quoteName('#__categories', 'cats'))
 ->where( $db->quoteName('cats.id') . ' IN (' . $db->quoteName('a.categories') . ')');

$query->select('(' . $categoriesSubQuery . ') AS ' . $db->quoteName('categoriesArr'));

...

Gives me Only the First item from the categories field (var_export):

     'name' => 'ITEM A',
     'alias' => 'a',
     'access' => 1,
     'catid' => 0,
     'categories' => '19,20',
     'published' => 1,
     ...
     'categoriesArr' => 'CATEGORY A',
     ...

I understand that the SubQuery could be the solution to "get all Categories from comma separated list of category id's" but i simply dont know how.

Main Goal is to have an Array of Objects / Strings of each category

Update: So based on the comments i'm on the way to normalize my structure and implement a #__component_item_category table including id,item_id,category_id - now in the Item Model i need to do the following step by step, is it ok to do so?

$categories = $this->categoriesToArray($categories);

        $db = Factory::getDbo();
        $query = $db->getQuery(true);

// Get all registered categories for this item from normalized table $array

// unset all still given/found categories from $array foreach $array

// check for leftover categories and delete them (if array > 0) foreach where itemid and category  (or IN?) $db->delete

// if categories > 0 --> Store the new categories (foreach)

Is it OK to do that all (db queries) in the model? i don't think so - a public function in the ItemTable Class would be better and implement class by use statement and a call of this function right?

6
  • There's no point in struggling the way you do. What you need is probably the "ManyToMany" database relationship. It's something like 'tag' system. I'll give you a simple example. You will have posts table (id, name), and tags/categories table (id,name). You need one more extra table to link them all. It might look like posts_tags table (id, post_id, tag_id). I think you can still use built in category table but you need extra table for storing multiple categories. I'm not quiet sure, but maybe Joomla has implemented similar behavior in com_tags.
    – Elboyler
    Commented Oct 22, 2022 at 21:39
  • How to Use IN Clause in Joomla Query or is this a FIND_IN_SET() problem and/or is this a problem with every denormalized db design? Commented Oct 22, 2022 at 22:19
  • Denormalized db design? I try to normalize it :) com_fields tends to use intermediate table, so why not to try it out?
    – Elboyler
    Commented Oct 23, 2022 at 0:03
  • I can also point to How to relate comma-separated ids from one table to the ids of another table? which includes a reference to some leisure reading: Is storing a delimited list in a database column really that bad? Commented Oct 23, 2022 at 2:52
  • yeah normalization was also my first approach but i struggeled with the pitfall how to reconize if a category got removed from an item. normalization would mean i have to check on save which links are existing and remove invalids leave the still actives and add the new ones - is there no easier way?
    – Marco
    Commented Oct 23, 2022 at 4:48

0

Your Answer

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

Browse other questions tagged or ask your own question.