使用Phalcon中定义的外键关系自动连接？

Question

I'm using Phalcon 3.4 and PHP 7.2 ----

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("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) . "
";
        $this->assertEquals('Smith', $row['lastName']);
    }
    echo "All Good!
";

    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!

Answer 1

i recommend using PHQL

$this->modelsManager->executeQuery('SELECT * FROM models\\Person LEFT JOIN models\\PersonAddress where models\\Person.lastName = \'Smith\'');

but to have the Address Table too you will need to provide a condition here example: ( assuming that the address table has model models\Address

$this->modelsManager->executeQuery('SELECT * FROM models\\Person LEFT JOIN models\\PersonAddress LEFT JOIN models\\Address ON models\\PersonAddress.address_id = models\\Address.id where models\\Person.lastName = \'Smith\'');

my advice is to make a reference key in address table referencing table person