Σύνταξη MySQL ερωτήματος

[philos]

Λοιπόν, χρειάζομαι βοήθεια στη σύνταξη ενός query (βασικά δύο αλλά κάπως κοινά). Έχουμε τους ακόλουθους δύο πίνακες. Ο post περιέχει τα δεδομένα των μηνυμάτων ενός forum και ο thumbs περιέχει τα δεδομένα των ψήφων (1 ή -1) που έχουν δώσει οι χρήστες στα μηνύματα. Σας περιγράφω τα structures που μας αφορούν για την επίλυση του προβλήματος (ίσως αναφέρω και περιττά columns):

Πίνακας "post":

postid (auto_increment)

username

userid

dateline

Πίνακας "thumbs":

id (auto_increment)

thumb (= έχει τιμές 1 ή -1)

userid

username

dateline

to_userid

postid

 

Προσοχή στο ότι ο thumbs έχει συνήθως πάνω από ένα κοινά postids στο αντίστοιχο column, αφού ένα μήνυμα (postid) μπορεί να έχει πάνω από 1 ψήφους (id).

Ο column userid του post και ο to_userid του thumbs έχουν την ίδια πληροφορία αντίστοιχα.

Οι dateline έχουν την σφραγίδα χρόνου που δημιουργήθηκαν τα μηνύματα ή οι ψήφοι αντίστοιχα.

 

Τι θέλω να κάνω;

Θέλω ένα query που θα επιστρέφει τα top 10 πιο ψηφισμένα posts με θετικές (thumb=1) και ένα query με τα top 10 πιο ψηφισμένα posts με αρνητικές (thumb=-1), τις τελευταίες 7 μέρες. Για κάθε post θα ήθελα να επιστρέφει και τον αριθμό των ψήφων που έχουν γίνει τις 7 αυτές τελευταίες μέρες (προσοχή: όχι το σύνολο των ψήφων του post αλλά το σύνολο των ψήφων σε διάστημα 7 ημερών).

 

Έχω γράψει το ακόλουθο αλλά δεν νομίζω να κάνει ακριβώς αυτο που ζητάω. :hmm:

>    SELECT post.*,
    (SELECT COUNT(thumb) FROM  thumbs AS thumb_table WHERE thumb_table.postid = post.postid AND thumb_table.thumb=1) AS thumbs_count
   FROM post AS post
   LEFT JOIN thumbs AS thumbs USING (postid)
   WHERE thumbs.dateline > CURDATE() - INTERVAL 1 WEEK
   GROUP BY postid
   ORDER BY thumbs_count DESC
   LIMIT 10

[defacer]

Πρώτα απ' όλα ορίστε πώς μπορείς να πάρεις για όλα τα posts που είχαν activity τις τελευταίες 7 μέρες το σύνολο των θετικών και αρνητικών ψήφων (φαντάζομαι βλέποντάς το γραμμένο θα καταλάβεις αμέσως):

 

>SELECT postid, thumb, COUNT(*) AS total
FROM thumbs
GROUP BY thumb, postid
WHERE dateline > CURDATE() - INTERVAL 1 WEEK

Μπορείς να προσθέσεις WHERE thumb = 1 για τα up και -1 για τα down για να περιορίσεις τα αποτελέσματα, θα σου χρειαστεί παρακάτω για να παίρνεις ακριβώς 10 και 10 για κάθε περίπτωση.

 

Μ' αυτό το δεδομένο τα υπόλοιπα είναι πανεύκολα:

 

>SELECT post.*, temp.*
FROM (SELECT postid, thumb, COUNT(*) AS total FROM thumbs
     GROUP BY thumb, postid WHERE dateline > CURDATE() - INTERVAL 1 WEEK) temp
LEFT JOIN post ON temp.postid = post.postid
ORDER BY temp.total DESC
LIMIT 10

και απλά προσθέτεις κατάλληλα WHERE στο subquery για τα up ή down.

 

Γενικά είναι πολύ κοντά σ' αυτό που έγραψες μόνος σου απλά αντί για subselect είναι καλύτερα να κάνεις subquery σε temp πίνακα και μετά να κάνεις left join αυτόν στα posts και όχι τα posts σ' αυτόν. O temp πίνακας δεν περιέχει καθόλου τα posts που δεν είχαν activity την τελευταία βδομάδα, οπότε αν ξεκινήσεις απο κει το join δε θα εμπλακούν καν στη διαδικασία -- ενώ αν το ξεκινήσεις από τα posts όπως έκανες εσύ μπορεί να γίνουν διάφορα (π.χ. αν υπάρχει ένα post χωρίς κανένα thumb το οποίο έγινε ένα χρόνο πριν και κανένα άλλο post, θα το δεις στα αποτελέσματα).

 

Τέλος φαντάζομαι ότι θα έχεις μια μίνι επιφοίτηση αν δοκιμάσεις το δικό σου query ως εξής και δεις τη διαφορά:

 

>SELECT post.*,
 (SELECT COUNT(thumb) FROM  thumbs AS thumb_table WHERE thumb_table.postid = post.postid AND thumb_table.thumb=1) AS thumbs_count
FROM post
LEFT JOIN thumbs ON post.postid = thumbs.postid AND thumbs.dateline > CURDATE() - INTERVAL 1 WEEK
GROUP BY postid
ORDER BY thumbs_count DESC
LIMIT 10

(Mετέφερα τη συνθήκη που υπήρχε για WHERE μέσα στη συνθήκη του JOIN. Ναι, γίνεται να βάλεις άσχετα με foreign keys πράγματα μέσα και έχει και λογική).

[philos]

Σε ευχαριστώ για την απάντηση defacer!

 

Παρόλο που το query που έγραψες μου φαίνεται πολύ λογικό και σωστό, το δοκίμασα αλλά:

α) μου επιστρέφει το σύνολο των thumb=1 που έχουν τα μηνύματα (και όχι το σύνολο των thumb τις τελευταίες 7 μέρες)

β) στο subquery μετέφερα την WHERE πριν την GROUP BY για να τρέξει.

Επεξ/σία 22 Ιουλίου 2012 από philos

[philos]

Παιδιά, το πρόβλημα λύθηκε!