Django - query 2 tables with 1 search

Using Django Getting Started
1

I am continuing to learn Django as a newbie…I would like some direction in relation to 1 search query against two tables that hold the same headers such as customer names. So table 1 is customer names from 2022 and table 2 is customer names from 2021.

I can create the models / admin and URL and set the project up.

How do I create a query to search both tables at the same time and display the result?

View.py
    def index(request,):

    q = request.GET.get('q')

    if q:
        #this is what is searched against.....ie the columns in our model.py
        vector = SearchVector('name')
        
        #This is the value the user is searching for:
        query = SearchQuery (q)
        
        # customers = customer.objects.filter(name__search=q)
        # customers = customer.objects.annotate(search=vector).filter(search=query)
        customers = customer1.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=0.001).order_by('-rank')
        
    else:
        customers = None
    
    
    context = {'customers': customers}
    return render(request, 'index.html', context)



Index.html
<!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>GAEN</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet"
    integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
</head>

<body>

  {% include 'navbar.html' %}

  <div class="container">


    
    {% block content %}

    <br><br><br><br>
    <form>
      <div class="mb-3">
        <label for="Search Query" class="form-label ">
          <h3>db Search Query</h3>
        </label>
        <br><br>
        <input type="text" class="form-control" aria-describedby="#" name="q">
        <br><br>
        <button type="submit" class="btn btn-primary ">Submit</button>
        <button type="submit" class="btn btn-danger">Reset</button>
      </div>
    </form>

    {% if customers %}


    <br>
    <h3><mark>Results:</mark> {{ customer | length }}</h3>

    <br><br>
    {% for customer in customers %}

    <table class="table table-striped">
      <thead>
        <tr>
          <th scope="col">Search Result</th>
          <th scope="col">name</th>                  
        </tr>
      </thead>

        <tbody>
          <tr>
            <th scope="row">1</th>
            <td>{{ customer.name }}</td>            
          </tr>
        </tbody>
      </table>

    
      Rank: {{ customer.rank }}
      <br><br><br>

          {% endfor %}
      
      

      {% endif %}
  {% endblock %}

</div>





  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/js/bootstrap.bundle.min.js"
    integrity="sha384-pprn3073KE6tl6bjs2QrFaJGz5/SUsLqktiwsUTF55Jfv3qYSDhgCecCxMW52nD2"
    crossorigin="anonymous"></script>
2

Are the models essentially the same for the two tables? (Same columns in the same order?)

If so, you can write your two queries and join the results using the union function.

3

Flipping heck. I will try that later when I finish playing overwatch and come back to you. Thanks

4

although actually how would that look in the index.html for the tables to be displayed?

5

I don’t follow your question.

The return value of the union function is a queryset as described in the docs.

6

Sorry so how would I display that to the page. As per my index.html that displays 1 query. How would the 2 queries look ?

7

You’re looking at this from the wrong angle.

Your index.html doesn’t “display” anything.

Your view renders your template (index.html) to create html.

The data being passed by the view to the template, in the context, is a queryset. (It doesn’t need to be - the way the template is written, customers could be just about any iterable of objects. But in your case here, what you are using for customers in the context is a queryset.)