I have an app where I am tracking user searches. The table currently looks like this:

What I want to change is the view. I'd dlike to group/aggregate/annotate the view based on Search Term column and keep the latest date of each search term. The column User searches is being updated when user puts the same term in the search bar so there is no need for me to keep all records. I just need the latest one.

I tried to update queryset using distinct() and annotate() in admin model but with no success.

In other words I want to have 1 unique term in the table with the latest date (Searched on column).

Below is my code:

models.py

class SearchQueryTracker(models.Model):
    id = models.AutoField(primary_key=True)
    author = models.ForeignKey(User, blank=True, null=True, on_delete=models.SET_NULL, unique=False)
    total_results = models.IntegerField('Search results', default=0)
    total_searches = models.IntegerField('User searches', default=0)
    search_term = models.CharField(max_length=1000, blank=True, null=True)
    searched_on = models.DateTimeField('searched on', default=timezone.now)

views.py

@login_required
def SearchViewFBV(request):
    query = request.GET.get('q')
    query_list = request.GET.get('q', None).split()
    query_list_count = len(query_list)
    user = User.objects.get(email=request.user.email)
    find_duplicate_results = SearchQueryTracker.objects.filter(search_term=query).update(total_searches=F('total_searches')   1)
    ....
    search_tracker = SearchQueryTracker()
    search_tracker.total_searches = find_duplicate_results
    search_tracker.author = user
    search_tracker.search_term = query
    search_tracker.save()

admin.py

class SearchQueryTrackerAdmin(ImportExportModelAdmin):
    list_display  = ('search_term', 'author', 'total_results', 'total_searches', 'searched_on')
    readonly_fields = list_display
    list_display_links = ('search_term', )
    search_fields = ('search_term', )
    
    def queryset(self, request):
        qs = super(SearchQueryTrackerAdmin, self).queryset(request)
        qs = qs.order_by('-searched_on',).distinct('search_term') # not working
        # qs = qs.annotate(Count('searched_on')) # not working
        return qs
    
admin.site.register(SearchQueryTracker, SearchQueryTrackerAdmin)

   

Any help or advice will be appreciated.

CodePudding user response：

Instead of creating a new SearchQueryTracker each time on SearchViewFBV, you can use the get_or_create method to retrieve the SearchQueryTracker if it exists, or create it otherwise.

@login_required
def SearchViewFBV(request):
    query = request.GET.get('q')
    query_list = request.GET.get('q', None).split()
    query_list_count = len(query_list)
    user = User.objects.get(email=request.user.email)
    search_tracker, created = SearchQueryTracker.objects.get_or_create(
        search_term=query, author=user,
        defaults={'total_searches': 1}  # If it is created, assign the total_searches so you don't have to save afterwards
    )

    if not created:
        search_tracker.total_searches  = 1
        search_tracker.save()

To avoid MultipleObjectsReturned exception you will need to clean your database of all duplicates first.