CODEWITHSUNDEEP
laraveljoinlaravel-query-builderlaravel-5.6
shababhsiddique
shababhsiddique

Reputation: 911

Laravel distinct on join results not working in query builder

I have a posts table that has join query with 4 other tables. 3 of them are one to one relations but the 4th is one to many. I want the query to return only 1 row for each post. What i am trying so far is like this-

$query = DB::table('posts')
                ->select('posts.*',
                        'subcategories.subcategory_title_en',
                        'subcategories.subcategory_title_bn',
                        'categories.category_title_en',
                        'categories.category_title_bn',
                        'users.*',
                        'postimages.postimage_thumbnail'
                        )
                ->join('subcategories', 'subcategories.subcategory_id', '=', 'posts.subcategory_id')
                ->join('categories', 'categories.category_id', '=', 'subcategories.parent_category_id')
                ->join('users', 'users.id', '=', 'posts.user_id')
                ->join('postimages', 'postimages.post_id', '=', 'posts.post_id');

        $query->groupBy('posts.post_id');

        echo $query->count();
        exit();

I have currently 50 posts in database, but the query returns all rows for each postimages, more than 1 row for each post that is. I thought distinct would only show each post_id once? What am i missing here?

I would prefer if someone tells me how to do this with query builder. As this will have a lot of searching on different columns and i want it to be as fast as possible.

This is a simpler version -

 $query = DB::table('posts')
                ->select('posts.*','postimages.postimage_thumbnail')
                ->join('postimages', 'postimages.post_id', '=', 'posts.post_id')
                ->groupBy('posts.post_id');


        echo $query->count();
        exit();

The strange thing is the SQL query that is shown by laravel " select posts.*, postimages.postimage_thumbnail from posts inner join postimages on postimages.post_id = posts.post_id group by posts.post_id" works fine in mysql

Upvotes: 2

Views: 5376

Answers (1)

afsal c
afsal c

Reputation: 610

You should use groupby

Try this code

 $query = DB::table('posts')
            ->select('posts.*',
                    'subcategories.subcategory_title_en',
                    'subcategories.subcategory_title_bn',
                    'categories.category_title_en',
                    'categories.category_title_bn',
                    'users.*',
                    'postimages.postimage_thumbnail'
                    )
            ->join('subcategories', 'subcategories.subcategory_id', '=', 'posts.subcategory_id')
            ->join('categories', 'categories.category_id', '=', 'subcategories.parent_category_id')
            ->join('users', 'users.id', '=', 'posts.user_id')
            ->join('postimages', 'postimages.post_id', '=', 'posts.post_id')->groupBy('posts.post_id');

In config/database.php at "mysql" change : 'strict' => true, to false

Upvotes: 3

Related Questions