gistfile1.sql

-- there's 5 events in the range 2010-10-15 to 2010-10-21
-- event1,event2,event3 and event4 have start_at in 2010-10-20
-- and event5 is on 2010-10-21
-- thats their tags: event1{tag1,tag2,tag3,tag4}, event2{tag1,tag2,tag3}, event3{tag1,tag2}, event4{tag1}
-- and event5 has only 'tag4'... so..
-- the tag count in the range 2010-10-15 to 2010-10-21 should display count = 2 for tag4...but its ignoring
-- the 2010-10-21 day, what is wrong with this select?

-- event with id = 13 is the one with tag4 with the start_at thats being ignored(2010-10-21)
sqlite> select events.* from events where events.id = 13;
                id = 13
              name = event 5
         descricao =
          local_id = 6
      incluir_mapa =
   flyer_file_name =
flyer_content_type =
   flyer_file_size =
  flyer_updated_at =
          start_at = 2010-10-21 02:00:00
            end_at = 2010-10-21 02:00:00
        created_at = 2010-10-22 00:08:09
        updated_at = 2010-10-22 00:08:09
           user_id = 7

-- this is the select I can't figure out whats wrong and the (wrong) result
sqlite> SELECT tags.*, COUNT(*) AS count FROM "tags" LEFT OUTER JOIN taggings ON
 tags.id = taggings.tag_id AND taggings.context = 'tags' INNER JOIN events ON ev
ents.id = taggings.taggable_id WHERE taggings.taggable_type = 'Event' AND events
.start_at >= '2010-10-15' AND events.end_at <= '2010-10-21' GROUP BY tags.id, ta
gs.name HAVING COUNT(*) > 0;
   id = 7
 name = tag1
count = 4

   id = 8
 name = tag2
count = 3

   id = 9
 name = tag3
count = 2

   id = 10
 name = tag4
count = 1