how to write sql join query in laravel

I have the following working SQL query and it is working fine

I have tested in Database (SQL editor)

select b.name "group_id", b.name "group_name",bt.name "group_type",a.location_name ,b.total_users ,group_concat(u.full_name) "members",group_concat(ui.picture_1) "pictures",(select picture_1 from user_pictures where id IN 
(select uii.profile_id from users as uii where uii.id = b.owner_id )) as owner_images
from users u
inner join groups b
inner join group_types bt
inner join group_users bu
inner join addresses a
inner join user_pictures ui
on
u.id=bu.user_id and
b.id=bu.group_id and
bt.id=b.type_id and
a.id=b.address_id and
ui.id =u.profile_id and
bu.status="active" and bu.user_id=3 and b.owner_id <> 3
group by b.name, bt.name, bu.status

SAMPLE OUTPUT

QUESTION: I would like to convert it into laravel format.

I tried the following getting everything except the pictures field

$groupInfo =  DB::table('users as u')                     
                                ->join('group_users as bu', 'bu.user_id', '=', 'u.id')
                                ->join('groups as b', 'b.id', '=', 'bu.group_id')
                                ->join('group_types as bt', 'bt.id', '=', 'b.type_id')
                                ->join('addresses as a', 'a.id', '=', 'b.address_id')  
                                ->join('user_images as ui', 'ui.id', '=', 'u.profile_id')             
                                ->where('bu.status', '=', 'active')
                                ->where('bu.user_id', '=', $user_id)
                                ->where('b.owner_id', '<>', $user_id)
                                ->groupBy('b.name')
                                ->groupBy('bt.name')
                                ->groupBy('bu.status')
                                ->select('b.id as group_id','b.name as group_name','bt.name as group_type','b.owner_id','a.location_name','b.total_users',DB::raw('group_concat(u.id) as members'),DB::raw('group_concat(ui.image_1) as images'))
                                ->get();

        return $groupInfo;  

How to add following sql inner query in the above laravel query?

(select picture_1 from user_pictures where id IN 
(select uii.profile_id from users as uii where uii.id = b.owner_id )) as owner_pictures

How Can I get my query executed?

Answers


This is sample. You can edit more and use this query

$results= \DB::table('groups as b')
                ->join('group_types as bt', function($join) {
                    $join->on('bt.id', '=', 'b.type_id')
                         ->where('b.owner_id', '<>', 3);
                })
                ->join('group_users as bu', function($join) {
                    $join->on('b.id', '=', 'bu.group_id')
                         ->where('bu.status', '=', 'active');
                })
                ->select('b.name','bt.name')
                ->groupBy('b.name, bt.name')                    
                ->get();

Take this example, modify it.

$GetData=  Table1::Join('Table2', 'Table1.Id', '=', 'Table2.Id')
  ->where('MasterId',$MasterId) //optional where clause, if u have any spl condition.
  ->get();

Table1 is the model name.


Need Your Help

FAKE: How to define MSBuild properties?

f# f#-fake

I want to switch from MSBuild to FAKE. In my MSBuild script I create a Webdeploy package by invoking MSBuild with the properties DeployOnBuild=True and DeployTarget=Package. This will trigger webde...

Wordpress:Page Title Appears twice

css wordpress woocommerce

I am in the process of setting up my own e-commerce website powered by wordpress and woocommerce but i have come up against some issues.