-1

Lets say I have table that looks like this

col1 col2
1    completed
2    error
3    inititated
4    error
5    completed
6    error
7    completed

Now I have query in django that do like this:

Model.objects.filter(col1__in=[1,2,8]).values('col2')

This query is running fine but what i want is to do something like this: Return "pending" for col2 where col1 is not in above list i.e return "pending" for 8 as it is not in table and "completed" and "error" for 1 and 2

Deepak Tripathi
  • 3,175
  • 1
  • 8
  • 21
  • I don't think that this is possible with a single query. Probably way to go is to write your own models.Manager. Maybe you can take a look here to get some ideas: https://stackoverflow.com/a/20674112/6705092 – Branko Radojevic Sep 18 '20 at 07:50
  • Thanx but i still doubt is it possible in postgresql with where Not Exists – Deepak Tripathi Sep 18 '20 at 08:06
  • I would personally add pending status in the table tbh, it is how I usually do this type of things. – Branko Radojevic Sep 18 '20 at 08:59
  • Hi @BrankoRadojevic I appreciate your personal opinion but think of a case when pending status is more often then other, then we are just increasing the database size without any reason ? – Deepak Tripathi Sep 18 '20 at 16:50
  • Well, if the pending status is always last one, it is not that common after all, isn't it? And with that you get very clean database querying. Of course, feel free to try other approaches, but this one might be the easiest. – Branko Radojevic Sep 20 '20 at 15:25

1 Answers1

1

I think some processing would need to be done in Python, maybe like this:

col1_list = [1, 2, 8]

# build a dict, so you can easily look up which 'col1' are present in the table
result_dict = {
    c1: c2
    for c1, c2 in Model.objects.filter(col1__in=col1_list)
        .values_list('col1', 'col2')
        .order_by('col1')}
print(result_dict)
# {1: 'completed', 2: 'error'}

# now build the full list and return 'pending' if 'col1' is not in table/dict
result_list = [
    (
        c1,
        result_dict.get(c1, 'pending')
    )
    for c1 in col1_list]
print(result_list)
# [(1, 'completed'), (2, 'error'), (8, 'pending')]

This is quite fast, because it is only one query; the only drawback is the Python overhead, but since it works on only two columns which are string and intergers (not objects) it shouldn't be too time/memory consuming.

Ralf
  • 16,086
  • 4
  • 44
  • 68
  • Hi @Ralf This approach is actually correct but I wanted a db approach as db level processing is fast . Correct me if i m wrong . Currently I m creating a pandas dataframe with default value status pending and then creating the dataframe from queryset, then doing dataframe.update(queryset_dataframe) . Thanx for your try I m upvoting it but waiting for any db approach. – Deepak Tripathi Sep 18 '20 at 16:49
  • @DeepakTripathi Well if you have another DB table with the IDs you are interested in, then you could do a join with the table you showed in the question. – Ralf Sep 18 '20 at 16:53
  • I will try this approach – Deepak Tripathi Sep 18 '20 at 18:39