Zend DB Join 2 rows from same table

Question

I have the following table.

---------------------------------------------
check_id | action_id | user_id |         dt |
---------------------------------------------
       1 |         1 |       6 | 2011-09-17 |
       2 |         1 |       6 | 2011-09-18 |
       3 |         3 |       6 | 2011-09-19 |
       4 |         3 |       6 | 2011-09-20 |
---------------------------------------------

I would like to query this table and get the following result.

-----------------------------------------------
action_id | user_id |   dt_start |     dt_end |
-----------------------------------------------
       1 |        6 | 2011-09-17 | 2011-09-18 |
       3 |        6 | 2011-09-19 | 2011-09-20 |
-----------------------------------------------

So I'm using the following query.

$checks->select()
->from(array('c1' => 'checks'), array('dt as dt_start')
->joinLeft(array('c2' => 'checks'), 'c1.action_id = c2.action_id', array('dt as dt_end')
->where('c1.user_id = ?', $userId)
->group('c1.action_id')

But this gives me the following result.

-----------------------------------------------
action_id | user_id |   dt_start |     dt_end |
-----------------------------------------------
       1 |        1 | 2011-09-17 | 2011-09-17 |
       1 |        3 | 2011-09-19 | 2011-09-19 |
-----------------------------------------------

Can someone tell me what I am doing wrong?

It's not immediately clear to me what SQL you are trying to create via Zend_Db_Select. Can you add the SQL to the question? Also, you can use the Zend_Db_Select::toString method to print the SQL that will be executed when queried, which may help you debug. — David Snabel-Caunt, Sep 20 '11 at 21:20

score 1 · Accepted Answer · answered Sep 20 '11 at 21:20

Try this query, without the group by:

SELECT *
FROM checks c1 LEFT OUTER JOIN checks c2 ON c1.action_id = c2.action_id 
WHERE c1.user_d = 6

You'll see that you get matches where c1.dt < c2.dt, which is what you want. But you also get matches where c1.dt > c2.dt, and where c1.dt = c2.dt. That is, the self-join includes results where c1 and c2 point the very same row.

Then you use a GROUP BY that collapses multiple rows into one, but MySQL chooses an arbitrary row from the group. (This is an ambiguous query, and if you SET SQL_MODE='ONLY_FULL_GROUP_BY' you'd get an error.)

So your self-join and GROUP BY only returns c1 and c2 that are actually the very same row.

To fix this, you should add a condition that c1.dt < c2.dt.

$checks->select()
->from(array('c1' => 'checks'), array('dt as dt_start')
->joinLeft(array('c2' => 'checks'), 
           'c1.action_id = c2.action_id AND c1.dt < c2.dt', 
           array('dt as dt_end')
->where('c1.user_id = ?', $userId)

You probably don't need the GROUP BY at all in that case, assuming that you don't have multiple starts and ends for each action.

By the way, this type of complexity is one reason that it's usually recommended to store event start/end data in a single row, with the start in one column and the end in a second column.

Using a GROUP BY action_id gives me the results I need. I could also use a joinRight but that also gives me results with only one row. I need results when two rows share the same action_id. Maybe storing the start and end date in a single row is better solution. I'm building a check-in/check out system. — Stuiterbal, Sep 20 '11 at 21:54
A right join doesn't return the row where an action_id is not yet shared with another row. I need those too. So a LEFT JOIN combined with a GROUP BY does the trick. — Stuiterbal, Sep 20 '11 at 22:04
That's fine, but did you get the part I covered about testing fot c1.dt < c2.dt? — Bill Karwin, Sep 20 '11 at 22:27

Zend DB Join 2 rows from same table

1 Answers1