postgresql django: how to store an array of instances of variable type?

Question

Suppose that you're creating a blog and each blogpost consists of an array of interleaving text fragments and fragments of svg (for instance).

You store each of those fragments in a custom django field (e.g. HTMLField and SVGField).

What's the best way to organize this?

How to maintain the order of fragments? This solution looks ugly:

class Post(models.Model):
    title = CharField(1000)

class Fragment(models.Model):
    index = IntegerField()
    html = HTMLField()
    svg = SVGField()
    post = ForeignKey(Post)

Create a separate model to store those. You can't depend on fields because sounds like you don't know how many are there. — Shang Wang
– Shang Wang, Commented Nov 5, 2015 at 19:15
@ShangWang I'm trying to use one-to-many relation, but how to maintain the order of fragments? Just use integer field? — Boris Burkov
– Boris Burkov, Commented Nov 5, 2015 at 20:01
How many different types do you have? If you don't have many, your way is fine, use integer field to store the order. — Shang Wang
– Shang Wang, Commented Nov 5, 2015 at 20:22
I have 2 types. But while appending a post, I have to iterate over them all to find the one with largest index, if my post contains 20 fragments. I have to manually implement CRUD operations, which is wildly ineffective, compared to non-relational databases. — Boris Burkov
– Boris Burkov, Commented Nov 5, 2015 at 20:34
No, you simply do Fragment.objects.filter(post=current_post).latest('index') to find the largest index. If you want to go from first to last, do Fragment.objects.filter(post=current_post).order_by('index'). Just take advantage of ORM operations it's very easy. — Shang Wang
– Shang Wang, Commented Nov 5, 2015 at 20:37

Community · Accepted Answer · 2017-05-23 10:27:17Z

1

As discussed, a separate model is a feasible way to go to record all the fragments. We use one IntegerField to record the fragment order, so that later one the whole Post could be recovered.

Some caveats here:

Use order_by, latest or slice n dice operations to sort/find elements.
When insert/delete operations are needed, it's going to break the overall sequence. We need to increase/decrease multiple elements to maintain the order. Use queryset and F() expression to change multiple records at once, like described in another SO post here.

There are some imperfections about the approach, but It's the best solution I could come up so far(I encountered similar situation before). Linked list is a good way but it's not database-friendly, as to get all fragments we need O(n) operations instead of O(1) with queryset.

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Nov 5, 2015 at 21:15

Shang Wang

25.7k21 gold badges77 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql django: how to store an array of instances of variable type?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related