Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Basic Array Query Question

I'm new to Phalcon and have a relation set up between managers and agents.

Each manager has many agents, and each agent has only 1 manager.

I have a table/model of survey data in which each agent can have many surveys, but each survey only has 1 agent.

Manager Model:

<?php

use Phalcon\Mvc\Model;

class Manager extends Model
{
    public $id;
    public $clientId;

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

Agent Model:

<?php

use Phalcon\Mvc\Model;

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

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

Survey Model:

<?php

use Phalcon\Mvc\Model;

class Survey extends Model
{
    public $id;
    public $client_id;
    public $driver;
    public $score;

    public function initialize()
    {
        $this->belongsTo('client_id','Agent','client_id');
    }
}

I don't have any issues utilizing these relationships. I am able to use this code to output each survey that belongs to a certain manager:

$manager = Manager::findFirstByClientId(648);

    foreach ($manager->agent as $agent) {
        foreach ($agent->survey as $survey) {
            //$surveys[] = $survey->id;
            echo $survey->id.' / '.$survey->score.' / '.$survey->client_id.'<br>';
        }
    }

I want to get a bit more control over this data and have yet to identify how. My main question is that if I do:

$manager = Manager::findFirstByClientId(648);

Which will pull manager id 648. Each survey has additional data that defines the surveys (id, client_id, driver, score). How can I continue to use the agents that belong to this manager, to further define the result set?

For example I'd like to pull surveys that belong to the agents that belong to manager 648, but also contain "driver 1". I've made some basic queries such as:

    $query = $this->modelsManager->createQuery('SELECT * FROM survey');
    $surveys = $query->execute();

But I'm not sure how I can insert my "array" or list of agents that belong to that manager into this query.

If this is too vague please let me know and I'll be more than happy to expand on anything.



2.4k
edited Apr '15

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.



2.4k
edited Apr '15

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();


473
Accepted
answer

Thanks for your help Mikel, I think I found what I was looking for.

Directing me to that page just had me reread some of the documentation on the PHQL items and I found the inWhere() clause.

The code I ended up with looks something like this:

$surveys = $this->modelsManager->createBuilder()
        ->from('Survey')
        ->where('driver = "Driver 1"')
        ->inWhere('client_id', $agents)
        ->getQuery()
        ->execute();

This allows me to iterate my agents by the manager I chose in an array, and then feed that array in the inWhere() clause to utilize them all instead of concatenating a string, and still lets me snag any other conditions I have set.