Βοήθεια με MySQL query

[Scandal]

Θα ήθελα τη βοήθεια των ειδικό(τερων), πάνω σε ένα MySQL query.
Προειδοποιώ ότι δεν είναι απλό

Έχουμε δύο πίνακες:
τον thread με στήλες:
threadid
title

και τον post με στήλες:
postid
threadid
dateline (σφραγίδα χρόνου)
post_thanks_amount (int)

Άλλα δεδομένα:
τα post κάθε thread χωρίζονται ανά 10 σε κάθε σελίδα.

Τι θέλω να πάρω:
Το post κάθε thread:
- με το μέγιστο (MAX) post_thanks_amount (μέσα σε ένα thread)
- όμως θέλω να επιλέξω το MAX εξαιρώντας ως επιλογή, posts της 1ης σελίδας
- καθώς και άλλες επεξεργασίες στην επιλογή του post που τελικά θα κριθεί ως μέγιστο

Ουσιαστικά ζητάω MAX βάσει κριτηρίων (GROUP BY threadid) κι όχι το MAX του thread γενικά.

Tip: με το ακόλουθο query μπορείς να υπολογίσεις τη σελίδα κάθε post:

(SELECT FLOOR((COUNT(*) + 1) / 10) + 1
FROM `". TABLE_PREFIX ."post` AS s
    WHERE s.postid < (SELECT t.postid
                      FROM `". TABLE_PREFIX ."post` AS t
                      WHERE post.postid > t.postid AND t.visible = 1 AND t.threadid = s.threadid
                      ORDER BY t.postid DESC
                      LIMIT 1)
            AND s.threadid = post.threadid
            AND s.visible = 1
) AS page_cnt

Για να μην το κουράζω, σας επισυνάπτω τι έχω γράψει

QUERY A: Αν τρέξω αυτό το query παίρνω κάποια αποτελέσματα (τα μηνύματα με τουλάχιστον x likes μέσα σε μια χρονική περίοδο και εκτός της 1ης σελίδας).

SELECT post.*, (SELECT FLOOR((COUNT(*) + 1) / 10) + 1
                FROM `". TABLE_PREFIX ."post` AS s
                       WHERE s.postid < (SELECT t.postid
                                         FROM `". TABLE_PREFIX ."post` AS t
                                         WHERE post.postid > t.postid AND t.visible = 1 AND t.threadid = s.threadid
                                         ORDER BY t.postid DESC
                                         LIMIT 1)
                            AND s.threadid = post.threadid
                            AND s.visible = 1
) AS page_cnt
FROM `". TABLE_PREFIX ."post` AS post
LEFT JOIN `". TABLE_PREFIX ."thread` AS thread USING (threadid)
WHERE post.visible = 1
    ".($options['window_range'] == 'max' ? '' : " AND post.dateline > $seconds_range" )."
    AND post.dateline < $seconds_offset
    AND post.post_thanks_amount >= $at_least_x_thanks
    AND thread.dateline < $seconds_thread_age
    AND thread.forumid NOT IN ('" . implode("', '", $guest_no_access_forumids) . "')
HAVING page_cnt > 1
ORDER BY post.post_thanks_amount DESC, thread.threadid DESC
LIMIT $results_number

Αυτό το query λοιπόν τρέχει σωστά.

Το ακόλουθο όμως, που είναι και ο λόγος που άνοιξα το thread, δεν παρουσιάζει μια σχετική συνάφεια στα αποτελέσματα με το query A.
Ειδικότερα, κάποια (πολύ λίγα) threads φαίνονται στο Α αλλά δεν φαίνονται στο Β κι ας πληρούνται οι προυποθέσεις.

QUERY B:

                                            SELECT post.*,                                           
                                            (SELECT FLOOR((COUNT(*) + 1) / $paginate) + 1
                                                FROM `". TABLE_PREFIX ."post` AS z
                                                WHERE z.postid < (SELECT q.postid
                                                                  FROM `". TABLE_PREFIX ."post` AS q
                                                                  WHERE post.postid > q.postid AND q.visible = 1 AND q.threadid = z.threadid
                                                                  ORDER BY q.postid DESC
                                                                  LIMIT 1)
                                                AND z.threadid = post.threadid
                                                AND z.visible = 1
                                            ) AS page_cnt
                                            FROM `". TABLE_PREFIX ."thread` AS thread
                                            LEFT JOIN `". TABLE_PREFIX ."post` AS post ON (post.threadid = thread.threadid)
                                        
                                            WHERE thread.dateline < $seconds_thread_age
                                                    AND thread.forumid NOT IN ('" . implode("', '", $guest_no_access_forumids) . "')                                   
                                            AND post.visible = 1
                                            ".($options['window_range'] == 'max' ? '' : " AND post.dateline > $seconds_range" )."
                                            AND post.dateline < $seconds_offset
                                            AND post.post_thanks_amount >= $at_least_x_thanks
                                           GROUP BY thread.threadid
                                           ". ($options['ignore_post_if_on_page_1'] ? ' HAVING page_cnt > 1 ': '') ."
                                           ORDER BY post.post_thanks_amount DESC, thread.threadid DESC                                         
                                           LIMIT $results_number

Καμιά ιδέα για το query B;
Ξέρω είναι τεράστιο αλλά το μπαλώνω από εδώ κι από εκεί εδώ και 10 ώρες
Pls μην με σνομπάρετε
Αν έχετε κατά νου ένα δικό σας mysql query που μπορεί να εξάγει τα αποτελέσματα που θέλω ή διόρθωση στο υπάρχον Β, ευπρόσδεκτα όλα!
Διαβάζω ότι ουσιαστικά η ORDER BY και η GROUP BY δεν λειτουργούν μαζί...

 

[Scandal]

Λοιπόν, κατέληξα σε αυτό το τέρας που μάλλον δουλεύει
Τώρα γιατί έβαλα LIMIT 1000 στο subquery θα σας γελάω. Χωρίς LIMIT (που δε θα έπρεπε να τα τσεκάρει όλα; ) ή μικρή τιμή, δεν επέστρεφε σωστά- ολοκληρωμένα αποτελέσματα. Οπότε έβαλα ένα high value.

Υπολογίζω δύο φορές τη σελίδα, γιατί τη θέλω και στο main query / αποτελέσματα.

                                        SELECT post.*,
                                            (SELECT FLOOR((COUNT(*) + 1) / $paginate) + 1
                                                FROM `". TABLE_PREFIX ."post` AS z
                                                WHERE z.postid < (SELECT q.postid
                                                                  FROM `". TABLE_PREFIX ."post` AS q
                                                                  WHERE post.postid > q.postid AND q.visible = 1 AND q.threadid = z.threadid
                                                                  ORDER BY q.postid DESC
                                                                  LIMIT 1)
                                                AND z.threadid = post.threadid
                                                AND z.visible = 1
                                            ) AS page_cnt                                            
                                        FROM (
                                           SELECT p.postid, p.threadid, p.post_thanks_amount,
                                            (SELECT FLOOR((COUNT(*) + 1) / $paginate) + 1
                                                FROM `". TABLE_PREFIX ."post` AS u
                                                WHERE u.postid < (SELECT s.postid
                                                                  FROM `". TABLE_PREFIX ."post` AS s
                                                                  WHERE p.postid > s.postid AND s.visible = 1 AND s.threadid = u.threadid
                                                                  ORDER BY s.postid DESC
                                                                  LIMIT 1)
                                                AND u.threadid = p.threadid
                                                AND u.visible = 1
                                            ) AS page_cnt_new
                                       
                                           FROM `". TABLE_PREFIX ."post` AS p
                                           LEFT JOIN `". TABLE_PREFIX ."thread` AS t USING (threadid)
                                           WHERE t.forumid NOT IN ('" . implode("', '", $guest_no_access_forumids) . "')
                                            ". ($options['thread_age_x_days'] == 0 ? '' : " AND t.dateline < $seconds_thread_age" )."                                        
                                            AND p.visible = 1
                                            ".($options['window_range'] == 'max' ? '' : " AND p.dateline > $seconds_range" )."
                                            AND p.dateline < $seconds_offset
                                            AND p.post_thanks_amount >= $at_least_x_thanks
                                            ". ($options['ignore_post_if_on_page_1'] ? ' HAVING page_cnt_new > 1 ': '') ."
                                            ORDER BY p.post_thanks_amount DESC, p.threadid DESC
                                            LIMIT 1000
                                        ) AS x
                                        LEFT JOIN `". TABLE_PREFIX ."post` AS post ON (post.postid = x.postid)
                                        LEFT JOIN `". TABLE_PREFIX ."thread` AS thread ON (post.threadid = thread.threadid)
                                        GROUP BY x.threadid
                                        ORDER BY x.post_thanks_amount DESC, x.threadid DESC
                                        LIMIT $results_number

 

[Guest 209912]

Θέλω να σε βοηθήσω, πραγματικά, αλλά για κανένα λόγο.

Αυτόματη ένωση συνεχόμενων μηνυμάτων: 23 Ιουλίου 2021

Φιλική σουμβουλή. Αντί να τα κάνεις όλα με SQL, απλά τα τράβα τα post που θέλεις και μετά κάνε αυτά που θέλεις με collection steams μέσα σε ένα JS ή PHP script. Π.χ. posts.filter( post => post.thread_id === "kati");

Αν δεν καταλαβαίνεις τι είναι αυτά που γράφω διάβασε για filter σε collections.

 

[Scandal]

Θέλω να σε βοηθήσω, πραγματικά, αλλά για κανένα λόγο.

Αυτόματη ένωση συνεχόμενων μηνυμάτων: 23 Ιουλίου 2021

Φιλική σουμβουλή. Αντί να τα κάνεις όλα με SQL, απλά τα τράβα τα post που θέλεις και μετά κάνε αυτά που θέλεις με collection steams μέσα σε ένα JS ή PHP script. Π.χ. posts.filter( post => post.thread_id === "kati");

Αν δεν καταλαβαίνεις τι είναι αυτά που γράφω διάβασε για filter σε collections.

Τα έχω ψηλο πετύχει στο xenForo τα collection steams, αλλά αυτά δεν είναι για μικροεπεξεργασίες;
Ό,τι μπορώ να το κάνω με MySQL το κάνω, δεν υπάρχει λόγος να γίνει με PHP.
Ειδικά όταν μιλάμε για εκατομμύρια εγγραφές κι όχι 10δες / 1000δες.

Στη προκειμένη πχ μια άλλη λύση που είχα κάνει στην αρχή για να γλιτώσω το μεγάλο - βαρή query που προκαλεί το HAVING page_cnt > 1:
Μέσα σε ατέρμονη loop τράβαγα posts σε κύκλους (με το πιο απλό query).
Αν το μήνυμα βρισκόταν στη σελίδα 1, τότε το έκανε unset.
Η loop συνέχιζε να τραβάει posts σε κύκλους και να κάνει unset τα page_cnt = 1 και μόλις εντόπιζε sizeof($results) >= $results_number έκανε break.
Μετά με PHP έκανα sort τα results βάσει του κλειδιού post_thanks_amount και έκοβα τα όποια περιττά με την array_slice και την array_unique.

 

[Guest 209912]

Ναι, γενικά ότι μπορείς να το κανεις με SQL το κάνεις με SQL γιατι αυτό είναι και το νόημα να χρησιμοποιεις ένα RDBMS, κανει όλες αυτές τις δουλειές με optimized τρόπο. Αυτό βέβαια δεν σημαίνει πως αυτός είναι και ο πιο εύκολος ή γρήγορος τρόπος, και γενικά το να ξέρεις να γράφεις καλά queries είναι ένα επάγγελμα από μόνο του DB engineer.

 

[vassilis498]

- Νομίζω πως το κάνεις αρκετά πιο περίπλοκο απ' ο,τι είναι. Καταρχήν δεν θες να υπολογίσεις τη σελίδα του κάθε post, μόνο η πρώτη σε νοιάζει, απλά πετάς τα 10 (ή 20 ή 40) πρώτα.

- Το πρώτο query για τη σελίδα μου φαίνεται λίγο περίεργο. Πρώτα υπολογίζεις το προηγούμενο μήνυμα και μετά κάνεις count και προσθέτεις 1. Γιατί; απλά πέτα το μέσα select.

- Στο having μπαίνουν κυρίως aggregate filters. Τα υπόλοιπα καλύτερα πριν το grouping. Είναι και πιο γρήγορο.

- Αν είναι καμιά σάπια εργασία για τη σχολή κλάιν αλλά σε άλλη περίπτωση μου φαίνεται λίγο ύποπτο που αντί να χρησιμοποιείς bindings κάνεις interpolate μεταβλητές κατευθείαν μες στο query. Ελπίζω τουλάχιστον να κάνεις κάποιο sanitization απ' έξω αλλιώς η φάση είναι ανοίξαμε και σας περιμένουμε.

Κυρίως είχα στο μυαλό μου κάτι τέτοιο:

select post.*
FROM post
INNER JOIN ( -- αν δε σε νοιαζει να φέρεις και τα thumbs up αυτό δε χρειάζεται
    select post.postid, max(post.post_thanks_amount)
    from post 
    inner join thread as t using (threadid)
    where post.postid not in ( -- αυτά είναι τα post της πρώτης σελίδας
        select post.postid 
        from post 
        where post.threadid = t.threadid
        limit <page_size>
    )
    -- and (ό,τι άλλο φίλτρο θες)
    group by t.threadid
) using (postid);