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!