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

A cry for help while using joins in Phalcon

Hello, For the past three hours I've been trying to write this small piece of code and with about 30 stackoverflow and phalcon forum tabs open and out of sheer frustration I'm asking for your help.

I have two mysql tables:

CREATE TABLE `inventory` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `type` text NOT NULL,
 `manufacturer` int(5) NOT NULL,
 `model` text NOT NULL,
 `entered_time` int(15) NOT NULL,
 `entered_by` varchar(128) NOT NULL,
 `picture` varchar(128) NOT NULL,
 PRIMARY KEY (`id`)
)
CREATE TABLE companies (
 id int(9) NOT NULL AUTO_INCREMENT,
 name varchar(128) NOT NULL,
 address text NOT NULL,
 phone varchar(32) NOT NULL,
 entered_time int(15) NOT NULL,
 picture varchar(128) NOT NULL,
 email varchar(128) NOT NULL,
 contact_person text NOT NULL,
 PRIMARY KEY (id)
)

And two empty models for both of them. Each item in the inventory table has to be associated with a company in the companies table based on inventory.manufacturer = companies.id. I know there's a way to establish relationships between those two tables but I can not figure out how to properly do it. I've tried to use the query builder but I would only get results from the first table using this code:

use EQAS\Models\Inventory;
use EQAS\Models\Companies;
// [...]
$items = $this->modelsManager->createBuilder()
            ->from('\EQAS\Models\Inventory')
            ->join('\EQAS\Models\Companies', '[\EQAS\Models\Inventory].manufacturer = co.id', 'co', 'LEFT')
            ->getQuery()->execute();

I realized that the actual sql query looks like SELECT inventory.* FROM [...] which means that the companies information is not being selected. While searching for solution I've seen a piece of code where someone uses the "columns" call to establish what is being selected. This however creates a mess out of the returned data and doesn't work.

The best solution for me would be to take advantage of the relationships and I would appreciate an example based on my code structure.

Best regards, Szymon.



8.1k
edited May '14

Did you have a look at you RDBMS' query log? That shows you the actual query (if enabled), and in your case/example you should see a query selecting all columns of Inventory: inventory.id, ... etc

You should provide columns to your builder. Using * will get you all, but Phalcon will, in that case, rename (alias) all columns in the form: _TABLEALIAS_COLUMNNAME (edit: it seems that is used internally by Phalcon, so one can e.g. do $row->inventory->id. But this doesn't seem to work properly for me.)

So I guess you're best off providing the columns (and alias-ing them) yourself manually.

Just play with it a little, and check your query log. Best way to learn how the builder works / what Phalcon is doing I guess.

Btw - instead of using an array, you can also provide the columns as a string

Btw#2 - As I said using * for columns doesn't seem to work properly? When I do a var_dump of the Row I see stuff grouped alright, but it seems to use the name of the last column in the table as the key of a group, instead of the table/model alias. Is this a bug? (anyone?)



981

Using the results as $row->inventory->id would be perfect but like you said it doesn't seem to work. I also confirm your "btw#2", I also get a row with index named after the last column in the database. I'll post it as a bug on github when I get home. Honestly this seems like a lot of work (aliasing on my own, etc.) for something that Phalcon was supposed to simplify for me. Thanks for the answer.



8.1k
Accepted
answer
edited May '14

Well, It does seem to work, but you have to explictly select all from all tables (models): [\EQAS\Models\Inventory].*, co.* https://docs.phalcon.io/en/latest/reference/phql.html#joins

Then it should work. Then you get a complex Resultset containing Models. You can also combine selecting some columns from one Model, and * from the other. The latter values will then be accessible as a Model.

(But still it is a bit weird what's going on when just using * - that could still be a bug of course..)



981
edited May '14

It almost works but there is still one issue. I've added ->columns("[\EQAS\Models\Inventory].*, co.*") as you recommended and the returned Phalcon\Mvc\Model\Row objects contain data from both tables (awesome!) but the keys are incorrect. The first one is named \EQAS\Models\Inventory and the second one is co (which makes sense because that's how the tables were named in the query). My questions right now is how would I go about chaning the key name of the first table (->from("\EQAS\Models\Inventory")) since the default is a namespace which causes problems. I've also double checked selecting all data with * and I have the same issue as you. Cheers.



8.1k
edited May '14

Was already thinking about mentioning that, but I forgot it seems.. :)

Yes, it's a bit 'lame' I guess, but only by using addFrom you can set an alias (AFAICT).. You can use that instead of using from.



981

That's why I couldn't find a way to do it with from ... For those looking for a complete solution, here's my query:

$items = $this->modelsManager->createBuilder()
            ->columns('inventory.*, company.*')
            ->addFrom('\EQAS\Models\Inventory', 'inventory')
            ->join('\EQAS\Models\Companies', 'inventory.manufacturer = company.id', 'company', 'LEFT')
            ->getQuery()
            ->execute();

And usage:

foreach($items as $item) {
            echo $item->inventory->type;
            echo $item->company->name;
}

Thanks for all the help renskiii!



8.1k

That's alright!