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 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.