How to show summary table with other table data in a row?

Question

I have following MySQL scheme :

Method table: PK id, name, comment
Okp table: PK id, FK method_id, comment
Tnved table: PK id, FK method_id, comment

Okp --many to one-->  Method   <-- many to one-- Tnved

Image representation:

I'm need to show HTML summary table from methods. But each method (each row) could have many data from other tables in fields and I'm need to show them all.

It looks like this:

    Methods summary
+-----+-----------+--------------+---------------+-----+---------+
| id  | name      | All OKP data | All TNVED data| ... | Comment |
+-----+-----------+--------------+---------------+-----+---------+
| 1   | Cloth 1   | 841000       | 5007000000    | ... | Special |
|     |           | 842000       | 5111000000    |     |         |
|     |           | 843000       | 5112000000    |     |         |
|     |           | 844000       | ... much more |     |         |
|     |           | ...much more |               |     |         |
+-----+-----------+--------------+---------------+-----+---------+
| 2   | Game 76   | 259000       | 6100000000    | ... | Nice    |
|     |           | 816700       | 6200000000    |     |         |
|     |           | 880000       | 6400000000    |     |         |
|     |           | ...much more | ...much more  |     |         |
+-----+-----------+--------------+---------------+-----+---------+
| ... | ...       | ...          | ...           |     | ...     |
+-----+-----------+--------------+---------------+-----+---------+
| 999 | T-shirt 3 | 831701       | 6302600000    | ... | Bad     |
+-----+-----------+--------------+---------------+-----+---------+

I'm tryed to use SQL JOIN but it looks monstrously with multiple redundancy. So i don't know how to use queries better.

I'm solved it with PHP by recieving related data for each row with separate queries, but this solution is too slow. (In fact i have 1000+ rows).

SO how to query and show such data?

I'm using following method to get information from DB:

//Model-file pseudo-code
$result = array();
$methods = $this->db
    ->select('*')
    ->from('method')
    ->get()
    ->result();
$i = 0;
foreach ($methods as $method){
    $result[$i]['method_t'] = $method;
    $result[$i]['okps'] = $this->db
        ->select('*')
        ->from('okp')
        ->where('method_id', $method['id]')
        ->get()
        ->result();
    $result[$i]['tnveds'] = $this->db
        ->select('*')
        ->from('tnved')
        ->where('method_id', $method['id]')
        ->get()
        ->result();
    //so on
    $i++;
}

I'm using following method to show summary table:

//View-file pseudo-code
//Table header
foreach ($result as $method) {
    echo '<tr>';
    echo '<td>' . $method['method_t']['id'] . '</td>';
    echo '<td>' . $method['method_t']['name'] . '</td>';
    echo '<td><ul>';
    foreach ($method['okps'] as $okp) {
        echo '<li>' . $okp['id'] . '</li>';
        //in fact much more data from $okp with separate template
    }
    echo '</ul></td>';
    echo '<td><ul>';
    foreach ($method['tnveds'] as $tnved) {
        echo '<li>' . $tnved['id'] . '</li>';
    }
    //in fact much more data from $tnveds with separate template
    echo '</ul></td>';
    //so on
    echo '</tr>';
}
echo '</table>';

Answer 1

The basic issue is the semi-cartesian product. If there are five rows from "okp" (for given m_id) and six rows from "tnved", a join operation is going to match each row from "okp" with each row from "tnved", a total of thirty rows. Six copies of each "okp" row, and five copies of each "tnved" row.

If there are three, four, five tables involved in the join, and each has a dozen or more rows... the resulting conglomeration of duplication becomes unwieldy.

---

There are several approaches to taming the problem.

If I was going to run a query against "method", and process each row from in a loop, one "m_id" at a time...

For each row from "method", I would execute another single query to get back all of the rows from "okp", "tnved", et al. by combining the individual queries with a UNION ALL. This would eliminate the duplication of the rows. With an appropriate index defined e.g. "... on okp (m_id, id)", the queries should be pretty efficient.

For example:

  SELECT okp.m_id     AS m_id
       , 'okp'        AS src
       , okp.id       AS id
       , okp.comment  AS comment  
    FROM okp
   WHERE okp.m_id = ?
   ORDER BY okp.m_id, okp.id

   UNION ALL

   SELECT tnved.m_id    AS m_id
        , 'tnved'        AS src
        , tnved.id       AS id
        , tnved.comment  AS comment
    WHERE tnved.m_id = ?
    ORDER BY tnved.m_id, tnved.id

We use a UNION ALL and not a UNION, so the results are just concatenated together, and avoid the overhead of a sort and removal of duplicate rows.

Note that we include a "src" discriminator column. Our code can use the value in this column to determine which query returned it. In this case, each query is from a single table, so we can just identify the table the values are from.

If one of the tables has more columns to return than the others, we add "dummy" expressions to the SELECT list of the other queries. That lets us satisfy the requirements of the UNION ALL where all the sets being combined must have the same number of columns, and the same datatypes.

This approach eliminates a lot of the duplication we get with a semi-cartesian product. This approach requires an "extra" query for each m_id that we process, but if we're only putting 20 m_id on a page, it's only 20 queries to run.

---

as far as the php for this... execute the query, supplying m_id value for each of the bind placeholders. fetch the results, and each row into the appropriate (emptied) array. rows from "okc" into an $okc[] array, the rows from "tnved" into a $tnved[] array.

Looks like that setup would work for the current "outputting" code.

As long as you're processing a limited number of m_id, and there are appropriate indexes available, this approach can be reasonably efficient.

Answer 2

Why not to use a single SQL query, but be careful when using INNER JOIN. Because you can have for method #1 related rows in okp table, but haven't any rows for method #1 in tnved table and vice versa.

Beware: in query and php code below I'm using name column instead of comment column for okp and tnved tables.

So, SQL should be smth like this:

SELECT m.id, o.id AS oid, o.name AS oname, t.id AS tid, t.name as tname, m.comment 
FROM 
(method AS m LEFT JOIN okp AS o ON m.id = o.method_id) 
LEFT JOIN tnved AS t ON m.id = t.method_id
ORDER BY m.id

After execution of query above you'll have smth like this: https://drive.google.com/a/thepaydock.com/file/d/0B3C0Ywfdde5Fa2lPc0FBdlZENG8/view?usp=drivesdk

Next iterate query results rows with PHP:

$output = array();
foreach($results AS $id => $method) {

    $output[$id]['okps']   = array();
    $output[$id]['tnveds'] = array();

    if(!is_null($method['oid'])) {
        $output[$id]['okps'][$method['oid']] = array(
            'name' => $method['oname'];
        );
    };

    if(!is_null($method['tid'])) {
        $output[$id]['tnveds'][$method['tid']] = array(
            'name' => $method['tname'];
        );
    } 
}

Next render $output array where $output array key is method_id.

Also php code above can be refactored. I've provided basic example.

Answer 3

Perhaps I'm missing something due to my lack of php skills, but I see no reason why you can't get all the records in one shot and then just use the php to show it how you want. Here's an example, but the php can only best be described as pseudo-code as I have little experience with it:

select
  m.id as m_id,
  m.name as m_name, 
  m.comment as m_comment,
  o.id as o_id,
  o.comment as o_comment,
  t.id as t_id, 
  t.comment as t_comment
from
  method m
  inner join okp o
    on m.id = o.method_id
  inner join tnved t
    on m.id = t.method_id
order by m.id, o.id, t.id;

For the php, something like the following. I omitted the tvned stuff as you can add that in by just copying the model of the okp part.

$is_first=true;
$m_id_last = 0;
$m_id = 0;
$o_id_last = 0;
$o_id = 0;
$o_str = "";
foreach ($result as $method) {
    $m_id_last = $m_id;
    $m_id = $method['m_id'];
    if ((!is_first) && ($m_id_last != $m_id)) {
        echo '<tr>';
        echo '<td>' . $m_id . '</td>';
        echo '<td>' . $method['name'] . '</td>';
        echo '<td><ul>';

        echo o_str;

        echo '</ul></td>';
        echo '</tr>';

        $o_str = "";
    }
    $o_str .= '<li>' . $method['o_id'] . '</li>';
    $is_first=false;
}