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

Join tables via model

Hi, I am trying to find a way to easyly join tables through phalcon to fetch data related to 1 object in 1 query.

I have these relation ships in my models

$this->hasMany("playerid", "Vehicles", "pid");
$this->hasMany("playerid", "Houses", "pid" );
$this->belongsTo("playerid","Gangs","owner");

I wonder if their is a way to tell phalcon to automaticly join these tables when i do Players::findFirstByPlayerid($id) other frameworks have a method ->with(["table1","table2"]) is there anything simular in phalcon apart from building the query manually?

atm i am doing

$player = Players::findFirstByPlayerid($id);
$this->view->player = $player;
$this->view->player->bankacc = $this->formatBank($player->bankacc);
$this->view->player->vehicles = $player->getVehicles('alive = 1');
$this->view->player->houseCount = $player->houses->count();

Currently 3 queries are being made: https://blackfire.io/profiles/3bac4bd9-87e1-46ce-87e2-325329966cd3/graph?settings%5Bdimension%5D=wt&settings%5Bdisplay%5D=focused&settings%5BtabPane%5D=nodes&selected=&callname=%40sql.queries it would be great if I can cut this down to 1 using the joins



145.0k


8.2k
in reply to Wojciech Ślawski
edited Jul '17

https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Mvc/Model

But much better is to use just modelsManager.

I tried to using the modelManager->queryBuilder(), but I keep getting non-unique table alias issues, even when the tables is aliased uniquely, also that package is for phalcon 2, will it work with phalcon 3?



145.0k
edited Jul '17

It's for phalcon 3.2.0 it should work with phalcon 3 too.

What you mean non-unique table alias issues? What's exactly the error? Post some example because im not sure what you talk about, i don't have any issues, i use query builder almost everywhere.



8.2k
in reply to Wojciech Ślawski
edited Jul '17

It's for phalcon 3.2.0 it should work with phalcon 3 too.

What you mean non-unique table alias issues? What's exactly the error? Post some example because im not sure what you talk about, i don't have any issues, i use query builder almost everywhere.

I am not home atm, but I did it along these lines

$player = $this->modelManager->queryBuilder()
->from(['Players', 'v' => 'Vehicles', 'h' => 'Houses'])
->innerJoin('Vehicles', 'Players.playerid = Vehicles.pid', 'v')
->innerJoin('Houses', 'Players.playerid = Houses.pid', 'h')
->where('Players.playerid = :playerid:')
->bind(['playerid' => $id])
->getquery()
->execute();

I would get that alias v is non-unique, no-matter what I changed it too, even random characters.



145.0k
edited Jul '17

Yea beacause you added here v two times in from and in innerJoin. It should be:

$player = $this->modelManager->queryBuilder()
->from('Players')
->innerJoin('Vehicles', 'Players.playerid = Vehicles.pid', 'v')
->innerJoin('Houses', 'Players.playerid = Houses.pid', 'h')
->where('Players.playerid = :playerid:')
->bind(['playerid' => $id])
->getquery()
->execute();


8.2k

Alright, when im home ill see if that works.



145.0k

It will though not sure what will be returned, perhaps you need to add ->columns('*'). I always select columns which i want anyway.



8.2k
edited Jul '17

Alight that works, but its not return data nested. I need to get 1 person from the database, with all their vehicles and all their houses.

code

$player = $this->modelsManager->createBuilder()
->columns('Players.*, v.*, h.*')
->from('Players')
->innerJoin('Vehicles', 'Players.playerid = v.pid', 'v')
->innerJoin('Houses', 'Players.playerid = v.pid', 'h')
->where('Players.playerid = :playerid:',['playerid' => $id])
->andWhere('v.alive = 1')
->getQuery()
->getSingleResult();

Outputs

https://gist.githubusercontent.com/JABirchall/dd05a6bd378323e6ffc5559451e7cb12/raw/b93edb530dc30ca76a491e4f6e6f579b1df17011/2017-07-26_18-43-32.txt

The person should have multiple vehciles and houses

Also, I only need to return a count of houses since this: is the the only output required for them. But adding count(h) errors out with

Column "h" doesn't belong to any of the selected models (1), when preparing: SELECT Players.*, v.*, count(h) FROM [Players] INNER JOIN [Vehicles] AS [v] ON Players.playerid = v.pid INNER JOIN [Houses] AS [h] ON Players.playerid = v.pid WHERE (Players.playerid = :playerid:) AND (v.alive = 1)



145.0k

No it doesn't return nested, this is expected return and correct. If you want nested then you need eager loading, just check repo above. But eager loading is anti-pattern. One and only correct way is imho to use models manager and no nested.