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

Composite key condition

Is it possible to use model condition with composite key like that?

$checkExist = Daily::findFirst(
                [
                    'columns'    => 'ts',
                    'conditions' => '(ts, tds_id, tracking_id) = (:ts:, :tdsId:, :trackingId:)',
                    'bind'       => [
                        'ts'         => $this->timeFrom,
                        'tdsId'      => $pair->tds_id,
                        'trackingId' => $pair->tracking_id,
                    ],
                ]
            );

All I got is error:

Syntax error, unexpected token COMMA, near to ' tds_id, tracking_id) = (:ts:, :tdsId:, :trackingId:) LIMIT :APL0:', when parsing: SELECT ts FROM [Models\Daily] WHERE (ts, tds_id, tracking_id) = (:ts:, :tdsId:, :trackingId:) LIMIT :APL0: (106)

Or may be there are another way to get this row?

Sure, try this

$checkExist = Daily::findFirst(
                [
                    'columns'    => 'ts',
                    'conditions' => 'ts = :ts: AND tds_id = :tdsId: AND tracking_id = :trackingId:',
                    'bind'       => [
                        'ts'         => $this->timeFrom,
                        'tdsId'      => $pair->tds_id,
                        'trackingId' => $pair->tracking_id,
                    ],
                ]
            );
edited Mar '16

Thank you David, but may be I wasn't clear enought. I understand I can use AND condition, but it may cause perfomance issues I thought... The main question "Is it possible to use model condition with composite key" or we can do it only via raw queries.

Btw EXPLAIN shows that there is no difference between your and my queries :) So I think this trivial solution will be ok.

mysql> EXPLAIN SELECT `ts` FROM `daily` WHERE (ts, tds_id, tracking_id) = ('2015-06-01 08:00:00', 17, 5133);
+----+-------------+-------+-------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | daily | const | PRIMARY       | PRIMARY | 12      | const,const,const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------------------+------+-------------+
1 row in set (0,00 sec)

mysql> EXPLAIN SELECT `ts` FROM `daily` WHERE ts = '2015-06-01 08:00:00' AND tds_id = 17 AND tracking_id = 5133;
+----+-------------+-------+-------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | daily | const | PRIMARY       | PRIMARY | 12      | const,const,const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------------------+------+-------------+
1 row in set (0,00 sec)
edited Mar '16

Ah, sorry. I think, that Phalcon does't support composite key syntax.

If you need that, you will have to override standard database abstraction layer, documentation can be found here https://docs.phalcon.io/en/latest/reference/db.html