1

This issue is extending of the question(s) by the link joomla.stackexchange.com/a/9836/4042 but with condition when child record has not only ONE parent (parent record) but MANY.

With two left joins from the accepted answer by that link it works perfectly if child record has only ONE parent (or many childs are bound to the same ONE parent). But what about the case when one child record is bound to multiple parents (parent records)? I.e:

#__records:

id  name
1   Child1
2   Parent1
3   Parent2

#__parents:

id  child_id  parent_id
1       1         2
2       1         3

Using that two left joins for this case I get the same child record twice in the list of records at the back-end for each parent_name:

ID  CHILD    PARENT
1   Child1   Parent1
1   Child1   Parent2

So there are two questions:

1. How to push the parent_name to array() and to get the list of child records at the back-end of my component (if administrator/index.php?com_mycom&view=recordS) like:

ID CHILD   PARENT
1  Child1  Parent1, Parent2

2. How to get the field with array of parents in the form for editing a single record and how to (re)save the field in the DB (if administrator/index.php?com_mycom&view=record&layout=edit&id=1)? I try to add to my admin/models/forms/record.xml the field like:

<field 
    name="parentheader"
    type="list"
    label="COM_MYCOM_PARENT"
    description=""
    class="chzn-color-state"
    multiple="multiple">
    <option value="0">COM_MYCOM_SELECT_PARENT</option>
</field>

and to render it in tmpl with this one:

JHtml::_('formbehavior.chosen', 'select');
...
<div class="control-group">
   <div class="control-label">
      <?php echo $this->form->getLabel('parentheader'); ?>
   </div>
   <div class="controls">
     <?php echo $this->form->getInput('parentheader'); ?>
   </div>
</div>

But I get this output: enter image description here

It must be like the field for categories in the product editing panel of Virtuemart where I can bind a product to many categories:

enter image description here

What code should I add to my admin/models/forms/record.xml and admin/models/record.php (or to JTable class)?

  • I read and tried to understand but I didn't. Maybe I am a bit tired now. However this is a 3 in 1 question. It might be better having 1 specific question for 1 specific problem separately, with better explanations on what you are trying to do for each one. – FFrewin May 21 '15 at 12:27
  • I've updated the questions a little bit – stckvrw May 21 '15 at 14:29
2

I can help you out with the answer to Q1 (the SQL one).

Here's the SQL I used on sqlfiddle:

SELECT
  b.`child_id`,
  a.`name`,
  GROUP_CONCAT( c.`name` ORDER BY c.`name` SEPARATOR ', ') AS parents
FROM `a_parents` AS b
INNER JOIN `a_records` AS a ON a.`id`=b.`child_id`
INNER JOIN `a_records` AS c ON c.`id`=b.`parent_id`
GROUP BY b.`child_id`;

It assumes the following:

  • you're only interested in those children that have parents (so, the parent table is the "origin", if you like - grouping by child_id)
  • it should cater for more than 2 parents (on the assumption this is not about human ancestry, otherwise tripartite/polypartite parentage is way beyond the scope of stackexchange!)
  • the total length of the parents' names is not more than 1024 (if it were, you'd need to do something like:

    SET group_concat_max_len = 1024 * 10;

to set it to 10K, for example - this is done before the SELECT)

  • Alias c is used to allow mapping the parent's name to parent_id
  • Use of MySQL 5.5 or later - earlier versions may work too

The nub of the solution here is use of GROUP_CONCAT which avoid the need to do any PHP array processing to "convert" many rows to a single column.


As to the second question (Q2), you will probably need to write a custom field handler. I'm guessing as you don't say, but if you're using VM in the way you screenshotted, it looks like J2.5 (rather than J3), there are plenty of examples of where VM2 uses its own special fields (see administrator/.../elements for examples, if I recall correctly and perhaps also administrator/.../models/fields). The links here to writing custom back end fields might help

  • Thank you! Could you duplicate the query but according to the API Joomla? – stckvrw Jun 5 '15 at 8:18
  • And also there is the problem: if child records have no any parent they are not displayed – stckvrw Jun 5 '15 at 9:12
  • @stckvrw - yes, you may be right about the no parents, although the original question was phrased as "...not ONE parent.... but MANY". Perhaps you could clarify if you actually meant ANY number of parents including zero. I did state in my first assumption that the results assumed that there was at least one parent. – Mark Bradley Jun 5 '15 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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