1

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.

![enter image description here

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.

![enter image description here

There are three tables involved in this example;

The Entity table enter image description here

The Membership tableenter image description here

And the Relationship tableenter image description here

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.

1
  • You may invite more responses if you create a ready-to-use demo at SQL fiddle sandbox or phpize.online. Apr 13, 2023 at 21:51

1 Answer 1

2

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);
1
  • Thank you for taking the time to reply. I suspect you are right that as the number of sources(table) expands it will become quite a beast to manage. Since posting the question I have fallen back to remove the reference back to the same entity table and replaced it with a parent_entity_id' field rather than use the relationship table. Thanks again.
    – Irata
    Apr 13, 2023 at 11:13

Your Answer

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

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