Complicated INNER JOIN Mysql Query From 3 Tables in Laravel

Question

I've been on this for a few hours without solution. I thought of laravel relationships but don't know how to pass a second condition because I need to relate with 3 tables. I'd like to use the query below in laravel.

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

There are 3 tables.

subscriptions: subscriber_id subscribes to broadcaster_id.
broadcasts: where broadcaster's message is saved.
seen_broadcast: where the information of subscribers are saved when they read a broadcast. This helps us provide detailed stats to broadcaster. user_id = subscriber_user_id, broadcast_id = broadcast_message_id

I want to be able to get broadcasts from all broadcaster that userA has subscribed to and have not seen.

The query above currently works outside laravel.

There are 3 tables. 1. subscriptions: subscriber_id subscribes to broadcaster_id 2. broadcasts: where broadcaster's message is saved 3: seen_broadcast: where the information of subscribers are saved when they read a broadcast. This helps us provide detailed stats to broadcaster. user_id = subscriber_user_id, broadcast_id = broadcast_message_id I want to be able to get broadcasts from all broadcaster that userA has subscribed to and have not seen. I hope this is clear enough — Overcomer
– Overcomer, Commented Mar 18, 2016 at 8:46
I think you are missing the key fields that the inner join links on table1 inner join table2 on table1.field = table2.field — user894932
– user894932, Commented Mar 18, 2016 at 8:58

Overcomer · Accepted Answer · 2016-03-18 13:04:10Z

1

After much runarounds, I ended up with this:

$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];

Also add use DB; in the controller.

If there's a better way to do this, please share.

edited Mar 18, 2016 at 13:04

answered Mar 18, 2016 at 10:09

Overcomer

4946 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Strawberry Over a year ago

Without a proper schema and a desired, we have limited means for assessing this answer. If it works for you, then great, but you might as well keep that information to yourself too.

num8er Over a year ago

You can use relations but in Your case it will send many requests to db. So for Your case to do so it's ok. But I'll prefer to keep additional table to collect statistics and etc using jobs in laravel.

Collectives™ on Stack Overflow

Complicated INNER JOIN Mysql Query From 3 Tables in Laravel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related