Fetching from database question

Using Django Using the ORM
1

Hi,

On my article pages I have a “Related article” section that displays 3 titles. I grab those articles from database with simple request

def article(request, category_slug, article_slug):
    post = Post.objects.get(slug=article_slug)
    theNumber = post.id
    related = Post.objects.filter(category=post.category).filter(pk__gt=theNumber)[:3]
    return render(request, 'article.html', {'post':post, 'related':related})

It works fine, but there is a problem. The article with the biggest ID doesn’t display related articles, the biggest minus one displays just one related article etc. So I guess I need to make my code more complicated. What would be the proper way to fetch extra, if ‘related’ fetches < 3 articles?
something like this )

def article(request, category_slug, article_slug):
    post = Post.objects.get(slug=article_slug)
    theNumber = post.id
    related = Post.objects.filter(category=post.category).filter(pk__gt=theNumber)[:3]
    theQuantity =  count(related)
    if  theQuantity < 3:
         fetchMore = 3 - theQuantity
          extraRelated = Post.objects.filter(category=post.category).filter(pk__lt=theNumber)[:fetchMore]
          related = related + extraRelated
    return render(request, 'article.html', {'post':post, 'related':related})

I know that this code is a mess. ) I’m doing my first steps in Python and Django.
Thanks in advance.

2

Please post your Post model. It’ll be a lot easier to understand and propose a solution if we can see the relationships between Posts.

3

Sure.

class Post(models.Model):
    title = models.CharField(max_length=255)
    slug = models.SlugField(default="", null=False)
    body = models.TextField()
    category = models.CharField(max_length=255, default='uncategorized')
    p = models.IntegerField(null=True, blank=True)
    city = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return self.title
4

I’m not seeing what determines a “related article”.

How do you know which other three Post to retrieve? Is it just the three posts (other than the current post) with the largest pk?

if so, then the query is something like this:

5

the same category

This code will display the same 3 articles as related for all the articles in a category. My idea is to arrange incoming links to each article for SEO purpose. So each article will have 3 inbound links.

6

I’m not seeing what you mean by this in terms of the query you’re trying to write.

So again, how do you determine which 3 articles to select for any given article?

7

Sorry for my English. )
3 articles with the same category and ID > the_current_article_id. This way all the articles will get the same amount of inbound links. Three in my case.

However the article with the smallest id in a category don’t get any, with the smallest id + 1 gets only 1 link, if I use my original code.

8

No need to apologize. I assure you that your English is far superior than my knowledge of whatever your native language may happen to be.

To rephrase to try and make sure I understand you.

You want the three articles having the lowest id that are greater than the current article, but adding in articles with an id less than the current article if there aren’t three articles in the first part.

To try and draw a picture for this, assume for this discussion that all articles listed here have the same category:

| Art 1 |  Art 2 |  Art 3 |  Art 4 |  Art 5 |  Art 6 |  Art 7 |  Art 8 |

If the current post is “Art 4”, then the three to be shown are “Art 5”, “Art 6”, and “Art 7”.

If the current post is “Art 7”, then the three to show are “Art 8”, “Art 5” and “Art 6”.

Is this correct?

9

Correct. Except I had in mind 8, 1, 2. As there is no need for extra calculation this way, just to grab the amount we need to make it 3. No need to sort as by default they are sorted by id increment.

10

P.S. It’s Ukrainian.

11

There are going to be two parts to this query regardless, and actually, the “Art 5”, “Art 6” response would be easier.

I think this will do what you need:

# This first part gets the Posts just above the identified post:
qs1 = Post.objects.filter(category=post.category, id__gt=post.id)[:3]
# This part gets the Posts starting from the beginning to fill the total of three:
qs2 = Post.objects.filter(category=post.category, id__lt=post.id)[: 3 - qs1.count()]
# Combine the two parts
related = qs1.union(qs2)

Now, if you wanted to retrieve “Art 8”, “Art 5” and “Art 6”, this could be written like this:

# This first part gets the Posts just above the identified post:
qs1 = Post.objects.filter(category=post.category, id__gt=post.id)[:3]
# This part gets the three Post just before the identified post:
qs2 = Post.objects.filter(category=post.category, id__lt=post.id).order_by('-id')[:3]
# Combine the two parts
related = qs1.union(qs2).order_by('-id')[:3]

This avoids the count function call.

Thank you! I complement you on your language skills.

12

Thanks! It looks like what I need. I’ll try the code and will confirm that it works.

Thanks. ) I used to live in Oregon. But my English is getting more and more rusty.

13

should it be…

related = Post.objects.filter(category=post.category, id__gt=post.id)[:3]
if related.count() < 3:
   qs = Post.objects.filter(category=post.category, id__lt=post.id)[: 3 - related.count()]
   related = related.union(qs)

…to avoid second query in most cases? There are dozens of articles, most of them have 3 related with id > current_article_id , and only 3 last articles will require ‘qs’ .

14

Nope, no need to do that. If the count function returns 3 such that the limit clause will be 0, then the ORM will not add that clause to the query.

15

hmm… it doesn’t work

def article(request, category_slug, article_slug):
    post = Post.objects.get(slug=article_slug)

    qs1 = Post.objects.filter(category=post.category, id__gt=post.id)[:3]
    qs2 = Post.objects.filter(category=post.category, id__lt=post.id)[: 3 - qs1.count()]
    related = qs1.union(qs2)

    return render(request, 'article.html', {'post':post, 'related':related})

error log says

2024-04-01 15:38:20,023: Internal Server Error: /oregon/steelhead-fishing-near-portland-oregon
Traceback (most recent call last):
File “/usr/local/lib/python3.10/site-packages/django/core/handlers/exception.py”, line 55, in inner
response = get_response(request)
File “/usr/local/lib/python3.10/site-packages/django/core/handlers/base.py”, line 197, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File “/home/varashdev/mysite/core/views.py”, line 22, in article
return render(request, ‘article.html’, {‘post’:post, ‘related’:related})
File “/usr/local/lib/python3.10/site-packages/django/shortcuts.py”, line 24, in render
content = loader.render_to_string(template_name, context, request, using=using)
File “/usr/local/lib/python3.10/site-packages/django/template/loader.py”, line 62, in render_to_string
return template.render(context, request)
File “/usr/local/lib/python3.10/site-packages/django/template/backends/django.py”, line 62, in render
return self.template.render(context)
File “/usr/local/lib/python3.10/site-packages/django/template/base.py”, line 175, in render
return self._render(context)
File “/usr/local/lib/python3.10/site-packages/django/template/base.py”, line 167, in _render
return self.nodelist.render(context)
File “/usr/local/lib/python3.10/site-packages/django/template/base.py”, line 1000, in render
return SafeString(“”.join([node.render_annotated(context) for node in self]))
File “/usr/local/lib/python3.10/site-packages/django/template/base.py”, line 1000, in
return SafeString(“”.join([node.render_annotated(context) for node in self]))
File “/usr/local/lib/python3.10/site-packages/django/template/base.py”, line 958, in render_annotated
return self.render(context)
return SafeString(“”.join([node.render_annotated(context) for node in self]))
File “/usr/local/lib/python3.10/site-packages/django/template/base.py”, line 1000, in
return SafeString(“”.join([node.render_annotated(context) for node in self]))
File “/usr/local/lib/python3.10/site-packages/django/template/base.py”, line 958, in render_annotated
return self.render(context)
File “/usr/local/lib/python3.10/site-packages/django/template/defaulttags.py”, line 194, in render
len_values = len(values)
File “/usr/local/lib/python3.10/site-packages/django/db/models/query.py”, line 302, in len
self._fetch_all()
File “/usr/local/lib/python3.10/site-packages/django/db/models/query.py”, line 1507, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File “/usr/local/lib/python3.10/site-packages/django/db/models/query.py”, line 57, in iter
results = compiler.execute_sql(
File “/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py”, line 1348, in execute_sql
sql, params = self.as_sql()
File “/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py”, line 588, in as_sql
result, params = self.get_combinator_sql(
File “/usr/local/lib/python3.10/site-packages/django/db/models/sql/compiler.py”, line 509, in get_combinator_sql
raise DatabaseError(
django.db.utils.DatabaseError: LIMIT/OFFSET not allowed in subqueries of compound statements.

16

What version of Django are you using? Which database engine?

17

Django==4.0.6, python==3.10, sqlite

I’m on PythonAnywhere. Those are their defaults.

18

It’s quite possible, likely even, that sqlite can’t do this, in which case you’d need to do this as two separate queries and concatenate these results yourself like you had identified in your original post.

19

OK, I’ll try. Thanks!