[RESOLVED] Unknown column 'date' in 'order clause'

[eluser]Christopher Clarke[/eluser]
Hello,

Although I'm not asking for this column at all in my script, and it doesn't exist in my database, I keep getting the following error, seemingly at random, which makes my script fail to run.

Unknown column 'date' in 'order clause'

I'm not specifying $this->db->where('date') or similer in the queries which are doing this. I know adding the date column to the tables in question will fix this, but I'd rather not.

This is the method which in which the error is currently occuring:

http://pastebin.com/m5377772e

It does not occur in all contexts, this function works in other locations.

The context is:

http://pastebin.com/m3a73cd3d

What could be the issue?

Thanks in advance.

~ Chris

[eluser]Clooner[/eluser]
Without going through all your code at pastebin.... What are the sql queries generated by ActiveRecords? (you can view them using the profiler)

[eluser]Christopher Clarke[/eluser]
Since the page generated is a generic CI error document, the profiler doesn't work. The full output of the error is as follows:

Code:

A Database Error Occurred

Error Number: 1054

Unknown column 'date' in 'order clause'

SELECT * FROM (`stories`) WHERE `story_id` = 196 AND `story_approved` = '1' ORDER BY `date` DESC LIMIT 1

And the following output (from Profiler) is of a typical page... which also happens to make use of the query (and runs fine) that is causing the error on the page which is ouputing the error:

Code:

0.0006      SELECT *
FROM (`users`)
WHERE `screen_name` = 'chrisclarke'
LIMIT 1
0.0012      SELECT *
FROM (`stories`)
WHERE `story_slug` = 'school-for-adventurers'
AND `story_approved` = '1'
LIMIT 1
0.0011      SELECT *
FROM (`stories`)
WHERE `story_slug` = 'school-for-adventurers'
AND `story_approved` = '1'
LIMIT 1
0.0011      SELECT *
FROM (`reviews`)
WHERE `review_story` = 196
AND `review_approved` = '1'
AND `is_update` = '0'
ORDER BY `review_helpful_order` ASC
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '38'
LIMIT 1
0.0008      SELECT COUNT(`review_id`) as count FROM `revratings` WHERE `rating` = '1' AND `review_id` = '10'
0.0003      SELECT COUNT(`review_id`) as count FROM `revratings` WHERE `rating` = '2' AND `review_id` = '10'
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '36'
LIMIT 1
0.0004      SELECT COUNT(`review_id`) as count FROM `revratings` WHERE `rating` = '1' AND `review_id` = '2'
0.0003      SELECT COUNT(`review_id`) as count FROM `revratings` WHERE `rating` = '2' AND `review_id` = '2'
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '3'
LIMIT 1
0.0004      SELECT COUNT(`review_id`) as count FROM `revratings` WHERE `rating` = '1' AND `review_id` = '9'
0.0003      SELECT COUNT(`review_id`) as count FROM `revratings` WHERE `rating` = '2' AND `review_id` = '9'
0.0008      SELECT *
FROM (`comments`)
WHERE `listing_id` = '196'
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '1'
LIMIT 1
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '1'
LIMIT 1
0.0007      SELECT *
FROM (`users`)
WHERE `user_id` = '1'
LIMIT 1
0.0007      SELECT *
FROM (`users`)
WHERE `user_id` = '1'
LIMIT 1
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '1'
LIMIT 1
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '1'
LIMIT 1
0.0008      SELECT *
FROM (`library`)
WHERE `book_id` = 196
AND `library_user` = '1'
0.0006      SELECT *
FROM (`users`)
WHERE `user_id` = '41'
LIMIT 1
0.0005      SELECT *
FROM (`users`)
WHERE `user_id` = '1'
LIMIT 1

(Hmm, I see some opportunities for optimisation)

If I add the date field, the code works fine but as I said, I'm not referring to date to begin with.

[eluser]Christopher Clarke[/eluser]
Actually, this may be more useful - its the page causing the error if I cave in and add date to my table.

Code:

0.0006      SELECT *
FROM (`users`)
WHERE `screen_name` = 'chrisclarke'
LIMIT 1
0.0007      SELECT *
FROM (`stories`)
WHERE `story_id` = 196
AND `story_approved` = '1'
LIMIT 1
0.0006      SELECT *
FROM (`stories`)
WHERE `story_id` = 196
AND `story_approved` = '1'
ORDER BY `date` DESC
LIMIT 1
0.0005      SELECT *
FROM (`library`)
WHERE `book_id` = 196
AND `library_user` = '1'
0.0004      UPDATE `library` SET `book_status` = 'planned', `library_user` = '1', `book_rating` = 8, `chapters_read` = 5, `book_id` = 196 WHERE `library_user` = '1' AND `book_id` = 196

[eluser]Christopher Clarke[/eluser]
I worked it out. I thought the get novel query was referring to the get_novel method but it was actually referring to novel_exists(); which did indeed contain order_by(date). I'm not sure why that was, but its working now.

Thanks clooner for introducing me to the profiller. I had no idea that existed, but I can see how useful it is going to be.

~ Chris

RESOLVED