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

Phalcon leftJoin limit 1

I'm trying to figure out, how I can limit my leftJoin match by 1, but keep getting the other rows from the table.

This is my code:

$comments = $this->modelsManager->createBuilder()
        ->columns(array('PostsComments.comment', 'PostsCommentsAnswers.comment as answer'))
        ->from('PostsComments')
        ->leftJoin('PostsCommentsAnswers', 'PostsCommentsAnswers.commentid = PostsComments.id')
        ->orderBy('PostsComments.id')
        ->limit(4)
        ->getQuery()
        ->execute();

How do i do, it will only get the first like this: "commentid = PostsComment.id limit 1" so that all others will be ignored ? :-)

Thanks!



85.5k
edited Sep '15

you have to add another query inside instead of the join. Unfortunatly i am not master of those subquery joins etc... basiccaly it takes me 10 mins playing with the query until i make it work.

Anyways... take the full query, paste in phpmyadmin / workbench and play with it until you manage it.

https://stackoverflow.com/questions/11388443/limiting-a-left-join-to-returning-one-result

my try: 


SELECT PostsComments.comment, PostsCommentsAnswers.comment as answer
    FROM PostsComments
LEFT JOIN ( SELECT comment FROM PostsCommentsAnswers WHERE PostsCommentsAnswers.commentid = PostsComments.id LIMIT 1) as toto

LIMIT 4

i am sure the query wont work, but it's a start :D

sorry i cant help you more but i am at work atm



4.7k

Are you trying to get the most recent comment for each post?



18.5k
edited Sep '15

There are couple of solutions that owuld work. But i guess it depends on what you are using the response for and how you are using them.

Using magic getters + params

$PostsComments->getPostsCommentsAnswers(['limit' => 1]);

Adding custom function to your PostsComments model

public function getFirstAnswer(){
    return $this->getNotes(['limit' => 1]);
}

Finally if you are detemrined to use your builder to return a single array. you coudl probably use php ->groupBy(PostsComments.id)

https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html