CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Milan R Dhameliya
Milan R Dhameliya

Reputation: 89

How to join Table into Table in Laravel Eloquent

I Have one Table That is tbl_service table_service has relation with 4 other table as below

tbl_service_review tbl_service_images tbl_service_packages tbl_service_step tbl_user

I Have Joined This Four Table With Tbl_service Model Relationship. Now, I want To Subjoin table_user into tbl_service_review.......basically i want reviewer profile_pic And His Name from database......i have already joined tbl_user for who added service......

please help me with this...... Thank You In Advance.

function get_service_detail_by_id(Request $request) {
    $rule = [
        'service_id' => 'required',
    ];

    $validate = Validator::make(request()->all(), $rule);

    if ($validate->fails()) {
        return response()->json(['data' => ['errors' => $validate->errors()->all()], 'message' => 'validation fail', 'status' => '0'], 406);
    }
    $userId = Auth::id();

    $service_id = $request->input('service_id');

    $package = Tbl_service::select(array('tbl_service.*',
                DB::raw("IFNULL((select IFNULL(avg(rate),0) from tbl_rating where service_id = tbl_service.service_id group by service_id),0) as avg_rating"),
                DB::raw("IFNULL((select COALESCE(count(rate),0) from tbl_rating where service_id = tbl_service.service_id group by service_id),0) as total_rating"),
                DB::raw("IFNULL((select COALESCE(count(like_to),0) from tbl_service_favorite where like_to = tbl_service.service_id group by service_id),0) as total_like"),
                DB::raw("IFNULL((select CASE WHEN id THEN 1 END from tbl_service_favorite where like_to = tbl_service.service_id and like_by = $userId group by tbl_service.service_id),0) as is_like_by_me"),
            ))
            ->with('Images', 'Packages', 'User_data', 'Steps')
            ->where('service_id', $service_id)
            ->get();

    return response()->json(['data' => $package, 'message' => 'Get List Service Detail Successfully', 'status' => 1], 200);
}

Table_service Model

public $timestamps = false;
protected $table = 'tbl_service';

public function Images() {
    return $this->hasMany('App\Models\Tbl_service_image', 'service_id', 'service_id');
}

public function Packages() {
    return $this->hasMany('App\Models\Tbl_service_package', 'service_id', 'service_id');
}

public function User_data() {
    return $this->hasMany('App\User', 'id', 'seller_id');
}

public function Steps() {
    return $this->hasMany('App\Models\Tbl_service_step', 'service_id', 'service_id');
}

public function Review() {
    return $this->hasMany('App\Models\Tbl_rating', 'service_id', 'service_id');
}

}

My Current Response is 

"data": [
    {
        "service_id": 2,
        "category_id": 2,
        "seller_id": 1,
        "service_title": "text",
        "service_package": 3,
        "created_at": "2019-08-06 09:20:45",
        "avg_rating": "5.0000",
        "total_rating": 2,
        "total_like": 0,
        "is_like_by_me": 0,
        "Review": [
            {
                "rating_by": 2,
                "rate": 5,
                "rating_text": "Good",
                "created_at": "2019-08-06 10:09:33",
            },
            {
                "rating_by": 2,
                "rate": 5,
                "rating_text": "Good",
                "created_at": "2019-08-06 10:09:33",
            }
        ],

        "user_data": [
            {
                "id": 1,
                "role": 0,
                "thirdparty_id": null,
                "login_by": 0,
                "name": "Test User",
                "email": "[email protected]",
                "country": "India",
                "languages": "English",
                "profile_pic": "https://images.unsplash.com/photo-1497316730643-415fac54a2af?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&w=1000&q=80",
                "is_online": 0,
                "last_seen": null,
                "description": "User Details",
                "password": "$2y$10$qf1ohDCSb0x7HrFDvF6cWe.B1V41r4gjuAhMm0DQFyhM1fy7ieKR2",
                "temp_pass": null,
                "is_active": 1,
                "is_blocked": 0,
                "user_lat": "21.12345",
                "user_long": "72.12345",
                "created_at": "2019-08-02 08:49:48"
            }
        ],

    }
],
"message": "Get List Service Detail Successfully",
"status": 1

My Expected Response Is 

"data": [
    {
        "service_id": 2,
        "category_id": 2,
        "seller_id": 1,
        "service_title": "Text",
        "service_package": 3,
        "created_at": "2019-08-06 09:20:45",
        "avg_rating": "5.0000",
        "total_rating": 2,
        "total_like": 0,
        "is_like_by_me": 0,
        "Review": [
            {
                "rating_by": 2,
                "rate": 5,
                "rating_text": "Good",
                "created_at": "2019-08-06 10:09:33",
                "name": "trst",
                "profile_pic": "https://images.askmen.com/1080x540/2016/01/25-021526-facebook_profile_picture_affects_chances_of_getting_hired.jpg"
            },
            {
                "rating_by": 2,
                "rate": 5,
                "rating_text": "Good",
                "created_at": "2019-08-06 10:09:33",
                "name": "trst",
                "profile_pic": "https://images.askmen.com/1080x540/2016/01/25-021526-facebook_profile_picture_affects_chances_of_getting_hired.jpg"
            }
        ],

        "user_data": [
            {
                "id": 1,
                "role": 0,
                "thirdparty_id": null,
                "login_by": 0,
                "name": "Test User",
                "email": "[email protected]",
                "country": "India",
                "languages": "English",
                "profile_pic": "https://images.unsplash.com/photo-1497316730643-415fac54a2af?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&w=1000&q=80",
                "is_online": 0,
                "last_seen": null,
                "description": "User Details",
                "password": "$2y$10$qf1ohDCSb0x7HrFDvF6cWe.B1V41r4gjuAhMm0DQFyhM1fy7ieKR2",
                "temp_pass": null,
                "is_active": 1,
                "is_blocked": 0,
                "user_lat": "21.12345",
                "user_long": "72.12345",
                "created_at": "2019-08-02 08:49:48"
            }
        ],

    }
],
"message": "Get List Service Detail Successfully",
"status": 1

Upvotes: 0

Views: 125

Answers (1)

phpdroid
phpdroid

Reputation: 1663

What your are looking forward to is merging relationship
Here is the link
Just merge Review relationship merge useredata->pluck('profile_pic') and review object

Upvotes: 1

Related Questions