LEFT JOIN βάσει τιμής σε table

Συζητήσεις για την βάση δεδομένων MySQL και το phpMyAdmin

Συντονιστές: WebDev Moderators, Super-Moderators

Απάντηση
Άβαταρ μέλους
philos
Δημοσιεύσεις: 260
Εγγραφή: 30 Αύγ 2007 23:32

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από philos » 16 Δεκ 2013 18:37

Λοιπόν, έχουμε έναν υποθετικό πίνακα name με columns:
itemid (auto_increment)
contentid
contenttype
userid
κ.α.

Η τιμή του userid μπορεί να συσχετιστεί με έναν συγκεκριμένο πίνακα (τον user), όμως το contentid αφορά ids από διάφορους πίνακες.
Το contenttype παίρνει τιμές 1-10 (= αναγνωριστικά). Αυτό που θέλω να κάνω είναι, όταν το contenttype είναι κάποιο συγκεκριμένο, το query να κάνει LEFT JOIN με διαφορετικό πίνακα.

πχ όταν το contenttype είναι 2, να κάνει LEFT JOIN με τον πίνακα pictures ON (pictures.pictureid = name.contentid), όταν είναι 5, να κάνει LEFT JOIN με τον messages ON (messages.messageid = name.contentid), προκειμένου να τραβήξω τα σχετικά δεδομένα.

Είναι εφικτό κάτι τέτοιο; Θα ήθελα ένα query-παράδειγμα για να καταλάβω πως μπορώ να το κάνω.

Ευχαριστώ!

Άβαταρ μέλους
giannis17
Honorary Member
Δημοσιεύσεις: 1215
Εγγραφή: 06 Ιαν 2005 19:50
Τοποθεσία: Παγκράτι - Αθήνα
Επικοινωνία:

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από giannis17 » 16 Δεκ 2013 21:39

Ο πιο εύκολος τρόπος είναι να γίνει έξω από τη βάση στο πρόγραμμα σου με ένα switch-case.

Δεν το έχω δει σε κώδικα SQL, θεωρητικά γίνεται καθώς υποστηρίζει και IF/ELSE και CASE αλλά ανάλογα την έκδοση SQL μπορεί να υπάρξουν ασυμβατότητες που θα οδηγήσουν σε λάθη στην πορεία κάτι που δεν σε συμφέρει και ουσιαστικά θα κάνει και πιο αργή την βάση καθώς θα πρέπει να τραβήξει τα δεδομένα από όλους τους πίνακες για να κάνει την σύγκριση και να σου δείξει αποτέλεσμα.

Αν βέβαια αυτό είναι κάτι που γίνεται συχνά στη βάση μπορείς να ορίσεις ένα CREATE VIEW με το query σου και αυτό πλέον να αποθηκευτεί σε ένα εικονικό πίνακα και να τρέχει μετά πολύ πιο γρήγορα.

Σε τι γλώσσα είναι η εφαρμογή σου;
"There is only one problem with common sense; it’s not very common."
– Milt Bryce

Άβαταρ μέλους
philos
Δημοσιεύσεις: 260
Εγγραφή: 30 Αύγ 2007 23:32

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από philos » 16 Δεκ 2013 21:52

Είναι php/mysql εφαρμογή, θέλω να φτιάξω ένα Activity Stream για το site μου και σκέφτηκα να χρησιμοποιήσω έναν μόνο πίνακα όπου κάθε δραστηριότητα θα καταχωρείται σε αυτόν. Έναν πίνακα ώστε να τρέχει στο τέλος ένα query. Είναι σημαντικό να ελαχιστοποιηθούν τα queries καθώς επιθυμώ να τρέχει σε ΚΑΘΕ σελίδα, σε sidebar (αυτή τη στιγμή έχω απαριθμήσει 20 content types που θέλω να εμφανίζονται).

Ευχαριστώ! :)

Άβαταρ μέλους
giannis17
Honorary Member
Δημοσιεύσεις: 1215
Εγγραφή: 06 Ιαν 2005 19:50
Τοποθεσία: Παγκράτι - Αθήνα
Επικοινωνία:

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από giannis17 » 16 Δεκ 2013 22:17

Ωραία λοιπόν, ο πιο σωστός τρόπος είναι να φτιάξεις 20 διαφορετικά views στην βάση σου, 1 για κάθε περίπτωση, και να καλείς το αντίστοιχο view ανάλογα με την "κατηγορία" όπου βρίσκεσαι αυτή τη στιγμή...δηλαδή θα στέλνεις ένα ελαφρύ query κάθε φορά το οποίο θα είναι και cached λόγω του view και η σύγκριση θα γίνεται με βάση το $_GET όχι με βάση το query οπότε δεν καταπονείς καθόλου την βάση.

για να φτιάξεις τα views:

Κώδικας: Επιλογή όλων

CREATE VIEW type1 AS SELECT * FROM name LEFT JOIN messages ON messages.messageid = name.contentid
και type2 για το άλλο join κ.ο.κ.

έπειτα στην PHP το μόνο που έχεις να κάνεις είναι ένα απλό switch:

Κώδικας: Επιλογή όλων

if($_GET["metavliti"]) {

$contenttype = $_GET["metavliti"];

switch ($contenttype) {
    case "1":
        $query="SELECT * FROM type1";
        break;
    case "2":
        $query="SELECT * FROM type2"
        break;
    case "3":
        $query="SELECT * FROM type3"
        break;
...

    case "20":
        $query="SELECT * FROM type20"
        break;
}
φυσικά αλλάζοντας την μεταβλητή με βάση την οποία θα διαλέγει το query και συμπληρώνοντας τα υπόλοιπα case.
"There is only one problem with common sense; it’s not very common."
– Milt Bryce

Άβαταρ μέλους
philos
Δημοσιεύσεις: 260
Εγγραφή: 30 Αύγ 2007 23:32

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από philos » 16 Δεκ 2013 22:22

Η αλήθεια είναι ότι δεν έχω ξανά χρησιμοποιήσει τη VIEW.

Νομίζω ότι η λύση σου τραβάει κάθε φορά ένα contenttype.
Εγώ θέλω να εξάγω έναν πίνακα που να περιλαμβάνει όλα τα στοιχεία (και τα 20 content types), order κατά χρονολογική σειρά, τα τελευταία Χ. ;)

Άβαταρ μέλους
giannis17
Honorary Member
Δημοσιεύσεις: 1215
Εγγραφή: 06 Ιαν 2005 19:50
Τοποθεσία: Παγκράτι - Αθήνα
Επικοινωνία:

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από giannis17 » 16 Δεκ 2013 22:34

Δηλαδή το αποτέλεσμα θα είναι "στατικό"; Ομολογώ πως δεν έχω καταλάβει ακόμα τι ακριβώς θέλεις να κάνεις...νόμιζα ήθελες μόνο ένα join την φορά. Ίσως σε βοηθήσει η UNION, κάνεις τα select σου κανονικά με τα left join και τα ενώνεις με UNION ανάμεσα, αν κάποιο join δεν δώσει αποτέλεσμα δε σε επηρεάζει.
"There is only one problem with common sense; it’s not very common."
– Milt Bryce

Άβαταρ μέλους
dva_dev
Script Master
Δημοσιεύσεις: 3790
Εγγραφή: 16 Σεπ 2005 01:32
Επικοινωνία:

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από dva_dev » 17 Δεκ 2013 18:50

Για αρχή, είναι εφικτό αυτό που θέλεις να κάνεις.

Αλλά και μερικές ερωτησούλες.
α) Είναι σίγουρο ότι θέλεις left join και όχι inner join?
β) Γιατί κατέληξες σε ένα τέτοιο σχήμα από τη στιγμή που δεν ξέρεις πως να τραβήξεις τα δεδομένα σου;
γ) Είσαι σίγουρος ότι θέλεις να πας σε μια λογική optimization ελαχιστοποιώντας τον αριθμό των queries από τη στιγμή που όπως καταλαβαίνω δεν έχεις συγκεκριμενοποιήσει τι ακριβώς θέλεις να κάνεις και το κυριότερο δεν έχεις δει να τρέχει για να δεις πως συμπεριφέρεται;

Τέλος όπως αναφέρει και ο giannis17, ούτε εγώ έχω καταλάβει ακριβώς τι θέλεις να κάνεις και γιατί χρειάζεσαι να το κάνεις έτσι. Αλλά πάμε παρακάτω...

Αν είναι να κάνεις union select τότε θα πρέπει όλα τα subqueries να έχουν τον ίδιο αριθμό στηλών με τους ίδιους τύπους δεδομένων στις αντίστοιχες στήλες.
Για να φτιάξεις views θα μπορούσες να κάνεις κάτι σαν αυτό (για την τιμή 2 στο contenttype) - και αν νομίζεις το αλλάζεις σε left join αντί για inner join.

Κώδικας: Επιλογή όλων

CREATE VIEW v2 AS
select n.itemid,n.contentid,n.contenttype,n.userid, p.fieldx
from name n
inner join pictures p on (n.contentid = p.id and n.contenttype = 2);

Άβαταρ μέλους
philos
Δημοσιεύσεις: 260
Εγγραφή: 30 Αύγ 2007 23:32

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από philos » 21 Δεκ 2013 10:10

Ευχαριστώ πολύ για τις απαντήσεις παιδιά! :)
Παρακάτω αναλύω τι θέλω να κάνω και τι δοκιμές έχω κάνει προσωπικά.

@giannis17: Το θέμα είναι ότι δεν θέλω ένα contenttype κάθε φορά, αλλά όλα!! Order by date/time και με LIMIT πχ 10 εως 30.

@dva_dev: Χρησιμοποιώ συνήθως LEFT JOIN γιατί έχω δει το vbulletin να το χρησιμοποιεί σχεδόν παντού (σε αυτό δουλεύω). Ξέρω, είμαι άσχετος. :p

Χθες που είχα χρόνο έκανα δοκιμές στο ακόλουθο σχήμα, αλλά δεν αποδίδει καθόλου (το query θέλει 80 seconds για να ολοκληρωθεί, με πίνακα 6500 εγγραφών).
Παρακαλώ την επιείκεια σας, δεν έχω εκτεταμένη εμπειρία.


Όπως είπα, μιλάμε για ένα Activity Stream το οποίο θα δείχνει τη συνολική δραστηριότητα της ιστοσελίδας, χωρισμένη σε σελίδες (δλδ θα μπορεί ο χρήστης να κινηθεί μπρος-πίσω στα δεδομένα, οπότε πρέπει να υπάρχει πρόσβαση σε όλα τα δεδομένα - αν χρησιμοποιήσουμε έναν πίνακα, αυτός δε θα πρέπει να αδειάζει σύντομα ή να περιορίζονται τα rows του).
Επιπλέον τα δεδομένα του Activity Stream θα πρέπει να γίνονται mark as read, ξεχωριστά για τον κάθε χρήστη.

Έτσι, κατέληξα σε δύο πίνακες, τον "sc_activitystream_item", ο οποίος περιέχει τη δραστηριότητα με itemid:
itemid AUTO INCREMENT
contenttypeid (πχ 1-20)
userid1
userid2
postid
threadid
discussionid
gmid
pictureid
albumid
picturecommentid
announcementid
pollid
quoteid
vmid
userchangelogid
extradata
dateline
readed_by_default (bool, το χρησιμοποιήσα στη δοκιμή για τα imported δεδομένα, δλδ τα δεδομένα που υπήρχαν πριν εγκατασταθεί η εφαρμογή)
state (visible, deleted, disabled)

Αυτός ο πίνακας γεμίζει δεδομένα κάθε φορά που γίνεται μια δημοσίευση στο site. Πχ όταν γίνεται ένα post, μπαίνει εγγραφή στον πίνακα posts ΚΑΙ στον πίνακα "sc_activitystream_item" (τα id του δλδ).
Όπως καταλαβαίνετε όταν ένα id column έχει τιμή, όλα τα άλλα έχουν 0 (εκτός του postid που αντιστοιχεί και σε threadid).

Και τον πίνακα "sc_activitystream_itemread" ο οποίος περιέχει τα mark as read δεδομένα για κάθε χρήστη και itemid.
userid
itemid
readtime

Να πω ότι στον "sc_activitystream_item" είχα πιο γενικά columns (πχ αντί για τα τόσα ids, είχα το column contentid, όμως δεν ήξερα πως να αλλάζω τα joins κάθε φορά, έτσι πάνω στις δοκιμές έφτιαξα ένα column για κάθε id).

Σας παρουσιάζω το query!!

Κώδικας: Επιλογή όλων

          $getstreams = $db->query_read("
          SELECT stream.*, user1.username AS username1, user2.username AS username2, 
                 userfield1.field8 AS gender1, userfield2.field8 AS gender2, 
                 forum.title_clean AS forumtitle, socialgroup.name AS groupname, socialgroup.groupid, albumpicture.albumid,
                 groupmessage.title AS discussiontitle, 
                 thread.title AS threadtitle, thread.threadid,
                 forum.forumid,
                 album.title AS albumtitle,
                 announcement.title AS announcementtitle,
                 picture.caption AS picturetitle,
                 album.title AS albumtitle,
                 album.albumid AS albumid,
                 quotes.author AS quotetitle,
                 poll.question AS polltitle,
                 announcement.title AS announcementtitle,
                 userchangelog.oldvalue, userchangelog.newvalue                                                  
                 " . (($vbulletin->userinfo['userid']) ? ", itemread.itemid AS readed" : "") . "
          FROM " . TABLE_PREFIX . "sc_activitystream_item AS stream
          " . (($vbulletin->userinfo['userid']) ? " LEFT JOIN " . TABLE_PREFIX . "sc_activitystream_itemread AS itemread ON (itemread.itemid = stream.itemid AND itemread.userid = " . $vbulletin->userinfo['userid'] . ")" : "") . "
          LEFT JOIN " . TABLE_PREFIX . "user AS user1 ON(user1.userid = stream.userid1)
          LEFT JOIN " . TABLE_PREFIX . "user AS user2 ON(user2.userid = stream.userid2)
	  LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield1 ON (userfield1.userid = stream.userid1)
          LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield2 ON (userfield2.userid = stream.userid2)
          LEFT JOIN " . TABLE_PREFIX . "picture AS picture ON (stream.pictureid = picture.pictureid) 
          LEFT JOIN " . TABLE_PREFIX . "quotes AS quotes ON (stream.quoteid = quotes.quoteid)
          LEFT JOIN " . TABLE_PREFIX . "visitormessage AS visitormessage ON (stream.vmid = visitormessage.vmid)           
          LEFT JOIN " . TABLE_PREFIX . "poll AS poll ON (stream.pollid = poll.pollid)
          LEFT JOIN " . TABLE_PREFIX . "albumpicture AS albumpicture ON (albumpicture.pictureid = stream.pictureid)
          LEFT JOIN " . TABLE_PREFIX . "album AS album ON (stream.albumid = album.albumid OR albumpicture.albumid = album.albumid)
          LEFT JOIN " . TABLE_PREFIX . "picturecomment AS picturecomment ON (stream.picturecommentid = picturecomment.commentid)
          LEFT JOIN " . TABLE_PREFIX . "announcement AS announcement ON (stream.announcementid = announcement.announcementid) 
          LEFT JOIN " . TABLE_PREFIX . "post AS post ON (stream.postid = post.postid)
          LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid OR thread.pollid = poll.pollid OR stream.threadid = thread.threadid)
          LEFT JOIN " . TABLE_PREFIX . "forum AS forum ON (forum.forumid = thread.forumid OR announcement.forumid = forum.forumid)         
          LEFT JOIN " . TABLE_PREFIX . "groupmessage AS groupmessage ON (groupmessage.gmid = stream.gmid)
          LEFT JOIN " . TABLE_PREFIX . "discussion AS discussion ON (groupmessage.discussionid = discussion.discussionid)
          LEFT JOIN " . TABLE_PREFIX . "socialgroup AS socialgroup ON (discussion.groupid = socialgroup.groupid)
          LEFT JOIN " . TABLE_PREFIX . "userchangelog AS userchangelog ON (userchangelog.changeid = stream.userchangelogid)                   
          WHERE stream.state = 'visible' 
                AND (forum.forumid NOT IN ('" . implode("', '", $final_excludearray) . "') OR forum.forumid IS NULL)
                AND (post.visible = 1 OR post.visible IS NULL)
                AND (thread.visible = 1 OR thread.visible IS NULL)
                AND (album.state = 'public' OR album.state IS NULL)
                AND (poll.public = 1 OR poll.public IS NULL) 
                AND (visitormessage.state = 'visible' OR visitormessage.state IS NULL)
                AND (picturecomment.state = 'visible' OR picturecomment.state IS NULL)
                AND (picture.state = 'visible' OR picture.state IS NULL)
                AND (groupmessage.state = 'visible' OR groupmessage.state IS NULL)
                AND (socialgroup.state = 'visible' OR socialgroup.state IS NULL)
          ORDER BY stream.dateline DESC 
          LIMIT " . $vbulletin->options['sc_activitystream_defaultperpage'] . "   
          ");
Τελικές σημειώσεις:
α) Στην αρχή ήθελα να καταγράφω τη δραστηριότητα από τη στιγμή που θα εγκαταστήσω το Activity Stream στο site μου, όμως μετά κατέληξα στο ότι θέλω τα δεδομένα από την αρχή (αυτό βέβαια μπορώ να το κάνω και με έναν απλό importer).
β) Ο λόγος που γίνονται όλα αυτά τα JOINS είναι κυρίως για να τροφοδοτώ τους τίτλους κάθε contenttype, οι οποίοι μπορεί να αλλάζουν με edit. Οπότε στον πίνακα των items καταχωρώ μόνο τα ids.
γ) Μπορεί να φαίνεται ότι ο πίνακας των items δεν έχει λόγο ύπαρξης λόγω του α), όμως πως θα μπορούσα να κάνω mark as read συγκεκριμένα περιεχόμενα για ΚΑΘΕ userid; Δε θέλω να χρησιμοποιήσω cookies.
Τέλος θα ήθελα να αφαιρώ από το Activity Stream συγκεκριμένα items - άλλος ένας λόγος ύπαρξης του "sc_activitystream_item";

Θα χαρώ ως έμπειροι να μου προτείνετε μια βελτιωμένη λύση. :)

Άβαταρ μέλους
giannis17
Honorary Member
Δημοσιεύσεις: 1215
Εγγραφή: 06 Ιαν 2005 19:50
Τοποθεσία: Παγκράτι - Αθήνα
Επικοινωνία:

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από giannis17 » 21 Δεκ 2013 22:23

Θεωρητικά αν ορίσεις σωστά τα foreign keys σταν βάση και δημιουργήσεις ένα view για όλο αυτό ο χρόνος του query μπορεί να κατέβει από τα 80 στα 8 δευτερόλεπτα τα οποία όμως και πάλι είναι πολλά.

Με βάση τον τρόπο που είναι στημένη η βάση η πιο λογική λύση για να μην έχεις αυτή την καθυστέρηση θα ήταν να φτιαχτεί ένας καινούριος πίνακας user_activity με όλες τις πληροφορίες που μαζεύεις ο οποίος θα ανανεώνεται κάθε φορά που γίνεται ένα post ας πούμε ή κατά το logout. Τα οποία μπορούν να γίνουν με την $mysqli->real_query αφού δεν σε ενδιαφέρει να περιμένεις το αποτέλεσμα οπότε καθυστέρηση μηδέν. Μειονέκτημα μεν ότι δεν θα έχεις ποτέ 100% ενημερωμένο το sidebar σου αλλά δεν βλέπω πιο εύκολο τρόπο.
"There is only one problem with common sense; it’s not very common."
– Milt Bryce

Άβαταρ μέλους
philos
Δημοσιεύσεις: 260
Εγγραφή: 30 Αύγ 2007 23:32

LEFT JOIN βάσει τιμής σε table

Δημοσίευση από philos » 21 Δεκ 2013 23:47

Κι εγώ σε αυτό έχω καταλήξει (να έχω τον πίνακα με το user activity).

Το query το διόρθωσα και πλέον τρέχει άμεσα. Έφταιγαν οι OR στις ON των LEFT JOINS. Όπου υπήρχε OR την αφαίρεσα και έκανα δεύτερο LEFT JOIN table AS table2.

Απάντηση

Επιστροφή στο “MySQL”

Μέλη σε σύνδεση

Μέλη σε αυτήν τη Δ. Συζήτηση: Δεν υπάρχουν εγγεγραμμένα μέλη και 0 επισκέπτες