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_onisnull=False, companydeleted_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 https://github.com/etianen/django-watson/issues/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!