I have a table for blogposts and a table for tags, with a many-to-many relation between them.
How can I extract the most frequent tag across a subset of blogposts? (e.g. only those from the past year) Is there a way to extract the frequencies of all the tags associated with the blogposts subset?
Thanks
Edit: my schema:
CREATE TABLE `tag` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`name` varchar(200) NOT NULL,
`access` varchar(1),
`linked_created` datetime
)
;
CREATE TABLE `blogpost_tags` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`blogpost_id` integer NOT NULL,
`tag_id` integer NOT NULL,
UNIQUE (`blogpost_id`, `tag_id`)
)
;
CREATE TABLE `blogpost` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`title` varchar(50),
`body` varchar(500)
)
;