Symfony2, Doctrine2, MySql and auto_increment column troubles

0
votes

Let's imagine that we have three tables with relations

  • User
  • Post
  • Comment

So relations is simple User Post Comemnt. This is common thing so I won't desrcibe it.

Every time when user add new post, we must first of all select (uneccesary query btw!) user (its entity) after that create new post and insert there user entity (we've already selected). The same thing with comments. Select post -> create new comment -> set to comment our post -> persist. (yeas we must define proper relation but it's a question)

So at the end we have : In each table we MUST have column 'id' with auto_increment property. And second column (in post and comment tables) with 'id' of parent item (in our case it's userID and postID).

So let's imagine we must do\edit\update\delete comments every 30 min (this is just example). And we have two choices.

First one is to select all comments (current in db). Delete existing in db. Do our updates\delete\create in php side of entities we selected and after that insert them again. So base meaning is "delete all then insert" (with new and updated).

And second one is to select all and do action on each, in php side if would be sometyhing like this: we will use foreach through all comments and switch between actions update, after that delete old, insert new.

In first case we have our trouble. Let's imagine we will re-create on each call update to 1000 entries. I know that type 'int' and 'bigint' is big value BUT it's not a deal, really. And what should I do it we have more relation on comment? For example with tags table of something like this...

In second case auto_increment won't be a trouble because we are not re-creating entries every time we call script. BUT we do A LOT OF unnecessary queries on EACH entry. (for example on native mysql delete an array of entries is ONE query, how much will it be in Doctrine? I will answer, minimum two, why? because first we must select + then delete it through manager = two db queries)

So question is how to avoid relations of tables without this column 'id' and auto_increment on it? Any thoughts will be appreciated.

WBR

1
mysqlsymfonydoctrine-orm

1 Answers

1
votes

You definitely need AI ID column, not just in doctrine, but generally.There are some cases where you don't, for example when you have composite primary keys, but in your case all of your entities: User, Post, Comment should have AI ID.

Second thing, You don't need to do SELECT + UPDATE/INSERT. Next example will cast only ONE query. For example, if you want to set Comment's user, you don't have to select user:

$comment = new MyCommentEntity();
$comment->setUser($entityManager->getReference('My\Namespaced\UserEntity', ['id' => $userId);
$entityManager->persist($comment);
$entityManager->flush();