Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Use of unqualified "id" column in _create_model_filter #308

Open
ercpe opened this issue Aug 14, 2024 · 1 comment
Open

Use of unqualified "id" column in _create_model_filter #308

ercpe opened this issue Aug 14, 2024 · 1 comment

Comments

@ercpe
Copy link

ercpe commented Aug 14, 2024

https://github.com/etianen/django-watson/blob/master/watson/search.py#L568 creates a unqualified Cast for the primary key column, which fails if the QuerySet has JOINs (e.g. because it's already filtered):

SELECT ("id"::text) AS "watson_pk_str"
FROM "model_a_table" U0 
INNER JOIN "model_b_table" U1 ON (U0."foo_id" = U1."id") 
WHERE U1."bar" = 20

The Cast should use U0.id as the column selector.

ercpe pushed a commit to ercpe/django-watson that referenced this issue Aug 14, 2024
Using `RawSQL` with `model._meta.pk.db_column` will add a verbatim SQL expression into the query. In this case, a reference to the primary key column, although without the proper table alias. This leads to a SQL error for QuerySets which already have a JOIN due to an ambiguous column (e.g. `id`).
Use Django's `Cast` function with the `id` property to cast the primary key of the model into a string, which will automatically de-alias `pk` to the name of primary key field and use the correct table alias in the resulting query.
@ercpe
Copy link
Author

ercpe commented Aug 14, 2024

I've created a patch in #309.

The Cast call will use a TextField as the output, although CharField (to cast into a varchar) ought to be enough.
I've left do_string_cast in place. It's unused now, but someone may rely on it being present in the backend implementation.

It would be great to have this fix merged and released since it's blocking our search. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant