Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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
Add Comment
Please, Sign In to add comment