We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Model Relationship Find

Hi guys, I have a question regarding relationships, did not spot this in the docs.

If I have the following tables

 CREATE TABLE robots (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    name varchar(70) NOT NULL,
    type varchar(32) NOT NULL,
    year int(11) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE robots_parts (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    robots_id int(10) NOT NULL,
    parts_id int(10) NOT NULL,
    created_at DATE NOT NULL,
    PRIMARY KEY (id),
    KEY robots_id (robots_id),
    KEY parts_id (parts_id)
);

with models

class Robots extends \Phalcon\Mvc\Model
{

}

class RobotParts extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->belongsTo(
            'robots_id',
            'Robots',
            'id'
        );
    }
}

Is there a way to get RobotParts where Robot type = "droid" in one find call (with loading in the robot details in the RobotParts model if possible) ? eg.

RobotParts::find();
SELECT * 
FROM robots_parts 
JOIN robots ON robots_parts.robot_id = robots.id
WHERE robots.type = "driod"

Thanks



125.7k
Accepted
answer
edited Jun '20

Not with find() - which works on just 1 table. You can also use getRelated(), but that's restricted to just RobotParts for a particular Robot.

For this you'll need to use QueryBuilder or the ModelsManager to execute a raw PHQL statement (not SQL - that would requried the raw DB connection). If you're in a controller, this should work:

$RobotParts = $this->modelsManager->executeQuery("SELECT rp.* FROM RobotParts rp, Robots r WHERE rp.robot_id = r.id and r.type = :type:",["type"=>"droid"]);
edited Jun '20

$user = User::find(1);

$user->posts()->orderBy("id","desc")->get(); // it will order it by id and get new collection of all items for this user MyPrepaidCenter