Phalcon builder sql multi table column select not working

Question

So i am trying to get the final sql as something like this:

Select r.*, ptable.storeid from rtable as r innerjoin ptable ON ptable.id = r.pid;

But in phalcon phql there is no example showing this, and i know i can do this in raw sql but i want to know if there is a way to do this using phql. So far ive tried like this:

$row = $this->modelsManager->createBuilder()
    ->addFrom('rtable', 'r')
    ->columns('Ptable.storeid')
    ->innerJoin('Ptable','r.pid = Ptable.id')
    ->getQuery()
    ->execute();

But this still does not work and gives a sql build error. if someone can give me an example of what im trying to achieve, would be great.

Answer 1

Looking at your code ->columns('Ptable.storeid') this part will only return resultset of objects containing only storeid values.

Here is a simple join example and how to select specific columns from two tables.

$result = $this->modelsManager->createBuilder()
    ->columns([
        'profile.*',
        'details.*',
    ])
    ->from(['profile' => 'Models\Profiles'])
    ->leftJoin('Models\ProfileDetails', 'details.profile_id = profile.id', 'details')
    ->where('profile.id = :id:', ['id' => 1])
    ->getQuery()->getSingleResult();


print_r($result->profile->toArray()); // All columns from Profiles table
print_r($result->details->toArray()); // All columns from ProfileDetails table

In the example above I'm selecting only the columns which I need for presentation. This is the best practice if you aim for performance. However you can select whole objects like so: aliasForTable1.*, aliasForTable2.*..., this will return whole model object and you will be able to use model methods.

Note that:

->getSingleResult() will return only 1 result;

->execute() will return resultset with multiple objects;

More Query Builder examples in the docs: https://docs.phalconphp.com/en/3.2/api/Phalcon_Mvc_Model_Query_Builder