Apply Where Clause of Left Join when common results are fetched - Laravel 5.1

my database structure is as follows:

Table: users

id | username | email | password | status

Table: articles

id | user_id(fk) | title | other | status

Table: analytics

id | user_id | article_id(fk) | session | revenue | country_code

Now I want all the articles, but also I want the data from analytics table. I want all the rows GROUP BY Article ID from analytics whose Total SUM of Session is greater than 200.

What I performed is as follows:

$articles = Article::selectRaw('articles.*, sum(analytics.revenue) as sum, IF(Sum(analytics.sessions) >= 200, Sum(analytics.sessions), 0)  AS sum_sessions
                ->join('users', 'users.id', '=', 'articles.user_id')
                ->leftJoin('analytics', function ($join) {
                    $join->on('analytics.article_id', '=', 'articles.id')
                        ->on('analytics.user_id', '=', 'users.id')
                        ->where('users.status','!=', '3')
                        ->where('analytics.country_code','=', 'US');
                })
                ->where('articles.status', 4)
                ->groupBy('articles.id')
                ->orderBy('articles.created_at', 'DESC')
                ->get();

But after putting the following condition in LEFT JOIN

->on('analytics.user_id', '=', 'users.id')
->where('users.status','!=', '3')
->where('analytics.country_code','=', 'US');

results are not being shown as it should be.

So what I want to do is I want to apply where condition to the LEFT JOIN only when common results appears from both the table, not when results from Left Tables are only shown.

Hi please read the full question, this is not duplicate or copied, this is what I'm facing right now. So please I request not to mark it as Duplicate untill you understand it. Thank you

Thank you in advance.

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/1rQScDm
via IFTTT