Question 282589 in Mysql Question posted in
The official MySQL documentation can be found here.

Django MySQL retrieving data from multiple tables

I am trying to retrieve data from multiple tables in the same query to be rendered in an html page.

Models and view below, ultimately I am trying to return a query set with following structure:

  1. filter SearchResults table by postcode
  2. queryset returns any listings from SearchResults matching the postcode, I would need to reference each field in SearchResults as well as including any related media records(one to many, i.e multiple media records for each listing)
  3. Not compulsory but nice to have the media returned in index order as I hope to render to html template in correct order
Models.py 

class SearchResults(models.Model):

    listing_id = models.AutoField(primary_key=True)
    listing_date_added = models.DateField
    listing_last_updated = models.DateField(blank=True, null=True)
    price_from = models.PositiveIntegerField(blank=True, null=True)
    price_to = models.PositiveIntegerField(blank=True, null=True)
    suburb = models.CharField(max_length=100, blank=True, null=True)
    state = models.TextField(blank=True, null=True)
    postcode = models.PositiveIntegerField(blank=True, null=True)

    def __str__(self):
        return f"{self.suburb}, {self.display_price}"

    class Meta:
        managed = False
        db_table = "listing"


class Media(models.Model):
    listing_id = models.ForeignKey(SearchResults, on_delete=models.DO_NOTHING)
    media_url = models.CharField(max_length=300, blank=True, null=True)
    media_index = models.PositiveIntegerField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'media'

    def __str__(self):
        return f"Media url: {self.media_url}, Media index: {self.media_index}"


Views.py

# POST request coming from initial search field entry
def search_results(request):
    if request.method == "POST":
        postcode_lookup = request.POST.get('search')[-4:]
        results = SearchResults.objects.filter(postcode=postcode_lookup).prefetch_related('media-set')
        print(results)
        return render(request, 'general/search.html', {'context': results})

I have tried changing the view around in multiple ways but I usually end up with operational error unknown column ‘media.id’ in ‘field list’.

Worst case scenario I can query the initial SearchResults with postcode filter and then loop through to retrieve associated media records, but in production it is possible I will have up to 5k SearchResults matching the postcode, which would kill the DB performance if it needs to query each one for the media records.

The DB Schema has listing_id as PK with listing_id as FK in the media table and I am running MySQL backend.

I am completely stuck, any help would be appreciated.

Tags:

2

Answers


  1. 0

    Use @property at your model:

    class SearchResults(models.Model):

    listing_id = models.AutoField(primary_key=True)
    listing_date_added = models.DateField
    listing_last_updated = models.DateField(blank=True, null=True)
    price_from = models.PositiveIntegerField(blank=True, null=True)
    price_to = models.PositiveIntegerField(blank=True, null=True)
    suburb = models.CharField(max_length=100, blank=True, null=True)
    state = models.TextField(blank=True, null=True)
    postcode = models.PositiveIntegerField(blank=True, null=True)

    def __str__(self):
        return f"{self.suburb}, {self.display_price}"

    class Meta:
        managed = False
        db_table = "listing"

    @property
    def media_list(self):
        medias=Media.objects.filter(listing_id=self.id).order_by(*your_desired_order*)
        return medias

    Add id in your media model(dont dorget to add it manually in your mysql table because media model have meta tags managed=false. Here the explanation:django primary key):

    id = models.AutoField(primary_key=True)

    With this you can get Media by:

    def search_results(request):
    if request.method == "POST":
        postcode_lookup = request.POST.get('search')[-4:]
        results = SearchResults.objects.filter(postcode=postcode_lookup)
        print(results)

        context = {
            'data': results,
        }
        return render(request, 'general/search.html', context)

    For the template:

        {% for data_result in data %}
    <!-- do something for SearchResults model here, example: -->
        {% data_result.state %}
        {% for data_media in data_result.media_list %}
         <!-- do something for Media model here, example: -->
            {{ data_media.media_url }}
        {% endfor %}
    {% endfor %}

    Note that this resulting the Media queryset inside SearchResults queryset, so treat it as queryset too at template.

    Login or Signup to reply.
  2. 0 
    def search_results(request):
    if request.method == "POST":
        postcode_lookup = request.POST.get('search')[-4:]
        results = SearchResults.objects.filter(postcode=postcode_lookup).prefetch_related('media_set')  
        print(results)
        return render(request, 'general/search.html', {'context': results})

    ‘media-set’ might be ‘media_set’
    Try this one.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search