使用Codeigniter查询朋友/博客数据库

Question

I'm making a social media kind of website, where people can post messages and become friends and such.

And so I have a database with the following tables.

Friends

+-----+-----------+----------+----------+--------+
| id  | Bevriend  | UserID1  | UserID2  | vType  |
+-----+-----------+----------+----------+--------+
|  1  |        1  |       1  |       3  |      0 |
+-----+-----------+----------+----------+--------+

Blog

+-----+----------------+-------+---------+
| id  |     title      | text  | userid  |
+-----+----------------+-------+---------+
|  1  | My first entry | Test  |       1 |
+-----+----------------+-------+---------+

I want to write a query of sorts to get the blog posts only from the person I am friends with. So long as either UserID1 or UserID2 contains the userID from my session AND in the database the bit known as "Bevriend" is set to 1 it should get the records.

I am at a loss, if I need to supply you with more information please let me know.

Answer 1

I am giving you a query that might help. It takes only items from blog which are posted by friend, but not current user. You query should look like (excluding blog posts where the current user is the author):

SELECT
    b.*
FROM Blog b
INNER JOIN Friends f ON (b.userid = f.UserID1 OR b.userid = f.UserID2)
WHERE
    f.Bevriend = 1
AND
(
    b.userid = f.UserID1 AND f.UserID2 = '{$currentUserID}'
OR
    b.user_id = f.UserID2 AND f.UserID1 = '{$currentUserID}'
)

What we do here is to JOIN the table this way that either UserID1 or UserID2 appear as Blog record "owner". Then in the where clause we say give me only these blog records where where the owner is my friend and not me, for each column UserID1 and UserID2.

This should look like in CI (including the blog posts where current user is the author):

$currentUserID = (int) $this->session->userdata('userID');
$page = (int) $this->input->get('page'); // example retrieving of desired page num
$pagesize = (int) $this->config->item('blog_pagesize'); // example retrieving of pagesize

// Prevent unexpected behavior
if(0 >= $page)
    $page = 1;

// Prevent unexpected behavior
if(0 >= $pagesize)
    $pagesize = 20; // If config is wrong we ensure it will continue to work

// Additional page checks go here...

$query = $this->db->query('
    SELECT
        b.*
    FROM Blog b
    INNER JOIN Friends f ON (b.userid = f.UserID1 OR b.userid = f.UserID2)
    WHERE
        f.Bevriend = 1
    AND
    ( f.UserID2 = ? OR UserID1 = ?)
    LIMIT ?, ?
', array($currentUserID, $currentUserID, ($page - 1) * $pagesize, $pagesize));

 foreach ($query->result() as $row) {
     echo $row->id;
     echo $row->title;
     echo $row->text;
 }

 // Clean up at the end
 $query->free_result();

You could adapt the columns you want to retrieve, ordering and remove duplications with GROUP BY or DISTINCT. We run custom query here, because is much more cleaner that using $this->db->select, $this->db->from, $this->db->join,$this->db->where, etc. when the query got more complex. If you use them you have to pass third paramater true to disable each of these functions escaping and it will look like "ugly code".

You cannot use pagination directly with custom query through $this->db->query. This is because it is CI query, but not an active record. Below I translate the query into active record one.

To use the default CI active record functionality:

$results = $this->db->select('b.*')
     ->from('Blog b')
     ->join('Friends f', 'b.userid = f.UserID1 OR b.userid = f.UserID2')
     ->where('f.Bevriend', 1)
     ->where("(f.UserID1 = {$currentUserID} OR f.UserID2 = {$currentUserID})")
     ->group_by('b.id')
     //->get()
     //->results();

The example above should enable you to use the default pagination, although I find it not very useful and flexible.