We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. MVC

Inner join user following

Hey, i'm sorry if i create to many posts - I just need help with getting started with Phalco, and it's many functions :-)

What i'm trying to do is, that i would like to display all my users, and if user is following an user, it will display the text "You are following"

I'm trying to do this, but i dont think it's the right way to do it.. :

    $this->view->users = $this->modelsManager->createBuilder()
    ->from('Users')
    ->innerjoin('userfollows')
    ->orderBy('Users.id')
    ->limit(9)
    ->getQuery()
    ->execute();

    foreach ($this->users as $test) {
        if($test->following = $test->id){ // User id
            echo 'You are following'
        }
    }

Hope you understand :-)



11.6k

looks like you compare the id of the followed user to the user id of the same row, could you shortly represent your two tables structures?



40.6k

Sure

Users:

userfollows:



11.6k

ok,

I'm not familiar with the modelsManager, but I think it would be easier first to pass some conditions to filter the jointure, so no need to compare,just test result to check if rows from userfollows have been linked to the user row

    $users = $this->modelsManager->createBuilder()
    ->from('Users')
    ->join('Userfollows', 'Users.id = Userfollows.follower')
    ->getQuery()
    ->execute();

    foreach ($users as $user) {  //no need to compare, as obviously if a row have been joined, it's cause ids was resp.
        if($user->userfollows){ // or use isset() or  !empty()
            foreach($user->userfollows as $followed){  //here I suppose it's one-to-many relation
                echo 'You are following user with ID:' . $followed->following;
        }
    }


40.6k

That dosent give me all users, but only the users you are following, and if $user->follows dosent work, becuase userfollows dosent exists

What im trying to do is to fetch all users, and if you are following an user, you get the message "you are following this user"..



11.6k
edited Jul '15

this snippet should work:


    $users = Users::find();
    foreach ($users as $user) {
        $userFollow = Userfollows::find('follower = $user->id');
        if($userFollow) { // or try if(!empty($userFollow)
            foreach($userFollow as $f){  //here I suppose it's one-to-many relation
                echo 'You are following user with ID:' . $f->following;
        }


34.6k
in reply to graphmatic

You might want to change it to avoid SQL injections:

$userFollow = Userfollows::findByFollower($user->id);

this snippet should work:


  $users = Users::find();
  foreach ($users as $user) {
      $userFollow = Userfollows::find('follower = $user->id');
      if($userFollow) { // or try if(!empty($userFollow)
          foreach($userFollow as $f){  //here I suppose it's one-to-many relation
               echo 'You are following user with ID:' . $f->following;
       }


40.6k

It dosent work either.. I think I have to use inner join, but I dont know how..

But thanks for trying to help me out, it really means alot :-)!



11.6k

that should work, the only part I'm unsure may be the way I propose to test the resultset

you also have to import your UserFollow model into the Users one (at top of usersController file add "use Userfollows;")



40.6k

I have talk to someone, and he said I have to use leftjoin or rightJoin ?

Yes, i does past out data, but dosent implement the "You are following" in the right id..



40.6k

I found the solution, my SQL has to be:

SELECT users.firstname, users.lastname, users.id, userfollows.following as following 
FROM users
LEFT JOIN userfollows ON userfollows.following = users.id

Now im just trying to find a way, to do this with modelsmanager->createbuilder();