Writing SQL with HAVING vs WHERE clauses : DerekAllard.com

Writing SQL with HAVING vs WHERE clauses

November 14^th, 2006

UGH! So apparently I need one of those Matrix-style brain implants to help me remember SQL.

I'm writing this down now as both a personal reminder... and maybe I'll save you from the same fate. When writing the archive for this blog, I wanted to create friendly URLs, so that /blog/archive would display all posts, /blog/archive/2006 would show all of the posts from 2006, and finally /blog/archive/2006/11 would show all of the posts from November of 2006. Simple enough right?

But for many reasons, the dates are stored in my database as unixtimestamps. So before I can compare months and years, I need to select the records out of the database, and generate Months and Years. No problem! Here's a simple SQL snippet:

SELECT *, DATE_FORMAT(FROM_UNIXTIME(post_date), '%c') AS post_month, 
DATE_FORMAT(FROM_UNIXTIME(post_date), '%Y') AS post_year 
FROM posts

And it worked beatifully. But then I added the where clause

SELECT *, DATE_FORMAT(FROM_UNIXTIME(post_date), '%c') AS post_month, 
DATE_FORMAT(FROM_UNIXTIME(post_date), '%Y') AS post_year 
FROM posts 
WHERE post_month = 10

And it crapped out. Apparently the Matrix neglected to remind me that WHERE is used as criteria for which records to grab, and can't be run on aliased columns.

The solution? Use HAVING instead of WHERE. From the MySQL docs, "You may use Alias's if you use HAVING instead of WHERE this is one of the defined differences between the two clauses. Having is also slower and will not be optimized, but if you are placing a complex function like this in your where you obviously aren't expecting great speed.".

"The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.) " (http://dev.mysql.com/doc/refman/5.0/en/select.html)

I guess derived tables could also work... but m'eh, too hard for this solution.

edit: The final SQL and the Code Igniter Active Record query are in the comments below.

This entry was made on November 14^th, 2006 @ 17:41 and filed into How-To, PHP.

Comments

ian wrote on November 14^th, 2006 @ 18:41

So… what does the SQL look like when you put a HAVING clause in rather than a WHERE clause?

ian wrote on November 14^th, 2006 @ 18:41

So… what does the SQL look like when you put a HAVING clause in rather than a WHERE clause?

Anonymous wrote on November 14^th, 2006 @ 21:54

Did you use active record? Please show that too.

Anonymous wrote on November 14^th, 2006 @ 21:54

Did you use active record? Please show that too.

Derek wrote on November 14^th, 2006 @ 23:58

The completed SQL is
SELECT *, DATE_FORMAT(FROM_UNIXTIME(post_date), ‘%c’) AS post_month, DATE_FORMAT(FROM_UNIXTIME(post_date), ‘%Y’) AS post_year FROM posts HAVING post_year LIKE ‘2006’ AND post_month LIKE ‘10’ ORDER BY post_date desc
I did use Code Igniter’s Active Record class to generate it. Here’s what that looked like.
function getAllPosts($year = ‘%‘, $month = ‘%‘) { $this->db->select (“*, DATE_FORMAT(FROM_UNIXTIME(post_date), ‘%c’) AS post_month, DATE_FORMAT(FROM_UNIXTIME(post_date), ‘%Y’) AS post_year”); $this->db->orderby(‘post_date’, ‘desc’); $this->db->having(“post_year LIKE ‘$year’”); $this->db->having(“post_month LIKE ‘$month’”); return $this->db->get(‘posts’); }

Ruud wrote on November 15^th, 2006 @ 3:14

If you don’t like using HAVING, try this

SELECT * FROM posts WHERE YEAR(FROM_UNIXTIME(post_date)) = 2006 AND MONTH(FROM_UNIXTIME(post_date)) = 10 ORDER BY post_date desc

Anonymous wrote on November 16^th, 2006 @ 8:37

I didn’t know about MySQL’s FROM_UNIXTIME function.. so I would have done it like this…

function getAllPosts($year = NULL, $month = NULL) { if(!empty($year) { if(empty($month) { $month = 1 $monthEnd = 1; $yearEnd = $year + 1; } elseif($month == 12) { $yearEnd = $year + 1; $monthEnd = 1; } else { $yearEnd = $year; $monthEnd = $month; }



 $rangeStart = mktime(0, 0, 0, $month, 1, $year) - 1;

 $rangeEnd   = mktime(0, 0, 0, $monthEnd, 1, $yearEnd);

$this->db->where(’post_date >’, $rangeStart); $this->db->where(’post_date <‘, $rangeEnd); } $this->db->orderby(‘post_date’, ‘desc’); return $this->db->get(‘posts’); }

SQL:

SELECT * FROM posts WHERE post_date > $rangeStart AND post_date < $rangeEnd ORDER BY post_date desc

DerekAllard.com : CodeIgniter, ExpressionEngine, and the World of Web Design

Writing SQL with HAVING vs WHERE clauses

Comments

Post a Comment