Limitations with indirect-relations #3

Closed
miracle2k opened this Issue Nov 6, 2009 · 4 comments

Comments

Projects
None yet
3 participants

Use case: I have Publication, Issue and Article models (A Publication has multiple issues has multiple articles).

I'd like to cache "article_count" for each publication. In theory, this should be possible with a CountField, except of course that there is no direct relation between "Publication" and "Article".

I'm not terribly familiar with triggers, but it seems like it should be possible to write triggers for the "Article" model that update the correct "Publication" by going through the "Issue" connection:

UPDATE publication SET article_count=article_count-1 
WHERE id=(SELECT issue.publication_id FROM issue WHERE issue.id = OLD.issue_id);

UPDATE publication, issue SET article_count = article_count-1 
WHERE issue.id = OLD.issue_id AND publication.id = issue.publication_id;

While an extended CountField would be nice, unfortunately, it seems like I can currently implement this using a custom @denormalized field either. There too, is no way to specify the indirect relationship; I guess the corresponding triggers would need to employ some IF()s to determine whether to insert a dirty row.

If I have overlooked anything, I'd be happy to hear about it. Should I just attempt to write a custom denorm object?

Owner

initcrash commented Nov 12, 2009

you could use a differnt database structure:
4 models: Publication, Issue, Article and PublicationIssueArticle
where PublicationIssueArticle has FKs to the three other models.
this should make the denormalization issue easier and also speed up queries like finding all Articles in a Publication.

Owner

initcrash commented Feb 19, 2011

nothing to fix here

clemente commented Apr 8, 2012

It would be very intuitive to use the double underscore syntax:
article_count=CountField('issues__article_set')
It feels like Django and seems an easier solution than re-engineering models. somearticle.issues.article_set is still a manager, and that's what CountField needs.

Yeah, I haven't worked on the site in question for a long time, so this issue didn't come up anymore, but I obviously don't want to redesign my database structure, and not just because it would be a huge amount of work in the particular case.

This issue was closed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment