pythondjangopostgresqltrigram

Django Postgres Trigram search - how can I see the score of each result?


I'm able to sort a trigram search by similarity and filter by the similarity score, meaning there must be some objective rank associated with every record. How can I access that score?

The code I'm using:

q = "search query"  # ie "honey"

A = 1.0
B = 0.4
C = 0.2
D = 0.1

total_weight = A + B + C + D

trigram = A/total_weight * TrigramSimilarity('name', q) + \
    B/total_weight * TrigramSimilarity('brand__name', q) + \
    B/total_weight * TrigramSimilarity('description', q) + \
    D/total_weight * TrigramSimilarity('category__name', q)

results = Product.objects.annotate(
        similarity=trigram
    ).filter(similarity__gt=0.1).order_by('-similarity')

If I'm able to filter by similarity__gt=0.1 or order by -similarity, is it possible to also see (and hopefully append) the similarity ranking of each record?


Solution

  • I figured it out. Much easier than expected. The raw SQL led me in the right direction. The above code creates the following statement:

    SELECT "listings_product"."id",
           "listings_product"."name",
           "listings_product"."slug",
           "listings_product"."brand_id",
           "listings_product"."category_id",
           "listings_product"."description",
           "listings_product"."featured",
           "listings_product"."image",
           "listings_product"."created_date",
           "listings_product"."created_by_id",
           "listings_product"."last_updated",
           ((((0.5882352941176471 * SIMILARITY("listings_product"."name", "honey")) + (0.23529411764705885 * SIMILARITY("listings_brand"."name", "honey"))) + (0.23529411764705885 * SIMILARITY("listings_product"."description", "honey"))) + (0.05882352941176471 * SIMILARITY("listings_category"."name", "honey"))) 
               AS "similarity" 
    FROM "listings_product" 
        INNER JOIN "listings_brand" ON ("listings_product"."brand_id" = "listings_brand"."id") 
        LEFT OUTER JOIN "listings_category" ON ("listings_product"."category_id" = "listings_category"."id") 
    WHERE ((((0.5882352941176471 * SIMILARITY("listings_product"."name", "honey")) + (0.23529411764705885 * SIMILARITY("listings_brand"."name", "honey"))) + (0.23529411764705885 * SIMILARITY("listings_product"."description", "honey"))) + (0.05882352941176471 * SIMILARITY("listings_category"."name", "honey"))) > 0.01 
    ORDER BY "similarity" DESC;
    

    AS "similarity" showed me that the score is likely already being returned. I went back to my django shell, ran the query, and tried:

    results[0].similarity
    

    Which returned 0.225113122340511.