Suppose I have a Django model with 10 fields, with 10 input values. How to get a QuerySet , when performing which from the table will be obtained the rows in which at least n columns match ( n <10)?

    1 answer 1

    Poor architecture, forcing crutches. Better to change it. The only solution I see with this model is to use a diaper from Conditional Expressions :

     SomeModel.objects.annotate( match_count=Case(When(bool_field1=value1, then=1), default=0, output_field=IntegerField()) + Case(When(bool_field2=value2, then=1), default=0, output_field=IntegerField()) + Case(When(bool_field3=value3, then=1), default=0, output_field=IntegerField()) + ... ).filter(match_count__gte=5)
    • Perhaps you then tell me how best to design in this case? I need to store the matrix n by m , in which the values ​​of type bool will lie. And if necessary, quickly filter out entities where this matrix coincides with the incoming matrix more than in k positions - IgorNikolaev
    • The most suitable storage option is ArrayField : matrix = ArrayField(ArrayField(models.BooleanField())) . But your search is not a task for ORM. I would rather put it on the database, I wrote a stored procedure that returns the identifiers of suitable records. - Sergey Gornostaev
    • they write here what to look for in an array is bad, but in a two-dimensional way, all the more so that if the design has come to this, then you need to normalize the data. I finally decided to store the matrix as a binary vector and, if necessary, sort through AND (weight a & b> = k) - IgorNikolaev
    • Or so. And ORM filtering will be easier. But in terms of data structure - this is overkill, in my opinion. - Sergey Gornostaev
    • In what plan through ORM will be easier? I am doing a raw() query like this SELECT * FROM MyModel WHERE (SELECT length(replace(x::TEXT, '0', '')) FROM ( VALUES (column_name::bit(56) & '00000000000000000000000000000000000000000000100000000001') ) AS something(x)) >= k; Is this something you can do without RawQuerySet ? - IgorNikolaev