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

count(*) every time on $model->save(), if no primary key in model's source table

Hi everybody,

There is a table "bookmark" with fields "advert_id" and "user_id" and model Bookmark

Every time user creates/updates a bookmark:

$bookmark = new Bookmark();
// ...
$bookmark->save();

in DB profiling log I see:

SELECT COUNT(*) "rowcount" FROM `testdb`.`bookmark` WHERE `user_id` = ? AND `advert_id` = ?
INSERT INTO `testdb`.`bookmark` (`user_id`, `advert_id`) VALUES (?, ?)

After adding new column 'id' as primary key there is no COUNT request.

Is this a bug or PDO necessity?

I don't wont to use primary key column, so currently I have to use raw sql for inserting/updating records into DB, which is much less convenient.



1.4k

Hey!

Is this possibly done to check for duplicate records? To not insert the same entry (e.g. user_id: 1, advert_id: 1) twice into the database? Since you added the primary key (I guess with auto increment) this check would be useless. In this case I would say it's aphalcon thing, but not a bug. But I unfortunatly can't say for sure.



145.0k

This is to check if row with some values already exists. If yes - then instead of doing insert into do update. Though not sure if this is necessary if those values are null for example.



1.8k
edited Jun '17

It seems so.

But it is strange for me, why COUNT(*) is used for that purpose (why not SELECT ... LIMIT 1).

Anyway, I've desided to continue using raw sql as it's better for perfomance.

Thanks for the answers.



145.0k

Because it's the fastest way.