SQL Join

[eluser]iainco[/eluser]
Hey guys, having trouble getting information from my database that represents which feeds have been selected by a user.

Database tables:
user_feed (userID, feedID) (Association between a user and a sports news feed)

feed (ID, sportID, categoryID, title) (Basic information for a sports news feed)

Code:

function fetchFeeds($categoryID)
{
    $this->db->select('feed.ID, feed.title, user_feed.feedID');
    $this->db->where('feed.categoryID', $categoryID);
    $this->db->from('feed');
    $this->db->join('user_feed', 'user_feed.feedID = feed.ID', 'left');

    return $this->db->get()->result_array();
}

The above works, and shows me all feeds that the current logged in user has saved and all feeds they haven't saved (for each category).

This is what I am looking for, but this query also shows me feeds that other users have saved - because I'm not using the current user's ID any where in my query.

Code:

function fetchFeeds($categoryID)
{
    $this->db->select('feed.ID, feed.title, user_feed.feedID');
    $this->db->where(array('feed.categoryID' => $categoryID, 'user_feed.userID' => $this->session->userdata('ID')));
    $this->db->from('feed');
    $this->db->join('user_feed', 'user_feed.feedID = feed.ID', 'left');

    return $this->db->get()->result_array();
}

But when I try this, it shows me only the feeds the current logged in user has saved (and not the feeds he/she hasn't saved)

I know the solution is using the join correctly but having tried all types (left, right, inner, outer, left outer, right outer) and not getting the result I was looking for I thought I'd post here and ask for some help.

Thanks!

Iain

[eluser]Randy Casburn[/eluser]
Hi Lain,

I'm a little slow, so feed me some more...

I see the table, I see the categoryID, etc, but...

You mention this trigger/flag/constraint/condition/etc. called:

has saved and all feeds they haven’t saved

Where is [saved] vs. [they haven't saved] described in your post exactly?

Randy

[eluser]iainco[/eluser]
Hi Randy, thanks for your post.

A user saves (or subscribes) to a feed by checking a box in the view. In terms of the database, if the user with ID 1 has saved the feed with ID 1 there will be a row in the `user_feed` table with userID = 1 and feedID = 1.

Feeds that users have not saved are still displayed in the view, but nothing is stored for them in the `user_feed` table, and the feeds the user has saved have their checkboxes ticked automatically.

I should add that, fetchFeed should return an array like this:

Array (
[0] Array (ID = 1, title = 'Feed 1', feedID = null) //user has not saved feed with ID 1
[1] Array (ID = 2, title = 'Feed 2', feedID = 2) // user has saved feed with ID 2
[2] Array (ID = 3, title = 'Feed 3', feedID = null) // user has not saved feed with ID 3
)

.. if there are only 3 feeds present in the feed table.

I can use this code so the correct checkboxes are checked automatically.

Code:

input type="checkbox" onclick="saveFeed(<?=$i['ID']?>)" <?=($i['feedID'] == $i['ID']) ? 'checked':''?>

Iain

[eluser]Randy Casburn[/eluser]
Right. You'll need to accomplish an OUTER join for this. An OUTER join SELECTs a portion of one result set (in your case it is the set of feeds "saved" by this "userID") while SELECTing an entire result set from another source (in your case this would be the whole set of available feeds whether "saved" or "unsaved" by any "userID").

Whether it is a "righty" or a "lefty" OUTER join simply depends on which table you put on the left or right in the select statement.

Go back and put together a RIGHT OUTER join with the feed table as the "righty" and I think you'll be happy.

Randy

[EDIT] - had to edit this for fear of getting a back wash from the more astute crowd of very smart folks here. Mathmatically speaking, Dr. Codd would be very displeased as I did not digress into Set Theory in the explanation about the OUTER JOIN. Suffice it to say you can blog about that and I acknowledge my poor layman's explanation

[eluser]iainco[/eluser]
Hi Randy, thanks for your help.

Code:

function fetchFeeds($categoryID)
{
    $this->db->select('feed.ID, feed.title, user_feed.feedID');
    $this->db->from('feed');
    $this->db->join('user_feed', 'user_feed.feedID = feed.ID AND user_feed.userID = ' . $this->session->userdata('ID'), 'left');
    $this->db->where('feed.categoryID', $categoryID);

    return $this->db->get()->result_array();
}

I found this to work exactly as I need it to... but it doesn't match with what you said.

Thanks

[eluser]Randy Casburn[/eluser]
Are you sure?

What if there are feeds in the feeds table that no user has "saved". Those feeds will not be returned in your result set. Hence the need for OUTER JOINs in the first place.

Randy

[eluser]iainco[/eluser]
Yup, I've tested it across multiple user accounts all with different feeds saved. Some feeds aren't saved by any users and are still displayed, as they should be...

Iain

[eluser]Randy Casburn[/eluser]
I see what I didn't saw then I were smarter...

Right- you just lopped on the whole table with the join...duh.

Randy