SQL按元表列排序，包括没有元条目的列 [英] SQL Order by meta table column including ones without meta entry

问题描述

我正在使用Laravel查询生成器.但是我想我的问题只是与SQL有关.

I am using Laravel Query Builder. But I guess my issue is just SQL related.

我有一个responses表和一个response_metas表.

I have a responses table and a response_metas table.

response_metas表结构为:

 id   |   response_id    |    name    |    value
-----------------------------------------------------

所有回复都有一些meta条目，有些具有名称= email meta.

All responses have some meta entries, some have the name=email meta.

有没有办法我可以得到:

Is there a way I can get:

对此元数据附加并排序的电子邮件(元)进行响应.以及没有name = email元条目的条目.

Responses having an email (meta) attached and ordered by this meta. As well as the ones without the name=email meta entry.

我可以做一个简单的连接:

I can do a simple join:

DB::table('responses')
 ->join('response_metas', 'responses.id', '=', 'response_metas.response_id');
 ->where( 'response_metas.name', 'email')
 ->orderBy( 'response_metas.value', 'ASC' )

但是很明显，我只收到包含电子邮件meta的回复.目标是首先获得此结果. 还有没有此meta条目的人.

But obviously I am getting only the responses having an email meta. The goal is to get this result first. But also the ones without this meta entry.

让我知道是否可以提出任何澄清.我已经使用UNION，CASE，Consitions进行了数小时的失败尝试……没有任何效果，但是我可能没有正确使用它们.

Let me know if I can bring any clarification. I have done hours of failed tries using UNION, CASE, Conditions... None worked but I was likely not using them the right way.

我们非常感谢您的帮助.

Any help is much appreciated.

推荐答案

如果您希望所有元数据的响应和排序(例如电子邮件元数据)应首先显示/排序，然后再显示其余的元数据，则可以通过

If you want all the meta data for responses and ordered like email meta data should appear/sort first and then rest of the meta data then you can use conditional order by

DB::table('responses')
     ->join('response_metas', 'responses.id', '=', 'response_metas.response_id');
     ->where( 'response_metas.name', 'email')
     ->orderByRaw('response_metas.name = "email" DESC')
     ->orderBy( 'response_metas.value', 'ASC' )

条件子句，如果等效于下面的表达式

The conditional clause if equivalent to below expression

->orderByRaw('CASE WHEN response_metas.name = "email" THEN 1 ELSE 0 END DESC')

要获取所有具有或不具有电子邮件元数据的响应，您可以使用左联接并将条件直接放入联接部分

To get all the responses either they have/don't have meta data for email you can use left join and put the conditions directly in join part

DB::table('responses')
    ->leftJoin('response_metas', function ($join) {
                $join->on('responses.id', '=', 'response_metas.response_id')
                     ->where( 'response_metas.name', 'email');
    })
    ->orderByRaw('CASE WHEN response_metas.value IS NOT NULL THEN 1 ELSE 0 END DESC')
    ->orderBy( 'response_metas.value', 'ASC' );

这篇关于SQL按元表列排序，包括没有元条目的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！