Similar to a feed reader, I'm storing a bunch of articles, each pertaining to a source (feed) and each feed can belong to a category. What I'm trying to do is:
- Retrieve the articles of the feeds that belong to a certain category.
- Group the articles. One scenario would be by date(published_time), so that I have groups, for example: (12.04.09 - 3 articles, 17.04.09 - 9 articles, and so on)
- Loop through each group and display each article. Pseudo-code:
foreach (Group group in results)
{
print(group.Name);
foreach (Article article in g.Articles)
{
print(article.Title);
print(article.Content);
}
}
I thought something simple like:
SELECT group_concat(item_id, '#') FROM items GROUP BY date(published_time)
would work. But then I'd have to split the resulting rows and loop through that (and there is no group_concat(*) function)
I'm confused as to how I would group(2) the results so that I can iterate through each one, preserving the group name. I thought that a SQL query returns ONE big table, and so, it seems to be impossible to accomplish this with just one query.
I reckon this is more of a DB design question, I'm also new to SQLite (SQL for that matter), so I ask you, gurus, how would one get this done efficiently?
1 Answer 1
SELECT Title, Content, date(published_time) AS Date
FROM items
ORDER BY date(published_time);
Pseudocode:
last = None
for r in results:
if not last or r.Date != last.Date:
print "Group", r.Date
print r.Title, r.Content
last = r
answered Dec 26, 2009 at 14:01
Roger Pate
Sign up to request clarification or add additional context in comments.
Comments
lang-sql