0

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.

Brendan Bullen
  • 11,607
  • 1
  • 31
  • 40
Siddharth
  • 1,649
  • 4
  • 22
  • 47
  • Possible duplicate of [Implementing group\_by and having in Laravel using Eloquent](http://stackoverflow.com/questions/16976241/implementing-group-by-and-having-in-laravel-using-eloquent) – Alex May 03 '16 at 13:12
  • Hi, that question is totally different, Please read at least once my full description, Thank you for your efforts – Siddharth May 03 '16 at 13:14

0 Answers0