dpt1712
2012-05-10 22:20
浏览 96
已采纳

需要针对复杂报表的最佳结构化连接查询

I'm building a custom report module for my SaaS and need a little advice on a query I'm building (MySQL and PHP). I'm dynamically building a table, and each record that is returned will be one row on that table. It dynamically joins many different tables, and it all works great with the exception of 3 tables I need to join.

All the other tables are a 1-1 LEFT JOIN. For example if I'm querying the employee table and trying to find department_id, it joins just hunky dory. It'll join like this:

First | Last  | Dept
John  | Smith | Toy Department

The thing that makes this complicated is that those extra 3 tables have a 1-many join. It may also be important to point out that when I make this join it could pull anywhere from 2-50 rows of data. For example if I'm querying the employee table and need to join the clock in/out table it'd end up looking like this:

First | Last  | Time
John  | Smith | 8:00 am
John  | Smith | 12:00 pm
John  | Smith | 1:00 pm
John  | Smith | 5:00 pm

If I did a right join, the first/last name would be null but I'd still have multiple rows.

The reason why I want to keep them in one row is because with the table that I'm setting up when the report is pulled, when the it hits the that needs to use one of the 3 1-many tables, it needs to stay in the same data cell (this is out of my control, it just has to happen):

<table>
    <tr>
        <td>John</td>
        <td>Smith</td>
        <td>8:00 am<br />12:00 pm<br />..... </td>
    </tr>
</table>

I really really really want to avoid having to hit that cell and then running another query to pull out all the data. The reason being is that I could have a report run with thousands of rows with 25+ columns and it would run that same query 25x1000's of rows.

What I was hoping for was something like this when the data gets returned:

First | Last  | Time_1  | Time_2   | Time_3  | Time_4
John  | Smith | 8:00 am | 12:00 pm | 1:00 pm | 5:00 pm

Some thoughts I've had is to run a while loop in PHP and dynamically build the Joins for MySQL, but I run into the problem of the fact that I'd need to supply how many times to join the table to get that column right. I could technically do:

for($i = 1; $i < 50; ++$i) {
    $sql_fields .= "time_clock_{$i}.time AS Time_{$i},";
    $joins .= "LEFT JOIN time_clock AS time_clock_{$i} ON time_clock_{$i}.employee_id = emp.id";
}
$sql_fields = rtrim($sql_fields,',');

mysql_query("SELECT emp.field_blah, emp.field_blah2, {$sql_fields} FROM employees AS emp {$joins} WHERE condition = blah");

I'm just afraid that this would really cause some performance issues with MySQL and all the crazy joins I'd have in there. Anyone have a better idea? I'll entertain anything at this point.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doubaisui2526 2012-05-10 22:25
    已采纳

    No.

    Build two queries. One to pull all the people with all the 1:1 elements; then another to pull all your times. Then loop over times in PHP, stuffing them into the people's time array.

    打赏 评论
  • doushuangdui5419 2012-05-10 22:47
    <?php
        $query = mysql_query("SELECT .... FROM employees LEFT JOIN.... LEFT JOIN..."); //even include the one-many tables
        $i = 0;
        while($rs = mysql_fetch_array($query)) { //note I'm using array not assoc
            // echo the 1:1 table data
            // when I hit the 1:many field do something like this, yes $j will get reset when the pointer hits the next row....I need more time to think....
            $j = 1;
            while($j) {
                if($rs[$i + $j]['id'] == $rs[$i]['id']) {
                    echo $rs[$i + $j]['Time'];
                    ++$skip_fields;
                } else {
                    $j = 0;
                }
                ++$j;
            }
            // then use $skip_fields to skip $j number of rows that the duplicate employee ids are on and decrement the value as the pointer moves on.
            ++$i;
        }
    ?>
    

    Nevermind.....using the numeric value only hits the column not the row..... oh well. :)

    打赏 评论

相关推荐 更多相似问题