Join/compare multiple tables with querybuilder

Question

Join/compare multiple tables with querybuilder

Stefjoe Jan '17

Created Jan '17	Last Reply Jan '17	Replies 5	Views 1214	Votes 0

Stefjoe 59.9k

Jan '17

Hi,

I have a search filter table where i can save searchcriteria, like country, region, status. I have also separate tables country, region, status.

Now i want to find all Users, where searchcriteria.country = country.country AND searchcriteria.region = region.region

For that i use the queryBuilder:

     $users = $this->modelsManager->createBuilder()
       ->from('Vokuro\Models\Users')
       ->join('Vokuro\Models\Searchcriteria',null,'s')
       ->join('Vokuro\Models\Region',null,'r')
       ->join('Vokuro\Models\Country',null,'c')
       ->where('Vokuro\Models\Users.id != '.$id.' AND s.usersId = '.$id)
       ->andwhere("s.zipcode = r.region")
       ->andwhere("s.country = c.country")
       ->getQuery()
       ->execute();

The table searchcriteria has a usersId with the search data. So i think i have to choose the usersId first and then i have to select each other table. But how?

This is what i get

Dispatcher has detected a cyclic routing causing stability problems
#0 [internal function]: Phalcon\Mvc\Dispatcher->_throwDispatchException('Dispatcher has ...', 1)
#1 [internal function]: Phalcon\Dispatcher->_dispatch()
#2 [internal function]: Phalcon\Dispatcher->dispatch()
#3 C:\xampp7013\htdocs\site\public\index.php(33): Phalcon\Mvc\Application->handle()
#4 {main}

Thx

ntesic · Answer 1 · 2017-01-11T15:47:35-07:00

This error have nothing to do with querybuilder. This is dispatcher error and probably you have problems with defined routes or wrong redirection in controller

Wojciech Ślawski · Answer 2 · 2017-01-11T17:00:11-07:00

Vokuro\Models - i would recommend to use namespace model alias. So you can have M:Users etc. Also don't do this:

'Vokuro\Models\Users.id != '.$id.' AND s.usersId = '.$id Don't use iterals ! This mean your application can be sql injected, use parameter binding.

Stefjoe · Answer 3 · 2017-01-12T03:22:07-07:00

Hello,

i changed the bindings.

 $users = $this->modelsManager->createBuilder()
   ->from('Vokuro\Models\Users')
   ->join('Vokuro\Models\Searchcriteria',null,'s')
   ->join('Vokuro\Models\Region',null,'r')
   ->join('Vokuro\Models\Country',null,'c')
   ->where('Vokuro\Models\Users.id != :uId: AND s.usersId = :usersId:', array('uId' => $id, 'usersId' => $id))
   ->andwhere("s.zipcode = r.region")
   ->andwhere("s.country = c.country")
   ->getQuery()
   ->execute();

But the QueryBuilder is not working with the Vokuro namespaces, i will check that again. Do you have any idea, how i can compare the table searchcriteria with the other tables?

I also tried raw sql, but i only get 1 record.

Rgds

Wojciech Ślawski · Answer 4 · 2017-01-12T04:12:07-07:00

What you mean no working? For me your code is correct and fine. What problem you have with this code? It should work without any problem. I have similar code in my app and works fine. You got only one record because obviously there is only one user with this id i guess ?

Stefjoe · Answer 5 · 2017-01-12T04:41:11-07:00

I meant this:

 $users = $this->modelsManager->createBuilder()
 ->from('Users')
 ->join('Searchcriteria',null,'s')
 ->join('Region',null,'r')
 ->join('Country',null,'c')
 ->where('Users.id != :uId: AND s.usersId = :usersId:', array('uId' => $id, 'usersId' => $id))
 ->andwhere("s.zipcode = r.region")
 ->andwhere("s.country = c.country")
 ->getQuery()
 ->execute();

Yes there is only this 1 user with this id(13). What i want ist to get the result of searchcriteria from this user(13) and compare these records with region, country and status. After that all users where this is true should be displayed, here i can't find a solution and need some help.

Rgds

Wojciech Ślawski · Answer 6 · 2017-01-12T05:04:45-07:00

Then you need to select all columns like ->columns(*). Then you can access Searchcriterai using your s alias from Row object.

Wojciech Ślawski · Answer 7 · 2017-01-12T05:08:50-07:00

Also if models have namespace you can't use them like this. You need to use:

$modelsManager->registerNamespaceAlias('M', 'Vokuro\Models');

And then M:Users M:Searchcriteria etc