Django: Equivalent of "select [column name] from [tablename]"

Question

I wanted to know is there anything equivalent to:

select columnname from tablename

Like Django tutorial says:

Entry.objects.filter(condition)

fetches all the objects with the given condition. It is like:

select * from Entry where condition

But I want to make a list of only one column [which in my case is a foreign key]. Found that:

Entry.objects.values_list('column_name', flat=True).filter(condition)

does the same. But in my case the column is a foreign key, and this query loses the property of a foreign key. It's just storing the values. I am not able to make the look-up calls.

Answer 1

Of course, values and values_list will retrieve the raw values from the database. Django can't work its "magic" on a model which means you don't get to traverse relationships because you're stuck with the id the foreign key is pointing towards, rather than the ForeignKey field.

If you need to filters those values, you could do the following (assuming column_name is a ForeignKey pointing to MyModel):

ids = Entry.objects.values_list('column_name', flat=True).filter(...)
my_models = MyModel.objects.filter(pk__in=set(ids))

Here's a documentation for values_list()

Answer 2

You have a model A with a foreign key to another model B, and you want to select the Bs which are referred to by some A. Is that right? If so, the query you want is just:

B.objects.filter(a__isnull = False)

If you have conditions on the corresponding A, then the query can be:

B.objects.filter(a__field1 = value1, a__field2 = value2, ...)

See Django's backwards relation documentation for an explanation of why this works, and the ForeignKey.related_name option if you want to change the name of the backwards relation.

Answer 3

To restrict a query set to a specific column(s) you use .values(columname)

You should also probably add distinct to the end, so your query will end being:

Entry.objects.filter(myfilter).values(columname).distinct()

See: https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.values

for more information

Depending on your answer in the comment, I'll come back and edit.

Edit:

I'm not certain if the approach is right one though. You can get all of your objects in a python list by getting a normal queryset via filter and then doing:

myobjectlist = map(lambda x: x.mycolumnname, myqueryset)

The only problem with that approach is if your queryset is large your memory use is going to be equally large.

Anyway, I'm still not certain on some of the specifics of the problem.