how to query many-to-many relationship in same table by redbeanphp?

Question

I have a table named "user" and this table has many to many relation over its own such as favorite action. this means a user can favorite multiple users and can be Favorited by multiple users.

now I want query over this relation and get every users those Favorite a spacial user.

I use this code :

$users = $agent->via($TB_Favorite,"agent_id=? and favorite.action=?", array($userId,FAVORITE_TYPE_USER_FAVORITE))->sharedUsers ;

I debug this code by profiling database and get this result:

        SELECT
            `users`.*  ,
            COALESCE(
            NULLIF(`favorite`.`users_id`, `users`.id),
            NULLIF(`favorite`.`users2_id`, `users`.id)) AS linked_by
        FROM `favorite`
        INNER JOIN `users` ON
        ( `users`.id = `favorite`.`users2_id` AND `favorite`.`users_id` IN ('13') ) OR
        ( `users`.id = `favorite`.`users_id` AND `favorite`.`users2_id` IN ('13') )

but in this case it must use favorite.agent_id instead favorite.users2_id how can i resolve this problem ?

this is my table diagram :

may be this make my position clear.

Answer 1

Okay, thanks for the diagram and the additional explanation. So, you want to query the users associated with the favorite activities of a user. Here's one possible solution (tested on an empty db here):

$x = R::dispense(array(
'_type' => 'user',
'name' => 'Q',
'ownActivity' => array(
    array('_type'=>'favorite', 
         'name'=>'A', 
         'agent'=> array(
              '_type'=>'user', 
              'name'=>'X')),
    array('_type'=>'favorite', 
          'name'=>'B', 
          'agent'=> array(
              '_type'=>'user', 
              'name'=>'Y')),)));
R::store($x);
$me = R::findOne('user', ' `name` = ? ', array('Q'));
$favoriteActivities = $me->ownFavoriteList;
foreach($favoriteActivities as $activity) {
    $agent = $activity->fetchAs('user')->agent;
    $favoriteUsers[$agent->id] = $agent;
    echo $agent->name;
}