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

LEFT JOIN not returning anything

I tested this query in SQL and it worked perfectly for returning all of the posts and if they are liked by member:

SELECT blog_posts., blog_likes. FROM blog_posts LEFT JOIN blog_likes ON blog_posts.postId = blog_likes.postId AND blog_likes.memberId = 5

I tried refracting it for use with PHQL:

SELECT Posts. , Likes. FROM Posts LEFT JOIN Likes ON Posts.postId = Likes.postId AND Likes.memberId = 5

I have created the relationships already inside the models, but for some reason it won't return anything. Using the query below will actually work by returning if it is liked or not but it won't return the post.

SELECT Posts.*, Likes.likeId, Likes.memberId FROM Posts LEFT JOIN Likes ON Posts.postId = Likes.postId AND Likes.memberId = 5

The above statement returns likeId and memberId but no post information like title, body, etc. Have any assumptions to why this isn't working?

cant really say if anything is wrong there but i suggest you log your query and see what SQL query does it generate: https://docs.phalcon.io/en/latest/reference/models.html#logging-low-level-sql-statements



10.4k

I tried the query that it logged and it worked in Terminal. Still have no idea what the problem could be.



10.4k
Accepted
answer

I figured out the issue. When you use select all from Posts, you have to explicitly state the column names you want to select. You can't use Posts.* but Posts.title, Posts.body, etc.