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

Different results with builder and raw SQL

Hi,

i get different results when i use the create builder or the phpmyadmin SQL Editor. So when i am logged in as user ID 48, i get only the result set of user with ID 48 with create builder, what is wrong for me!

     $users = $this->modelsManager->createBuilder()
       ->from('Vokuro\Models\Users')
       ->join('Vokuro\Models\Searchcriteria  ON Vokuro\Models\Searchcriteria.country =      Vokuro\Models\Users.country AND Vokuro\Models\Users.region  LIKE CONCAT(SUBSTRING(Vokuro\Models\Searchcriteria.zipcode, 1, 1),"%")')
       ->where('Vokuro\Models\Searchcriteria.usersId = :id:',array('id' => $id))
       ->orderBy('Vokuro\Models\Users.region')
       ->getQuery()
       ->execute();

But when i use raw SQL in phpmyadmin SQL Editor:

select * from Users
       join Searchcriteria  ON Searchcriteria.country = Users.country AND Users.region  LIKE CONCAT(SUBSTRING(Searchcriteria.zipcode, 1, 1),"%")
       where Searchcriteria.usersId = 48
       order By Users.region

I get the result set of user with ID 68, 69, what is totally correct for me!

Where is the difference? What i am doing wrong?

Thx for help



145.0k
edited Mar '17

Maybe check first what sql is generated by PHQL?(use some logger for example)

       ->join('Vokuro\Models\Searchcriteria  ON Vokuro\Models\Searchcriteria.country =      Vokuro\Models\Users.country AND Vokuro\Models\Users.region  LIKE CONCAT(SUBSTRING(Vokuro\Models\Searchcriteria.zipcode, 1, 1),"%")')

???

Second argument in join is ON conditions just change your code to:

->join('Vokuro\Models\Searchcriteria', 'Vokuro\Models\Searchcriteria.country = Vokuro\Models\Users.country AND Vokuro\Models\Users.region  LIKE CONCAT(SUBSTRING(Vokuro\Models\Searchcriteria.zipcode, 1, 1),"%")')

Also use some namespace aliases and sql aliases.



59.9k

Hi, thx for your reply. I changed your recommends.

I tried this logger, how can i call this. It is already implemented, but do i have to call a function?

https://forum.phalcon.io/discussion/1026/logging-sql-bind-params

Normally it should log each SQL query, or not?

Rgds



59.9k
edited Apr '17

Hi @Wojciech Ślawski,

i found a way to debug, this is the SQL:

    SELECT `users`.`id`, `users`.`name`, `users`.`email`, `users`.`password`, `users`.`mustChangePassword`, `users`.`profilesId`,   `users`.`banned`, `users`.`suspended`, `users`.`active`   

    FROM `users`  

    INNER JOIN `searchcriteria` ON `searchcriteria`.`country` = `users`.`country` 

    AND `users`.`region` LIKE CONCAT(SUBSTRING(`searchcriteria`.`zipcode`, 1, 1), '%') 

    WHERE `searchcriteria`.`usersId` = :id ORDER BY `users`.`region`

When i insert 48 for :id, the result is empty, no results.

hope you can help me.



145.0k

Inner join means if value exists in A AND B. Check this image for joins understanding:

here