如何关联IN子查询结果并筛选dapps表中active=1的记录？

原生SQL实现 #

首先明确表关联前提：dapp_tokens表存在dapp_id字段关联dapps表的主键id，新增筛选条件后直接在原来的子查询中关联dapps表过滤即可。

基于IN子查询的写法（和原有逻辑兼容性最高） #

SELECT *
FROM tokens
WHERE active = 1
  AND id IN (
    SELECT dt.token_id
    FROM dapp_tokens dt
    INNER JOIN dapps d ON dt.dapp_id = d.id
    WHERE dt.active = 1 AND d.active = 1
  )
ORDER BY market_cap, id DESC
-- 分页参数按实际需求补充
LIMIT [每页数量] OFFSET [偏移量]

性能优化版（WHERE EXISTS写法，大数据量下查询效率更高） #

SELECT *
FROM tokens t
WHERE t.active = 1
  AND EXISTS (
    SELECT 1
    FROM dapp_tokens dt
    INNER JOIN dapps d ON dt.dapp_id = d.id
    WHERE dt.token_id = t.id AND dt.active = 1 AND d.active = 1
  )
ORDER BY t.market_cap, t.id DESC

现有Laravel查询代码修改 #

直接修改原有whereRaw内的子查询即可，改动最小：

$data['tokens'] = Token::where('active', 1)
    ->whereRaw('tokens.id in (select dt.token_id from dapp_tokens dt inner join dapps d on dt.dapp_id = d.id where dt.active = ? and d.active = ?)', [1, 1])
    ->sortable('market_cap')
    ->orderBy('id','desc')
    ->paginate($page_count);

如果要切换为性能更好的whereExists写法，代码如下（需要提前引入DB门面）：

use Illuminate\Support\Facades\DB;

$data['tokens'] = Token::where('active', 1)
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
              ->from('dapp_tokens as dt')
              ->join('dapps as d', 'dt.dapp_id', '=', 'd.id')
              ->whereColumn('dt.token_id', 'tokens.id')
              ->where('dt.active', 1)
              ->where('d.active', 1);
    })
    ->sortable('market_cap')
    ->orderBy('id','desc')
    ->paginate($page_count);

内容的提问来源于stack exchange，提问作者Dapp Future