I’m usinbg this plugin to let a user run its own queries in the project-database. Doing this, I encounter the same problem as I was discussing in this thread. I want to have only the last entry of table emplyoments displayed in results.
Tried it with adapting code of former thread like so:
...
qs = Employees.objects.annotate(
current=Subquery(
Employments.objects.filter(
employee=OuterRef('id')
).order_by('-duty_begin').values('id')[:1]
)
)
f = qs.filter(filters, employments=F('current'))
if request.method == 'GET':
if 'custom_query' in request.GET:
context['filter'] = DatabaseFilter(request.GET, queryset=f)
else:
.....
return render(request, 'query.html', context)
But if i want to filter an item of the table employments, i also get older than the actual employments within the results…
What you’re trying to do is write a query to select Employments, where the only Employments being returned are the most current Employments for each employee.
Your understanding is correct. As my actual code is working without errors despite I’m not getting what I want, I also have an assumption where the problem is:
I am defining a queryset with subquery and limit to one result based on sorting -duty_begin
The queryset is only defined at this moment, it is executed within the context['filter'] = DatabaseFilter(request.GET, queryset=f) code-line
If django-filter does his job properly, it will filter the employments and therefore apply the restrictions of the queryset afterwards…
First, I’m sorry! I meant to reply to this when I first saw it, but it was set-up day for DjangoCon and I lost track of your post.
This is fundamentally a significantly different situation than we addressed before in that you’re not trying to filter the results on Table A with a subset of values from Table B. In this case, you’re looking to filter what you get back from Table A alone.
This can be solved by using a Subquery in a filter to only return the rows where the date in Employment is the latest date for an employee.
e.g.:
no need to apologize, i appreciate your work and you are doing it for free, so never mind.
I thank You for Your input but the problem is, that the queryset I#ve posted initially is meant for custom user-queries throughout ALL items of the database. Therefore the core table has to be “Employees” as it is the core table of the whole database…
What i would need is something like this pseudo-code:
I’m still not understanding what you’re trying to generate here. I’m trying to follow what you’ve written, but I’m not seeing where what you’re asking for is different than what was discussed at Latest database entry of a kind - #2 by KenWhitesell
You are looking for is a list of Employees, based on what criteria? (Maybe it would be better if you described how what you are looking for here is different from the previous discussion?)
Note: You cannot directly annotate a complete object.
If you want just one field from Employment, that value can be used as the annotation. If the value is the PK of that Employment, you can then use that PK in subsequent queries to retrieve the full object. Yes, it creates an N+1 query situation, which could be acceptable under certain circumstances.
Or, you could use the JSONAgg function to create a JSON representation of the portions of the Employment object that you need to prevent the N+1 situation but creating a more complex query.
Side note: One of the things you may need to realize is that not all possible queries that you may want to issue can be written in terms of Employees - at least not as a single query.
There are times when you do need to “turn a query inside out” or “sideways” to get the data you’re looking for.
If you’re using user input to select one of those types of queries, you may need to add an if condition in your view to check the value of the selected filter and execute different queries as a result of that condition.
where do i start ? Your assumptions in our last Thread to this topic WHERE correct. But in THIS thread I am talking about an other part of the project.
Your given solution for the last thread on this topic is working flawlessly. HERE I was only trying to adapt your given solution to a similar problem and it was futile.
The difference is, that I am talking here about a queryset, that is base for a Django-filter query. To clarify: I use the Django-filter plugin to provide the possibility of a user based and customized database query. In the Template, all possible entries are shown and the user can select, for which combination of items to search.
It works fine after all, but the only bug is the fact, that former employments are disturbing the results. so for this custom queries i need only the current employments to be searched/filtered.
That’s going to be the rub, and what I was trying to address in my side note above.
It may be that you just can’t create a single query for “combinations of items to search”.
Without knowing what all combinations you’re trying to address, I can’t be more specific than that - but you’re probably going to need to address this almost on a case-by-case basis.
Given how your models are designed, it may not be possible to do what you’re trying to do - in the manner you’re attempting to do it.