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. Beginners

Get newest user, not the first in inner join

Hey,

How to I get the latest user, and not the first user that made a notification?

User is author

This is my code

$notifications = $this->modelsManager->createBuilder()
        ->columns(array('COUNT(*) as count', 'Notifications.type', 'Notifications.userid', 'Notifications.postid', 'Notifications.time', 'Users.firstname', 'Users.lastname', 'Users.profileimage', 'Posts.title', 'Posts.image'))
        ->from('Notifications')
        ->join('Users', 'Notifications.author = Users.id')
        ->leftJoin('Posts', 'Notifications.postid = Posts.id')
        ->groupBy(array('Notifications.postid', 'Notifications.type'))
        ->orderBy('Notifications.id DESC')
        ->getQuery()
        ->execute();

        return $notifications;


145.0k

Order by Notifications.author DESC not working ?



16.0k

that query should return the last notification, but the join should be done on

Notifications.userid = Users.id

and not on author

Do you get any errors, or bad results?



40.6k

My code works fine it just fetches the very first user who made a notification, i need it to display the latest user

Rememeber it's inside a group by, so if i made a notification: "Fred commented on your post" Then if another one made a notification "Bob and 1 more made a comment on your post"

But it still says "Fred and 1 more made a comment on your post"

..... Hope you understand! :-)



85.5k
edited Oct '15

this might help you... as I said inside I am not a master of those queries, takes me half an hour to make it work:

https://forum.phalcon.io/discussion/8559/phalcon-leftjoin-limit-1

my other suggestion would be:

make the query in raw php query, you know... "SELECT * FROM Bla bla bla " and post the question in stack overflow. This is how I expanded my knowlage of sub queires and I had exact same issues as you do.