Tuesday, 17 September 2013

INSERT ... ON DUPLICATE KEY UPDATE in Doctrine

INSERT ... ON DUPLICATE KEY UPDATE in Doctrine

I am coming to StackOverflow, since I can't find anybody with the same
problem (not even here!)
I am using Doctrine and I have a @ManyToMany entity relation that I would
like to persist to the database. To get you an idea of what I am doing, I
have two Entities: Article and Tags.
Of course, an article can have many tags, and a tag can point to many
articles.

I could do this in many ways (in fact, I had it without entity relations)
but I want to add extra information to those tags: times_clicked and
times_tagged.

Now, inserting them in an easy way (with an extra id column that acts as
PRIMARY KEY) is fairly easy, but I would like tag to be a PRIMARY KEY.
This is for the only (and reasonable) reason that I don't want a table
that contains 1000 entries with the tag column "vacations", so in each new
article I can do some sort of ++times_tagged if the tag did exist.
I have tried with a RAW SQL INSERT (using
$em->getConnection()->prepare($sql)...) but it doesn't work because it
throws an error:
Can't instantiate custom generator :
and there it should say the class name, which is "" because raw statements
don't relate to classes (I guess... but I could not find a single piece of
information anywhere about this)
This happens, now I know, because I am not persisting the $article
variable from the Tag entity. This is again a supposition based on my
limited knowledge.

In any case, my best approach has been using a try and catch, thanks to
some info found here, and the idea would be to run an UPDATE if an error
is thrown, but it keeps throwing the error... (which makes me wonder how
little I know about this)
try {
// ...
$tag = new Tag('vacation');
$article->addTag($tag);
$em->persist($tag);
// ...
$em->persist($article);
$em->flush();
}catch (\PDOException $e) {
if ($e->getCode() === '23000') {
//echo $e->getMessage();
// this is fine! It means "DUPLICATE ENTRY"
// Perform an update for the tag 'vacation'.
// SET times_tagged = times_tagged+1
}
else throw "Well, NOW we have a problem!";
}
I hope I didn't bore you with too much introduction! So, the question is
fairly simple:
HOW can I achieve this? (i.e. How can I persist both Article and Tag, but
using the tag name as a PRIMARY KEY?


Thank you deeply in advance!

No comments:

Post a Comment