Μερικές ερωτήσεις για MySQL SELECT και WHERE

[philos]

Καλησπέρα παιδιά
έχω δύο ερωτησούλες σε MySQL. Κάνω και τις προσπάθειές μου αλλά δεν έχω και πολλά δεδομένα για να το φιξάρω.
Ο πίνακας post (μηνυμάτων) έχει τη στήλη dateline που είναι unix timestamp που δημιουργήθηκε το μήνυμα καθώς και τη στήλη threadid που δείχνει σε ποιο thread του forum ανήκει το μήνυμα.

1. Έστω ότι έχουμε στην php:
$offset_days = 4;
$range_days  = 5;

$range = $range_days + $offset_days;

Θέλω να διαλέξω τα στοιχεία που βλέπετε στο mockup. Οι κόκκινες βούλες είναι ημέρες σε timeline.

Τι WHERE βάζω;

Έχω γράψει αυτό μέχρι στιγμής:
WHERE (FROM_UNIXTIME(dateline) > CURDATE() - INTERVAL $range DAY)
    AND (FROM_UNIXTIME(dateline) < CURDATE() - INTERVAL $offset_days DAY)
    
Είναι σωστό;

2. Έστω ότι τα μηνύματα σε κάθε thread (threadid) χωρίζονται σε σελίδες των 15:
$perpage = 15;

Μπορεί κανείς να σκεφτεί τι μπορώ να βάλω στη SELECT για να εντοπίσω τον αριθμό σελίδας που βρίσκεται το post; (κάτι AS thread_page) ;
Εννοώ να γίνει στη SELECT κι όχι με php υπολογισμούς σε loop κτλ. Υποθέτω ότι χρειαζόμαστε κάποιο subquery, αλλά να μην κρασάρει και η MySQL σε περίπτωση μεγάλου πίνακα

Να πω ότι γενικά το query κάνει SELECT *** FROM post WHERE ***.

Επεξ/σία 18 Ιουλίου 2021 από philos

[Ponous]

Για το 1ο εγώ θα σου συνιστούσα να δεις το WHERE BETWEEN.

Παράδειγμα :

SELECT * 
FROM your_table
WHERE your_date_field BETWEEN DATE_SUB('2021-01-01', INTERVAL $some_days DAY) AND DATE_SUB('2021-01-01', INTERVAL $some_less_days DAY)

Εκεί όπου έβαλα '2021-01-01' θα πρέπει να αντικατασταθεί με την σημερινή ημερομηνία (αυτή που τρέχει το script). Δεν σ'το γράφω για να δεις την δομή.

• Η εντολή DATE_SUB θα αφαιρέσει όσες μέρες έχουν οι τιμές στις μεταβλητές some_days & some_less_days από την ημερομηνία 2021-01-01
• Στην περίπτωση σου θα γίνει κάτι τέτοιο:
  • some_days = (range_days + offset_days)
  • some_less_days = offset_days
• Η μεταβλητές some_days πρέπει να είναι αριθμός. Π.χ. 4

Δεν το έχω δοκιμάσει, αλλά άμα το φέρεις στα μέτρα σου λογικά θα δουλέψει.

Για το 2ο:

Θα πρέπει να παίζεις με το LIMIT και το OFFSET.

Άκυρο, κατάλαβα λάθος πρόβλημα για το 2ο 🤪

Δεν το έχω ξανακάνει σε query, απλά με ένα γρήγορο ψάξιμο έχω βρει πως στην έκδοση 8 υπάρχει η εντολή ROW_NUMBER. Αλλά δεν την έχω ξαναχρησιμοποιήσει.

Επεξ/σία 21 Ιουλίου 2021 από Ponous

[philos]

Με αυτό το query/ την PHP function γίνεται αυτό που θέλω.

function get_post_page($postid, $threadid)
{
	global $vbulletin, $db;
	
	$postid = intval($postid);
	$threadid = intval($threadid);
	
	$query = $db->query_first("SELECT COUNT(*) AS cnt FROM `". TABLE_PREFIX ."post` WHERE postid < $postid AND threadid = $threadid AND visible = 1");
    $count = $query['cnt'];

    $page = 1; 
    $offset = 0;
	$paginate = $vbulletin->options['maxposts'];
    for ($i = 0; $i < $count; $i++) 
	{
        $offset++;
        if ($offset == $paginate) 
		{
          $page++;
          $offset = 0;
        }
    }
    return intval($page);
}

Απλά επειδή θέλω να εξαιρέσω στο τελείως βασικό query τα posts της πρώτης σελίδας, προσπαθώ να βρω έναν τρόπο να βάλω στο βασικό query μια SELECT (που θα κάνει count τη σελίδα που ανήκει κάθε αποτέλεσμα / post), ώστε να κάνω μετά στη WHERE page_cnt > 1.

 

Οι μέχρι στιμής αποτυχημένες χαζομάρες μου:

SELECT *,
	(SELECT FLOOR(COUNT(*) / 15) AS page_cnt 
	FROM post AS s 
	WHERE s.postid < 
			(SELECT postid 
			FROM post
			WHERE postid < s.postid AND visible = 1 AND threadid = s.threadid LIMIT 1) 
		AND s.threadid = post.threadid 
		AND s.visible = 1
		LIMIT 1) AS page_cnt
FROM post
WHERE page_cnt > 1

Καμιά ιδέα;

Θα μπει σε server με PHP 5.2.

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

[philos]

Κάτι βρήκα. Αν πιστεύετε ότι μπορεί να γίνει και πιο αποδοτικό, let me know

	SELECT [...]
	(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
                                                  
        [...]
        HAVING page_cnt > 1

 

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

[philos]

Σπάω το κεφάλι μου και δεν μου βγαίνει με τίποτα το ακόλουθο

Ισχύει ο υπολογισμός του page_cnt που παρέθεσα παραπάνω, ο οποίος εξάγει τη σελίδα που είναι ένα post.

Έτσι, με ένα HAVING page_cnt > 1 επιλέγω μηνύματα που είναι από τη 2η σελίδα και άνω.

Έστω ότι έχουμε ένα column με ονομασία score στον πίνακα post, και φυσικά και τον πίνακα thread.

Η ερώτηση είναι, πως μπορώ να επιλέξω το MAX score για threads (δλδ έχω να κάνω GROUP BY thread.threadid) σελίδας πάνω του 1 (δλδ το post βρίσκεται στη σελίδα 2+ ).

Δηλαδή αν το thread έχει ένα post στη πρώτη σελίδα με 100 score που είναι το MAX του, εγώ θέλω να skipάρω αυτό το MAX και να διαλέξω για παράδειγμα το αμέσως επόμενο αφού κάνω HAVING page_cnt > 1. Αυτό το άλλο (αμέσως επόμενο του 100), θα πρέπει να είναι στη σελίδα 2 ή μεγαλύτερη.

Ουσιαστικά θέλω να αναζητήσω το MAX post.score για κάποια threads, απλά εξαιρώντας τη σελίδα 1. Θέλω το αμέσως επόμενο MAX.

Καμιά ιδέα;