Basic Array Query Question

I think you made an mistake in the Agent Model:

<?php

use Phalcon\Mvc\Model;

class Agent extends Model
{
    public $id;
    public $supervisor_id;

    public function initialize()
    {
        $this->belongsTo('supervisor_id','Manager','client_id');
        $this->hasMany('client_id','Survey','client_id');
    }
}

Your Agent Model has a public property named client_id but it should be supervisor_id. The Relation is not correct so Phalcon does not find any relative entities.

$manager = Manager:findFirst(array('client_id' => 648, 'driver' => 1));

$agents = $manager->Agent;

$surveys = array();

foreach ($agents as $agent) {
    $surveys = $agent->Survey;

    foreach ($surveys as $survey) {
        $surveys[] = $survey;
    }
}

I don't know if its possible to get the Surveys depending on the Manager. Therefore the Manager needs to hold a relation between all Surveys. The only (correct me if i'm wrong) way is to get the Surveys from each Agent.

Thanks for the comment Mikel.

Based on the code I had above, I don't have any problem getting the surveys in that way.

Ultimately though, what I need is ways to filter the surveys down even further based on criteria that would eventually be given by form by the user.

So the code I had above allowed me to find a Manager, iterate through the related agents, and iterate through those agents' surveys.

I've made this project in PHP, but am looking to take advantage of what Phalcon has to offer to make it more efficient.

Previously what I had been doing, was querying the database for all agents that belong to a manager, iterate through and concatenate them into a string for an sql statement, which I'd have to believe is not a good thingfor both security and efficiency).

I'm looking for a way to query for the surveys for all the agents that belong to a manager, and also add a few more parameters based on the survey table.

For example a survey record might look like this:

id: 1
client_id: 234
driver: Driver 1
score: 9

Previously what I would do, is query the agent table for all agents matching a specific manager. I would then take each of those records and create a string that looked like this:

(client_id = 234 OR client_id = 235) AND

And then I would throw that into an sql statement that looked like:

SELECT * FROM survey WHERE (client_id = 234 OR client_id = 235) AND score = '9'

Where I ran into problems with this, is I wasn't able to use bindParam or anything in PDO for the client_id because it was dynamic every time I'd query so I inserted it in with a variable:

'SELECT * FROM survey WHERE '.$sql.' score = :score'

So the question that stands now, is how can I use PHQL or the queryBuilder to create this type of a statement efficiently. I tried using the queryBuilder with a leftJoin with Survey and Agent, but that ends up for whatever reason returning the survey results back multiple times.ill return 3 survey recods back, but each individual survey was repeated 3 times in the data set so I end up with 7 results instead of 3.

I'm currently messing wtih this bit of code for the queryBuilder:

        $surveys = $this->modelsManager->createBuilder()
            ->columns('Survey.*,Agent.*')
            ->from('Survey')
            ->where('driver = "Driver 1"')
            ->join('Survey', 'Survey.client_id = Agent.client_id', 'Agent')
            ->getQuery()
            ->execute();

    foreach($surveys as $survey) {
        echo $survey->score.'<br>';
    }

My current survey test records are:

id: 1
client_id: 234
driver: Driver 1
score: 9

id: 2
client_id: 234
driver: Driver 2
score: 1

id: 3
client_id: 234
driver: Driver 1
score: 5

id: 4
client_id: 235
driver: Driver 2
score: 8

id: 5
client_id: 815
driver: Driver 3
score: 3

id: 6
client_id: 814
driver: Driver 1
score: 4

Now for whatever reason when I run the code above I get:

9
9
9
5
5
5
4

When I'm expecting:

9
5
4

My goal is just to use PHQL and Phalcon as intended to make use of it's efficiencies. Any thoughts on how I can make this work? I feel that it's just due to my lack of knowledge of PHQL and queryBuilder to get what I want. I'd rather not just go back to writing out the full query with the concatenated string thrown in there.

Well, if i understand you correctly what you want is a group translation in PHQL. Try GroupBy

$surveys = $this->modelsManager->createBuilder()
  ->columns('Survey.*,Agent.*')
  ->from('Survey')
  ->where('driver = "Driver 1"')
  ->join('Survey', 'Survey.client_id = Agent.client_id', 'Agent')
  ->groupBy(array('score'))
  ->getQuery()
  ->execute();