如何在django中合并两个查询，并从两个不相关的表中选择不同的查询

I'm working in Django (1.8) with a MySQL backend. I have two unrelated tables that I want to query. The results are a concatenation of two columns from each table. I want to merge the results, sort them and select distinct. For the sake of example let's assume I have tableA and tableB both of which have first_name and last_name columns. Each cloumn heading has a reference to the table in it (e.g. first_name_A). Currently I'm running a query on each table and using sorted(chain(queryA, queryB)) to put them together. The problem comes when I try to reference the result in a for loop in the template.

我正在使用MySQL后端在Django（1.8）中工作。我有两个不相关的表，我想查询。结果是每个表中两列的串联。我想合并结果，对它们进行排序并选择不同。为了举例，我们假设我有tableA和tableB，它们都有first_name和last_name列。每个cloumn标题都引用其中的表（例如first_name_A）。目前我正在对每个表运行查询并使用sorted（chain（queryA，queryB））将它们放在一起。当我尝试在模板中的for循环中引用结果时出现问题。

Codewise I have this:

Codewise我有这个：

views.py

views.py

queryA = tableA.objects.order_by('first_name_A', 'last_name_A').values('first_name_A', 'last_name_A').distinct()
queryB = tableB.objects.order_by('first_name_B', 'last_name_B').values('first_name_B', 'last_name_B').distinct()

query = sorted(chain(queryA, queryB))

return render_to_response('results.html', {'queries': query})

html

HTML

<div>
{% for query in queries %}
    <tr>
        <td>{{ query.first_name_A }} {{ query.last_name_A }}</td>
    </tr>
{% endfor %}
</div>

The above html will obviously only return the first and last names from tableA. These are sorted and distinct but no info from tableB is included. As the columns from each table have different headings and no properties alike other than the values (rather than the keys) how can I merge them, sort and select distinct combinations? Or should I just rename the columns to remove the table reference?

上面的html显然只返回tableA中的名字和姓氏。这些是有序和不同的，但不包括tableB的信息。由于每个表中的列具有不同的标题，除了值（而不是键）之外没有其他属性，我如何合并它们，排序和选择不同的组合？或者我应该只重命名列以删除表引用？

2 个解决方案

#1

You can use the extra() modifier to assign alternative names.

您可以使用extra（）修饰符指定备用名称。

queryA = tableA.extra(select={'alias' : 'first_name_A'}).order_by('first_name_A').values('alias').distinct()
queryB = tableB.extra(select={'alias' : 'first_name_B'}).order_by('first_name_B').values('alias').distinct()

The django documentation discourages the use of extra but there doesn't seem to be an easy way to create aliases in a queryset. This ticket suggest that this may change in the future. The last comment describes an alternative method with F expressions and annotate which should work with django v1.8

django文档不鼓励使用额外的，但似乎没有一种简单的方法在查询集中创建别名。此票证表明这可能在将来发生变化。最后一条评论描述了一个使用F表达式和注释的替代方法，它应该与django v1.8一起使用

#2

I've solved it but I'm not sure it's the most pythonic (nor correct) way. If anyone has any better suggestions then please let me know.

我已经解决了，但我不确定它是最pythonic（也不正确）的方式。如果有人有任何更好的建议，请告诉我。

views.py

views.py

        queryA = tableA.objects.order_by('first_name_A','last_name_A').values('first_name_A', 'last_name_A').distinct()
        queryB = tableB.objects.order_by('first_name_B','last_name_B').values('first_name_B', 'last_name_B').distinct()


        chain_query = sorted(chain(queryA, queryB))


        valueList = []


        for q in chain_query:
            wholeName = ' '.join(q.values())
            valueList.append(wholeName)


        query = sorted(set(valueList))  

        return render_to_response('results.html', {'queries': query})

html

HTML

    {% for query in queries %}
    <tr>
        <td>{{ query }}</td>
    </tr>
    {% endfor %}