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

Automatic Joins Using Defined Foreign Key Relationships?

I'm trying to use the Query Builder's constructor and just specify the args variable to create my query.

My hope is to be able to dynamically create joined queries this way. Both of the classes have the many to many relationship well defined. I would love to be able to just specify that I need Person, and Address and it knows to use the PersonAddress table to find the relationships...

Person Table:

            id  lastName    firstName   displayAs
            1   Smith           Jeff                J Smith
            2   Johnson      Mark           Mark Johnson
            3   Rivers          Sally           Sally Rivers
            4   Smith          Anna         Anna Smith
            5   Rivers         Billy            Billy Rivers

Person Address Table

                person_id   address_id
                1                       1
                4                       1
                2                       3
                5                       3
                3                       4

and finally Address Table

                id  nickname    streetNumber    streetName  city    state   zipcode
                1   Home            123                         Main St      Windsor    CO  80550
                2   Work            2561                        S Shields    Fort Collins   CO  80526
                3   Home            512                         Rock Ave    Loveland    CO  12352
                4   Home            999                         Uh Oh St    Severance   CO  80550

Both the Person and PersonAddress Models have the relationships well defined. (Created using the scaffolding)

         public function initialize()
          {
              $this->setSchema("biz-mngmt-test");
              $this->setSource("person");
              $this->hasMany('id', 'models\PersonAddress', 'person_id', ['alias' => 'PersonAddress']);
              $this->hasMany('id', 'models\PersonFamily', 'person_id', ['alias' => 'PersonFamily']);
          }

However it seems to default to an outer join.

            $params = [
                "models" => ["models\\Person", "models\\PersonAddress"],
                "columns" => ['firstName', 'lastName', 'address_id', 'person_id'],
                "conditions" => "lastName = 'Smith'",

            ];

            $queryBuilder = new \Phalcon\Mvc\Model\Query\Builder($params, $this->getDI());

            /** @var Phalcon\Mvc\Model\Resultset\Simple $result */
            $result = $queryBuilder->getQuery()->execute();

            $this->assertEquals(10, $result->count() );

            //Double check the last name

            foreach($result->jsonSerialize() as $row){
                echo "" . implode(" ", $row) . "\n";
                $this->assertEquals('Smith', $row['lastName']);
            }
            echo "All Good!\n";

            echo print_r($result->jsonSerialize(),true);

            $this->assertTrue(true);

Outputs:

          Jeff Smith 1 1
          Anna Smith 1 1
          Jeff Smith 1 4
          Anna Smith 1 4
          Jeff Smith 3 2
          Anna Smith 3 2
          Jeff Smith 3 5
          Anna Smith 3 5
          Jeff Smith 4 3
          Anna Smith 4 3

Is there a way to specify that it does an implicit inner (or left) join on the foreign key relationships that it has defined?

It looks like there is a join method that does an implicit join, that I could use if I have too, but would really be a lot cleaner if everything could just be listed in the parameters!

I don't know for sure, but I doubt there's a way to modify the behaviour of QueryBuilder in this way. If you want to ensure a left join is used, I think you'll have to build the query yourself.

What exactly are you trying to test here? Or is the testing code just to demonstrate the problem?

If you're trying to find a way to get all addresses for a single Person, you can set up a hasManyToMany() relationship for that.

Actually query builder is using if it can figure out joins, of course you still need to make an like call leftJoin method, but you don't need to provide for example on condition, just class name is enough.