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

Limit BUG with Joins (left or right)

Hi, i have a problem when i want to retrieve data from the database. I am executing the following SQL via the queryBuilder.

'SELECT [model_facebookpost_2].* FROM [Model\FacebookPost] AS [model_facebookpost_2] LEFT JOIN [Model\FacebookPostContent] AS [model_facebookpostcontent_1] ON model_facebookpost_2.id = model_facebookpostcontent_1.fbPostId LEFT JOIN [Model\FacebookPostLink] AS [model_facebookpostlink_1] ON model_facebookpost_2.id = model_facebookpostlink_1.id WHERE model_facebookpost_2.accountId = :model_facebookpost_2_eq_0: ORDER BY dateCreated DESC LIMIT :APL0:'

I have set the limit to 10. When I execute the SQL with the LEFT JOIN i get 4 datasets from the db. When I execute it without the LEFT JOIN i get 10 datasets. I also want to get 10 dataset when i include the LEFT JOIN. Can anybody help me or does anyone know a workarround?

Phalcon Version: 3.2.1 PHP Version: 7.0.22 DBMS: MySQL OS: Ubuntu

Thanks Tom

1


93.7k
edited Dec '17

Not sure about your table structure, but maybe its this join that causes trouble:

LEFT JOIN [Model\FacebookPostLink] AS [model_facebookpostlink_1] ON model_facebookpost_2.id = model_facebookpostlink_1.id

Shouldn't this model_facebookpostlink_1.id be model_facebookpostlink_1.fbPostId ? Like so:

LEFT JOIN [Model\FacebookPostLink] AS [model_facebookpostlink_1] ON model_facebookpost_2.id = model_facebookpostlink_1.fbPostId

Since you are joining posts table ID with links table ID, instead of fbPostID in links table?



397
in reply to Nikolay Mihaylov

Thanks but the join is correct. Becouse model_facebookpost_2 and model_facebookpostlink_1 has an 1 to (0 or 1) relation, the model_facebookpostlink_1.id has the same id as model_facebookpost_2.id. Even if the join was incorret we also musst get 10 datasets. Becouse with the left join we musst get the related dataset or null. Not only the dataset, that has a related model_facebookpostlink_1. I only get this bug when i am using the limit parameter without the limit parameter the sql works well.

Not sure about your table structure, but maybe its this join that causes trouble:

LEFT JOIN [Model\FacebookPostLink] AS [model_facebookpostlink_1] ON model_facebookpost_2.id = model_facebookpostlink_1.id

Shouldn't this model_facebookpostlink_1.id be model_facebookpostlink_1.fbPostId ? Like so:

LEFT JOIN [Model\FacebookPostLink] AS [model_facebookpostlink_1] ON model_facebookpost_2.id = model_facebookpostlink_1.fbPostId

Since you are joining posts table ID with links table ID, instead of fbPostID in links table?