I have the following database schema:
posts
-------
post_id
post_name
posts_tags
-------------
poast_id
tag_id
tags
-------
tag_id
tag_value
I need to select all posts records for provided tags. For example, give me
all posts that have tags "c#, asp.net, microsoft", or give me all posts
that
have tags "sql, linq" etc.
I'm no expert but I've come up with something that seems to work, but
BEWARE - I've no idea how efficient it would be with large data sets
(probably not very!). However, it might give you some ideas. I've split it
up into a number of queries as it makes it easier to see what's going on,
but it's probably possible to combine them. Apologies if you prefer the
"from ... select" syntax, but I find it easier to just use the functions
(and apologies too for the very unhelpful names I''ve used).
// Get sequence of tags wanted with no duplicates
var tagNames = textBox1.Text.Split(',').ToList().Distinct();
int numOfTags = tagNames.Count();
// Get sequence of ids of wanted tags (may want to do case-insensitive
comparisons here)
var tagids = tagNames.Join(tags, tn => tn, t => t.tag_value, (tn, t) =>
t.tag_id);
// Check that all tags are known
if (tagids.Count() != numOfTags)
{
MessageBox.Show("Not all tags were recognised");
return;
}
// Effectively get all post_tag entries for all the required tags and group
them by the post id
var posttags =
post_tags.Join(tagids, pt => pt.tag_id, t => t, (pt, t) => new {
pt.post_id, pt.tag_id}).
GroupBy(x => x.post_id);
// Each posttags entry consists of a Key (post_id) and a collection of tag
ids. We only want
// the ones with ALL the required tag ids.
var postids = posttags.Where(pts => pts.Count() == numOfTags).Select(pts =>
pts.Key);
// Finally convert the post ids into names
var postnames = postids.Join(posts, pi => pi, p => p.post_id, (pi, p) =>
p.post_name);
Good luck!
Chris Jobson
