Problem z sql
Załóżmy, że mamy takie tabele:
- CREATE TABLE users (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- name VARCHAR(255) UNIQUE NOT NULL
- );
- CREATE TABLE categories (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- name VARCHAR(255) UNIQUE NOT NULL
- );
- CREATE TABLE posts (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- user_id INTEGER NOT NULL REFERENCES users(id),
- category_id INTEGER NOT NULL REFERENCES categories(id),
- title VARCHAR(255) UNIQUE NOT NULL,
- visited INTEGER NOT NULL DEFAULT 0
- );
Teraz zadam proste pytanie: jak pobrać wszystkich użytkowników, którzy najpopularniejszy post był w danej kategori? Odpowiedź to chyba:
SELECT * FROM users WHERE id IN (SELECT user_id FROM posts WHERE (user_id, visited) IN (SELECT user_id, MAX(visited) FROM posts GROUP BY user_id) AND category_id = 1);(z testów wynika, że jest to rozwiązanie poprawne)?
Zacznijmy od powiedzenia wprost, że:
- Wyszukanie tego konkretnego kawałka SQL zajeło mi masę czasu.
- Ktoś chcę ten kawałek ruszać? Będzię on kawałkiem niezrozumiałego, na pierwszy rzut oka, kodu.
- W SQLite nie działa...
Czy ktoś wie, jak to zrobić prościej (zaczynam zastanawiać się nad widokami)? Bo w końcu nie wydaje się to tak niespodziewaną i skomplikowaną operacją...
Komentarze do wpisu
Możesz śledzić odpowiedzi poprzez kanał RSS. Możesz dodać komentarz lub zostawić ślad (trackback) ze swojego bloga.
BTM
Wyszedł mi tylko jeszcze większy behemot niż Tobie:
SELECT `category_id`, `user_id` FROM `posts` WHERE (`visited`, `category_id`) IN (SELECT MAX, `category_id` FROM `posts` WHERE `category_id` IN (SELECT `category_id` FROM `posts` GROUP BY `category_id`) GROUP BY `category_id`) GROUP BY `category_id`
;-)
06 stycznia 2008, 11:29:26
BTM
Ajć, coś mi zjadło max`a :P
Anyhow, lekko uproszczona wersja (bez jednej zbędnej subkwerendy, ale i tak wyjdzie w sumie tyle co u Ciebie, bo jeszcze dane usera musisz wybrać):
SELECT `category_id`, `user_id` FROM `posts` WHERE (`visited`, `category_id`) IN (SELECT MAX, `category_id` FROM `posts` GROUP BY `category_id`) GROUP BY `category_id`
06 stycznia 2008, 11:31:45
Uzytkownik
Nie o to chodziło – ty pobierasz użytkowników którzy mają największą lidzbę odwiedzin w każdej z kategorii z osobna, a ja chcę pobrać wszystkich użytkowników, których najpopularniejszy post jest w danej kategorii ;)
06 stycznia 2008, 11:36:01
Seban
W ActiveRecord można by tak (zakładając, że w klasach ustawione są prawidłowe asocjacje):
user = Post.find(:first, :conditions => [„category_id = ?” category_id], :order => „visited”, :include => :user).user
Może warto jakiś ORM zastosować? Tym bardziej, że mógłbyś wtedy w klasie Category napisać statyczną metodę find_most_popular. Przynajmniej w Ruby.
06 stycznia 2008, 11:49:55
Uzytkownik
Nie nie da się – to mi użytkownika, który ma najwięcej postów w danej kategorii() a nie wszystkich użytkowników, których najpopularniejszy post (
SELECT * FROM posts WHERE user_id = 1 ORDER BY visited DESC LIMIT 1) jest w danej kategorii.A myślę o ORM (o tym miał być na poczatku post) tylko wtedy to byłoby w Sequelu:
DB[:users].filter(:id => DB[:posts].select(:user_id).filter("(user_id, visited)".to_sym => DB[:posts].select(:user_id, "MAX(visited)".to_sym)).filter(:category_id => 3))A w AR (z przepychaniem przez tablice w Ruby:
User.find(:all, :include => :posts).map {|u| u.posts.find :first, :order => :visited, :include => :user }.select {|p| p.category_id == catgeory.id}.map {|p| p.user}.uniqCo nie jest:
06 stycznia 2008, 13:42:06
lato_p
Próbowałem to napisać z użyciem JOIN, ale wyszło dokładnie to samo, tylko zapisane w inny sposób.
SELECT u.* FROM (SELECT user_id, MAX max FROM posts GROUP BY users_id) p1 INNER JOIN posts p ON p.user_id = p1.user_id AND p.visited = p1.max INNER JOIN users u ON u.id = p.user_id WHERE p.category_id = 1
06 stycznia 2008, 13:56:15
Uzytkownik
lato_p: Działa w SQLite przynajmniej.
Kod w SQL:
SELECT u.* FROM (SELECT user_id, MAX(visited) max FROM posts GROUP BY user_id) p1 INNER JOIN posts p ON p.user_id = p1.user_id AND p.visited = p1.max INNER JOIN users u ON u.id = p.user_id WHERE p.category_id = 1Ale jak to zapisać w ORM nie mam pojęcia (nie używając sql w za dużej ilości).
06 stycznia 2008, 14:04:56
Uzytkownik
W AR może da się jako:
User.find(:all, :include => :posts).map {|u| u.posts.find :first, :order => :visited }.select {|p| p.category_id == catgeory.id}.map {|p| p.user}.uniqAle nie jest to zbytnie ułatwienie.
06 stycznia 2008, 14:52:46
Dodaj komentarz