While working on some new functionality, I created some tasks to keep track of what I still needed to do for that feature, and I noticed some of them wheren’t appearing when I went to the list. As I wasn’t specifically testing this at the time, I wasn’t overly sure on what the differences where with some of my tasks, so I have no idea what caused the issue. The results also varied based on what page you view it. Some of the results where shown on the main tasks page, but not on the tasks detail page and vice versa.
After having created a new, clean account and project, here are my findings:
- Tasks created on the tickets page and assigned to myself work fine
- Tasks created on the tasks page and assigned to myself throw an undeclared variable error when added via AJAX and upon refresh, it’s not there. This would imply there as a problem with the join, although it doesn’t throw a SQL error
- The above fails to include the user ID the task is assigned to – Shouldn’t fail as it’s a left join although the default user ID is 0, which has no match!
- Having corrected the above so the inserted row has the user ID, it still fails as before
- At this point, everything in the database looks like it should (aka all the columns have the correct or valid values)
- Tasks added from the tasks page are now added to the list correctly with AJAX and no longer have the undefined variable error – The problem was that when I saved the task, I used that same data to populate the new row, but this only contains the user ID, not the users name (Up until this morning, it just had the ID in the row, not the name, which is why it never happened before now). However it’s not visibl after refreshing or on any of the other views
- After some more debugging with the raw SQL (easier to debug raw SQL then active record) in Navicat (better for debugging then PHP My Admin) I discovered that the problem wasn’t from the joins but from “SUM(times.duration) AS has_time”. As SUM is an aggregate function, it takes all the rows and aggregates them into one (which is why the first task was added without a problem)
- The solution was to add a group by clause, grouping the results on the ID of the task, this would force each one to be returned seperately
Everything now works fine =) Even the sum of the times spent on each task, which originally caused the problem works as expected
