How to filter a model in case of too complicated database structure?-CodePudding

I want to make a flexible online shop which will allow it's admins to create products and add custom product fields without need to program. I did it, but the final database structure is so complicated that I can't figure out how to filter it.

Let's say there are categories with some products attached to it. Each category has only one unique template, the template holds custom fields names and types(int, char). When a product is created, the corresponding template-like fields are written to another model that holds custom fields names and values.

So, how to filter the product model considering its custom fields values? To clarify, let's say someone created smartphones category, created template with fields "Brand" and "Screen size", added some smartphones and wants to filter phones with brand="Apple" and screen size > 4.5 inches.

I hope that makes sense ^_^

Database structure:

class Category(models.Model):
    name = models.CharField(max_length=63)

class Product(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    name = models.CharField(max_length=63)
    price = models.IntegerField(validators=[MinValueValidator(0), MaxValueValidator(1073741823)], null=True, blank=True)

#Template
class CategoryTemplate(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE, null=True)
    name = models.CharField(max_length=255, null=True, blank=True)

#Model that holds template custom fields
class TemplateField(models.Model):
    template = models.ForeignKey(CategoryTemplate, on_delete=models.CASCADE)
    name = models.CharField(max_length=255, null=True, blank=True)
    is_integer = models.BooleanField(blank=True, default=False)

#Values of custom char product fields
class ProductPropertiesChar(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    property_name = models.CharField(max_length=255, null=True, blank=True)
    property_value = models.CharField(max_length=255, null=True, blank=True)

#Values of custom integer product fields
class ProductPropertiesInteger(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    property_name = models.CharField(max_length=255, null=True, blank=True)
    property_value = models.IntegerField(validators=[MinValueValidator(0), MaxValueValidator(1073741823)], null=True, blank=True)

CodePudding user response：

Maybe this will work. Firstly, I'd strongly recommed using explicit related names!

class ProductPropertiesChar(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE,
              related_name='charprop')
    ...

Simple case: all Products related to a single specified ProductPropertiesChar (the default related name is too horrible to type)

results = Product.objects.filter( charprop__property_name='Brand',
                    charprop__property_value='Apple' )

You can combine several values with __in or use the other usual __ lookups. You should also be able to .exclude(...).

results = Product.objects.filter( charprop__property_name='Brand',
                    charprop__property_value__in = ['Apple','Samsung','Google'] )

You ought to be able to use Q objects

q1 = Q( charprop__property_name='Brand',charprop__property_value='Apple' )
q2 = Q( intprop__property_name='ScreenSize', intprop__property_value__gte=130 )

I'm pretty sure or will work

results = Product.objects.filter( q1 | q2 )

I'm not quite so sure about and because you are following the related name to two different objects

results = Product.objects.filter( q1 & q2 )  # not sure

You may instead need to use .intersection (doc here)

qs1 = Product.objects.filter( q1)
qs2 = Productr.objects.filter( q2)

results = qs1.intersection( qs2)