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.