Database and query to store and retreive friend list [migrated]
- by amr Kamboj
I am developing a module in website to save and retreive friend list. I am using Zend Framework and for DB handling I am using Doctrine(ORM).
There are two models:
1) users that stores all the users
2) my_friends that stores the friend list (that is refference table with M:M relation of user)
the structure of my_friends is following
...id..........user_id............friend_id........approved....
...10.........20 ..................25...................1..........
...10.........21 ..................25...................1..........
...10.........22 ..................30...................1..........
...10.........25 ..................30...................1..........
The Doctrine query to retreive friend list id follwing
$friends = Doctrine_Query::create()->from('my_friends as mf')
->leftJoin('mf.users as friend')
->where("mf.user_id = 25")
->andWhere("mf.approved = 1");
Suppose I am viewing the user no.- 25.
With this query I am only getting the user no.- 30.
where as user no.- 25 is also approved friend of user no.- 20 and 21.
Please guide me, what should be the query to find all friend and is there any need to change the DB structure.
