来自 Laravel 中 3 个表的复杂内部连接 mysql 查询 - Complicated INNER JOIN Mysql Query From 3 Tables in Laravel

Complicated INNER JOIN Mysql Query From 3 Tables in Laravel

我已经在这个上面呆了几个小时没有解决方案。我想到了 laravel 关系，但不知道如何传递第二个条件，因为我需要与 3 个表相关联。我想在拉拉维尔中使用下面的查询。

SELECT
subscriptions.subscribed_to,
broadcasts. *,
FROM subscriptions
INNER JOIN broadcasts
WHERE subscriptions.subscriber = {$user_id}
AND (
    SELECT COUNT(*) FROM seen_broadcasts
    WHERE user_id = {$user_id}
    AND broadcast_id = broadcasts.id
) = 0
ORDER BY  broadcast.date DESC

有3张桌子。

订阅：subscriber_id订阅broadcaster_id。
：保存广播公司消息的位置。
seen_broadcast：订阅者在阅读广播时保存的信息。这有助于我们向广播公司提供详细的统计数据。 user_id = subscriber_user_id, broadcast_id = broadcast_message_id

我希望能够从用户 A 已订阅但未看到的所有广播公司获取广播。

上面的查询目前在 laravel 之外工作。

经过多次奔波，我最终得到了这个：

$broadcast_result = DB::select( DB::raw("
                                        SELECT
                                        subscriptions.subscribed_to,
                                        broadcasts.*
                                        FROM subscriptions
                                        INNER JOIN broadcasts
                                        WHERE subscriptions.browser_agent_id = :subsc_id
                                        AND broadcasts.user_id = subscriptions.subscribed_to
                                        AND (
                                            SELECT COUNT(*) FROM broadcasts_seen
                                            WHERE broadcast_id = broadcasts.id
                                            AND subscriber_id = subscriptions.subscriber_id
                                        ) = 0
                                        ORDER BY  broadcasts.date DESC LIMIT 1
                                    "), array(
                                       'subsc_id' => $subscriber->id
                                     ));
$broadcast_set = $broadcast_result[0];

同时在控制器中添加use DB;。

如果有更好的方法，请分享。