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

table with normal primary ID column and mySQL 2-column unique key

First question, is there a way to have the Phalcon Model recognize a 2-column unique mySQL key so that I can use the ->save() option to update/create data. I looked at the Phalcon\Mvc\Model\MetaData docs, but didn't see an option for specifying uniques without messing with the actual primary key.

This is what the table looks like:

CREATE TABLE IF NOT EXISTS activities (
  activity_id int(9) NOT NULL AUTO_INCREMENT,
  roster_id int(9) NOT NULL,
  col_id int(3) NOT NULL,
  title varchar(100) NOT NULL,
  weight int(3) NOT NULL,
  PRIMARY KEY (activity_id),
  UNIQUE KEY rostersheet (roster_id,col_id)
);

Assuming that the answer to that is no, I tried to code the logic to search if the record exists, and then update or create as needed. But I ran into a problem with the findFirst() that doesn't make sense to me, which means I don't understand the way the data is returned, or maybe it's a cacheing problem?

Without getting too far into the weeds of my code:

for ($col = 1; $col <$maxColumn; $col++)
{
    unset($activity);

    $activity = Activities::findFirst(array(
        "roster_id = '".$roster->roster_id."'",
        "col_id = '".$col."'"
    ));

    if ($activity)
    {
        // code to update the activity  
    } else {
        // code to create a new activity
    }   
}

The problem is, the first time through the loop works (a new activity is created), but then for subsequent loops, it updates the first record, instead of creating new ones. I have echo'd values to insure that the combo value of roster_id and col_id are indeed unique, and I empty the table prior to running the code.

I added a unset($activity) before the findFirst() line, since it is obviously remembering the activity object from the previous loops' search. But that didn't clear the object, which implies to me that the FindFirst() results are being cached, even though from the Phalcon docs, it sounds like you have to extend a CacheabeModel to keep previous results.

So the 2nd question is, how do I clear the findFirst() variable during loops? Or is there a better way to do this?



939

I had the findFirst() formatted incorrectly, so it was only using the first criteria, not both.

$activity = Activities::findFirst(array(
  "col_id = ".$col." and roster_id = '".$roster->roster_id."'"
));

I'd still be interested in seeing if there is a better way to go about doing that.



98.9k
Accepted
answer
edited Oct '14

This version is more PHQL-friendly, also more secure:

for ($col = 1; $col < $maxColumn; $col++) {

    $activity = Activities::findFirst(array(
        "roster_id = ?0 AND col_id = ?1",
        'bind' => array($roster->roster_id, $col)
    ));
    if (!$activity) {
        $activity = new Activities();
        $activity->roster_id = $roster->roster_id;
        $activity->col_id = $col;
    }

    //update other fields ...

    if (!$activity->save()) {
        var_dump($activity->getMessages());
    }
}