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?
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
.