How to combine or merge SQL results into one row

Question

I have a Joomla list page that I am trying to build each row from multiple tables that I haven't been able to figure out how to do efficiently with MySQL and would like some assistance. I suspect the answer is in some sort sub-query but I haven't been able find an example or explanation that I can relate to.

What I am trying to achieve is to have a table containing Entities(people, companies, etc.) and store their relationship with other elements either in the same table or other components tables, e.g. membership & user id, in a 'relationships' table and display the fields of those various relationships where required.

This is what I want to achieve, a single line per Entity with the extra fields filled in from the related tables.

This is what I am getting with my basic Join Left approach is multiple lines relative to how many entries the relationship table has for each entity.

There are three tables involved in this example;

The Entity table

The Membership table

And the Relationship table

The model is intended to read through the entity table and for each entity look up the relationship table for entries matching the entities id and then retrieve the extra fields for that entity from the other table(s). The 'source' column is just there for reference in this example to indicate which table is going to be read.

Note the 'source' entity is a reference back to the same table used in the from statement and is used to get the Parent Entity, in this example the Individual entity has a relationship back to the Company, the entity Barney and Fred work for. I don't believe referencing the same table will alter the solution, I could have just used a fourth table, but being able to handle the relationship back to other entities is a key requirement of what I am tying to build.

The current statements I have been using

$query->select(
            $db->quoteName('e.id','id'),
            $db->quoteName('e.entity'),
            $db->quoteName('p.entity'),
            $db->quoteName('m.status'),
            );

$query->from($db->quoteName('#__entities', 'e'));
$query->leftjoin($db->quoteName('#__relationships) . ' AS r ON r.entity_id = e.id');
$query->leftjoin($db->quoteName('#__entities) . ' AS p ON p.id = r.target');
$query->leftjoin($db->quoteName('#__memberships) . ' AS m ON m.user_id = r.target');

I did create this sub-query thinking it might be able to used somewhere but couldn't see how to use in the ablove.

$query_parent->select('p.entity_name');
$query_parent->from($db->quoteName(#__relationships, 'r'));
$query_parent->leftjoin($db->quoteName(#__entities) . ' AS p ON p.id = r.target');
$query_parent->where($db->quoteName('r.source') . " = 'entities'");

I am trying to avoid having to use a foreach type loop or merging records in the array to achieve all the fields on the one line as in the first image as I am hoping MySQL can do it for me.

Answer 1

There is a solution using pure MySQL, but it may get excessively complicated if the number of tables or sources increases and I'm not sure it's the most efficient approach.

You need to join the relationships table to itself for each source that you want to use. Then use conditional left joins to pull it all together.

Plain MySQL example:

    SELECT DISTINCT entities.entity, parent.entity, memberships.status
      FROM entities
INNER JOIN relationships rel1 ON entities.id = rel1.entity_id
INNER JOIN relationships rel2 ON rel1.entity_id = rel2.entity_id AND rel1.id <> rel2.id
 LEFT JOIN entities parent ON (rel1.source = 'entity' AND parent.id = rel1.target) OR (rel2.source = 'entity' AND parent.id = rel2.target)
 LEFT JOIN memberships ON (rel1.source = 'membership' AND memberships.user_id = rel1.target) OR (rel2.source = 'membership' AND memberships.user_id = rel2.target);

Using Joomla query would be something like:

$query->select('DISTINCT entities.entity, parent.entity, memberships.status')
      ->from('entities')
      ->join('INNER', $db->qn('relationships', 'rel1'), 'entities.id = rel1.entity_id')
      ->join('INNER', $db->qn('relationships', 'rel2'), 'rel1.entity_id = rel2.entity_id AND rel1.id <> rel2.id')
      ->join('LEFT',  $db->qn('entities', 'parent'), '(rel1.source = 'entity' AND parent.id = rel1.target) OR (rel2.source = 'entity' AND parent.id = rel2.target)
      ->join('LEFT',  $db->qn('memberships'), '(rel1.source = 'membership' AND memberships.id = rel1.target) OR (rel2.source = 'membership' AND memberships.id = rel2.target);