Search for a subset by filtering on related model fields

[ranebo]

Hello,

Thanks for creating and maintaining this great package!

I've run into a situation where I would like to search a subset of models by filtering on a related model's field, like this:

watson.search("Search terms", models=(MyModel.objects.filter(related__is_public=True),))

When I try this, I get a django.db.utils.ProgrammingError: column reference "id" is ambiguous error.

Not sure if i'm missing a configuration, or if this isn't feasible at the moment. Would be great to know a way how to do this.

Thanks!

P.S. Here is the actual db error I'm working with, incase something else is going on:

ERROR:  column reference "id" is ambiguous at character 578
STATEMENT:  SELECT (ts_rank_cd(watson_searchentry.search_tsv, to_tsquery('pg_catalog.simple', '$$c$$:*'))) AS "watson_rank", "watson_searchentry"."id", "watson_searchentry"."engine_slug", "watson_searchentry"."content_type_id", "watson_searchentry"."object_id", "watson_searchentry"."object_id_int", "watson_searchentry"."title", "watson_searchentry"."description", "watson_searchentry"."content", "watson_searchentry"."url", "watson_searchentry"."meta_encoded" FROM "watson_searchentry" WHERE ("watson_searchentry"."engine_slug" = 'bread' AND "watson_searchentry"."object_id" IN (SELECT ("id"::text) AS "watson_pk_str" FROM "services_servicefirm" U0 INNER JOIN "professionals_company" U1 ON (U0."company_id" = U1."id") WHERE (U1."claimed_on" IS NOT NULL AND U1."deleted_on" IS NULL AND U1."is_public" = true AND U0."deleted_on" IS NULL AND U0."is_public" = true AND U0."membership_is_active" = true)) AND "watson_searchentry"."content_type_id" = 124 AND (search_tsv @@ to_tsquery('pg_catalog.simple', '$$c$$:*'))) ORDER BY "watson_rank" DESC  LIMIT 21

from

filters = dict(
    company__claimed_on__isnull=False,
    company__deleted_on=None,
    deleted_on=None,
    company__is_public=True,
    is_public=True,
    membership_is_active=True,
)

watson.search("Search terms", models=(ServiceFirm.objects.filter(**filters),))

[etianen]

If you're only searching a single model, try using `watson.filter` rather that `watson.search`. That will return a queryset of ServiceFirm, which can be easier to work with. What you're doing should work, of course, and is a bug. I don't have time to look at it in the immediate future, sadly, but would take an MR that fixes it. Hopefully the above workaround will work for you.

…

On Tue, 30 Nov 2021 at 21:41, Rane Gridley ***@***.***> wrote: Hello, Thanks for creating and maintaining this great package! I've run into a situation where I would like to search a subset of models by filtering on a related model's field, like this: watson.search("Search terms", models=(MyModel.objects.filter(related__is_public=True),)) When I try this, I get a django.db.utils.ProgrammingError: column reference "id" is ambiguous error. Not sure if i'm missing a configuration, or if this isn't feasible at the moment. Would be great to know a way how to do this. Thanks! P.S. Here is the actual stack trace I'm working with, incase something else is going on: ERROR: column reference "id" is ambiguous at character 578 STATEMENT: SELECT (ts_rank_cd(watson_searchentry.search_tsv, to_tsquery('pg_catalog.simple', '$$c$$:*'))) AS "watson_rank", "watson_searchentry"."id", "watson_searchentry"."engine_slug", "watson_searchentry"."content_type_id", "watson_searchentry"."object_id", "watson_searchentry"."object_id_int", "watson_searchentry"."title", "watson_searchentry"."description", "watson_searchentry"."content", "watson_searchentry"."url", "watson_searchentry"."meta_encoded" FROM "watson_searchentry" WHERE ("watson_searchentry"."engine_slug" = 'bread' AND "watson_searchentry"."object_id" IN (SELECT ("id"::text) AS "watson_pk_str" FROM "services_servicefirm" U0 INNER JOIN "professionals_company" U1 ON (U0."company_id" = U1."id") WHERE (U1."claimed_on" IS NOT NULL AND U1."deleted_on" IS NULL AND U1."is_public" = true AND U0."deleted_on" IS NULL AND U0."is_public" = true AND U0."membership_is_active" = true)) AND "watson_searchentry"."content_type_id" = 124 AND (search_tsv @@ to_tsquery('pg_catalog.simple', '$$c$$:*'))) ORDER BY "watson_rank" DESC LIMIT 21 from fitlers = dict( company__claimed_on__isnull=False, company__deleted_on=None, deleted_on=None, company__is_public=True, is_public=True, membership_is_active=True, ) watson.search("Search terms", models=(ServiceFirm.objects.filter(**fitlers),)) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#291>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABEKCGCXMYTAYITS74PY2DUOVAHNANCNFSM5JC4A6KA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[ranebo]

Thanks for the response!

'watson.filter' worked just fine, so has what I need for now. Later down the line I might need it for multiple models, so I will consider taking the time for the MR then.

Hopefully it gets fixed before then.
Thanks again!