• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Multiple table joins

#1
To make maintenance easier and possibly get better performance I have split a large table 'widgets' with many columns into 4 smaller tables; widgets, tags, links and notes. For each widget row there are a fixed number of tags but a widget row can have any number of links or notes - from none to many. 

The widget table has columns: widgetID, name, color and foreign key userID from a 'users' table.
The tags table has columns: tagID, tagName, and foreign key: widgetID.
The links table has columns: linkID, linkDesc, linkURL and foreign key widgetID.
The notes table has columns: notesID, dateEnt, dateLastEdit and foreign key widgetID.

The xID columns are all PRIMARY KEY AUTO_INCREMENT except when they're foreign keys.  I'm using mysqli driver.

This is my query to create array of objects that can populate all the variables in a grid view:
Code:
public function getWidgetGridData() {
   $userID=$this->session->userdata('userID');
    $objArr=$this->db
        ->select(
           'A.widgetID, A.name, A.color, 
            B.tagBank1, B.tagBank2, B.tagBank3, B.tagBank4,
            C.linkID, C.linkDesc, C.linkURL,
            D.dateEnt, D.dateLastEdit, D.content'
        )
        ->from('items A')
        ->join('tags B','A.widgetID= B.tagID', 'left')
        ->join('links C','A.widgetID= C.linkID', 'left')
        ->join('notes D','A.widgetID= D.noteID', 'left')
        ->where('A.userID',$userID)
        ->order_by('A.title','asc')
        ->get()->result(); 
   
     return $objArr // should return array of objects to controller (one for each select col)
It kind of works but never correctly yet. I tried various combos of link types and various sequence of statements. Sometimes it repeats some widget rows a number of times that matches the number of links for that widget. (Should be only one row per widget). Sometimes it doesn't list a widget row at all when there are no links present for that widget. Sometimes it just lists a single widget row - maybe associated with the fact there's only one note for one widget in the note table. When I comment out the links and notes joins, it works fine (i.e with just the tags join).
Any suggestions greatly appreciated.
Reply

#2
Your joins are linking A.widgetID to B.tagID, whereas they need to be A.widgetID to B.widgetID (linking to the foreign key). Otherwise you'll be linking data arbitrarily.

Try updating your joins to all link to the foreign key, rather than the primary.

PS: The reason you may have seen the tags join working (although it is incorrect) may just be due to lack of test data/variety. Your tagID's likely were the same as the widgetID in your test case.
Reply

#3
(11-16-2017, 06:41 PM)JayAdra Wrote: Your joins are linking A.widgetID to B.tagID, whereas they need to be A.widgetID to B.widgetID (linking to the foreign key). Otherwise you'll be linking data arbitrarily. Try updating your joins to all link to the foreign key, rather than the primary. PS: The reason you may have seen the tags join working (although it is incorrect) may just be due to lack of test data/variety. Your tagID's likely were the same as the widgetID in your test case.

Thanks Jay. The code I showed above was my attempt at abstracting what I thought were the relevant pieces but after so many hrs over the last few days my brain was not seeing what I was typing. Here is the actual model function I have running now, un-abstracted.


Code:
public function getTunesGrid() {
        $memID=$this->session->userdata('memID');
        $tunesMainArr=$this->db
            ->select(
                   'A.tuneID, title, newLyrics,
                    tm1, tm2, tm3, tm4,
                    linkID, linkDesc, linkURL, noteID'
                )
            ->from('mztunes A')
            ->join('mztags B','A.tuneID = B.tuneID', 'left')
            ->join('mzlinks C','A.tuneID = C.tuneID', 'left')
            ->join('mznotes D','A.tuneID = D.tuneID', 'left')
            ->where('A.memID',$memID)
            ->order_by('A.title','asc')
            ->get()->result(); // makes tunesMainArr an array of tune objects
        
        foreach ($tunesMainArr as $tune) {
            echo    "tid: ".$tune->tuneID.", title: ".$tune->title.
                    ", tm1: ".$tune->tm1.", tm2: ".$tune->tm2.", tm3: ".$tune->tm3.", tm4: ".$tune->tm4.
                    ", linkID: ".$tune->linkID."<br>";  
        }
        exit();

 I appended that echo so I could see just what the query produced. There are actually 243 $tune objects in the mztunes database that satisfy the '->where('A.memID',$memID)' method

Here's the first several lines of the echo:

tid: 2283, title: A Beautiful Life (Stanley Bros), tm1: 9, tm2: 0, tm3: 0, tm4: 4, linkID: 277
tid: 2216, title: Abe's Retreat, tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 241
tid: 2277, title: Acony Bell, tm1: 64, tm2: 0, tm3: 0, tm4: 4096, linkID: 
tid: 2077, title: Adieu False Heart (Jody Stecher and Kate Breslin), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 236
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 134
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 9
tid: 1411, title: Adieu False Heart (Linda Rondstadt and Anne Savoy), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 112
tid: 1408, title: Alabama Bound (Doc Watson), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 105
tid: 1408, title: Alabama Bound (Doc Watson), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 106
tid: 1408, title: Alabama Bound (Doc Watson), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 107
tid: 105, title: All About You (from Lee Stripling), tm1: 4, tm2: 4, tm3: 0, tm4: 0, linkID: 
tid: 2223, title: Anchored in Love Divine (Annie and Mac), tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID: 254
tid: 2009, title: Angeline the Baker, tm1: 0, tm2: 1, tm3: 0, tm4: 0, linkID: 
tid: 2285, title: Another Cup of Joe, tm1: 0, tm2: 0, tm3: 0, tm4: 0, linkID:

There should be only one row per tuneID. Tune 1411 actually has 4 links, tune 1408 has 3 but I somehow need to get those onto the same tune row - I think.

What I'm trying to do is have all the links for a tune appear as properties of the one tune array element to which they belong. That seems to be a bridge too far for my brain right now.

This is the view at this stage showing each tune on one panel. When user clicks the links btn that tune for example, the links for that panel are 'toggled' and will appear just below the top line but in the same 'panel'. 

Note that the tm1 . . . 4 columns are tagMask1. . 4 stored as INT. They appear as expected in the echo above and also below the top line in the view below when the Tags btn is clicked for that tune.


Attached Files Thumbnail(s)
   
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.