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

Get foreign key's field from find()

Hey,

I want to know if it's possible to get directly all field of a foreign key. e.g

If I use findFirst I can do that :

$robot = Robots::findFirst(array(
    "conditions" => "id IN :id:",
    "bind" => array("id" => 1)
));

$partsName = $robot->getRobotsParts()->name;

But if I use a find I have to do that :

$data = array()
$robots = Robots::findFirst()

foreach ($robots as $robot) {
    $data['id'] = $robot->id;
    $data['partsName'] = $robot->getRobotsParts()->name;
}

But there is something to do that :

$robots = Robots::find(array(
    "columns" => "id, parts_name" // But in my Robots model I have just the `id_parts`. parts_name is in the parts table
));

echo $robots->toArray();

It's possible to select directly the parts_name column ?

3


93.7k
Accepted
answer
edited Apr '18

Short answer - no. To achieve this you need a join, which is not possible with ORM. Either use your second example or even better - use the QueryBuilder which is much better in terms of performance. Your query should look something like this:

public function getRobotsWithPartNames()
{
    return $this->modelsManager->createBuilder()
        ->columns([
            'robot.id',

            'part.name',
        ])
        ->from(['robot' => 'Robots']) // <- make sure to specify full namespace here

        ->leftJoin('Models\RobotsParts', 'part.foreign_key = robot.id', 'part') // <- make sure to specify full namespace here and fix your key names

        ->where('...')
        ->andWhere('...')

        ->getQuery()->execute();
}


15.9k
edited Apr '18

I think you could use:

$criteria->join("Robots", "r.id = RobotsParts.robots_id");

https://docs.phalcon.io/en/3.3/api/Phalcon_Mvc_Model_Criteria

to manually set the criteria and pass it to model's find method.



41.3k

Thank you guys, so it's not possible with ORM ! Of course it's possible with the QueryBuilder :)



145.0k

QueryBuilder is still part of ORM, it still uses PHQL.



92

A new comment How to avoid.



92
in reply to Wojciech Ślawski

QueryBuilder is still part of ORM, it still uses PHQL. HAHAHHA