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

Custom SQL and assigning results to model objects

Big picture

Complex SQL query is required in order to retrieve some custome data set. Resulting data contain 2 diffrent models properties - Document and Job. Now there is problem of converting (and splitting) this primitive arrays data into model objects.

My solution

Execute SQL query (at self::QUEUE_QUERY)

$pdoResults = $db->query(self::QUEUE_QUERY);
$pdoResults->setFetchMode(PDO::FETCH_ASSOC);
$data = $pdoResults->fetchAll();

Then populate model object with the array

$this->document = new Document();
$this->document->assign($data);
$this->job = new Job();
$this->job->assign($data);

Each model pick items coresponding with properties. Now it might looks ok, but it's not.

Issue

Model is not aware of the fact that is existing record. When Im trying to performe save() then he trying to insert new record (what causing primary key violation).

How I can bind model with data populated on my own with certain already existing records in database?

I would like to model performe an update when Im call save on it.



33.8k
edited Jan '15

You mean this? I don't understand very well.

// Get the record with populated data (existing record?) of the DB.
$this->document = Document::findFirst(..);
$this->document->assign($data);

Also, save() method is a mix type of create() / update(). If you want to only let to insert / update a record, use the corresponding method.

edited Jan '15

@RompePC Thanks for response !

Well I don't want to use find, findFirst or such methods. I need to execute complex SQL. This SQL bring me an array as result. Now, how I can make model objects from this array? (without doing extra db queries by any find and such methods).



33.8k
edited Jan '15

Are we taliing about SQL that uses aliased tables? Because you cannot (as far as I know). But if it is a complex SQL, result of using the existing tables of the DB, you would use aliased fields in the SELECT that have the same name that the Model's fields:

$models = [];

foreach($data as $subData)
{
    $model = new Model();

    $model->assign($subData);

    $models[] = $model;
}
edited Jan '15

The SQL is:

SELECT job.*, document.*, date_part('epoch', now()-document.last_check_ts) AS expired_time FROM job
JOIN document on job.id=document.job_id
WHERE job.active=true AND (document.last_check_ts is null OR date_part('epoch', now()-document.last_check_ts)>job.document_min_age)
ORDER BY priority ASC, expired_time DESC LIMIT 100

Actully, what Im doing right now is like in the code you provide above. Im using assign function as you shown in foreach. But after all, I will have to remember this models were 'instantiated in special way' - what is relly bad thing.

For instance: after that I will pass models instintiated in that way (by assign function) to another function and so on. In that another function I will have to remember that I can't call save() (to performe update) cause it was instantiated by assign.

thanks for your help @RompePC

EDIT: In other words, how I can performe custom SQL without losing possibility to use all abilities of Phalcon\Mvc\Model?



33.8k

Then just create an utility class for that (ModelConverter, for example). Then, you create methods that returns correctly converted model objects and pass the primitive models to then (convertDocument($primitiveDocument), for example). But this will return a model's instace, just as you are doing now.

Other solution might be creating a pseudo-model; a model that doesn't extends Phalcon\Mvc\Model, but has the same getter and setter methods for its fields (create the fields as properties of the class).

And about save() method, I forgotted to add that it will try to insert if you created the instance like $model = new Model(); else it will update the record.

If I say some non-sense don't worry, its because of the tiredness I've from all the day.



2.1k

primary key?

$this->useDynamicUpdate(true);

place it in the model->initialize()

thanks guys for your response. @7thcubic, I'll try this. thanks.