Two left joins and multiple parents for a single record

Question

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:

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

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

Answer 1

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