CODEWITHSUNDEEP
mysqlsqllaravel
lene
lene

Reputation: 35

How to use `GroupBy` and `join` together in Laravel SQL query

I have two tables clinic and service. In service table there is a column clinic_id. Now I want to retrieve all the services under every column.

I am giving some demo data for better understand.

clinic table data

{id:1,name:clinic1},{id:2,name: clinic2}

service table data

{id: 1, clinic_id: 1, name: service11}, 
{id: 2, clinic_id: 1, name: service12}, 
{id: 3, clinic_id: 2, name: service21}, 
{id: 4, clinic_id: 2, name: service22}

Now I want to retrieve data like below

{clinic_name: clinic1}:[{service_name: service11}, {service_name: service12}],
{clinic_name: clinic2}:[{service_name: service21}, {service_name: service22}]

What is the SQL query for Laravel ?

Upvotes: 0

Views: 77

Answers (1)

Rubin Porwal
Rubin Porwal

Reputation: 3845

According to description as mentioned into question Eloquent relationships can be used to accomplish above task

According to documentation of Laravel

A "one-to-many" relationship is used to define relationships where a single model owns any amount of other models. For example, a blog post may have an infinite number of comments. Like all other Eloquent relationships, one-to-many relationships are defined by placing a function on your Eloquent model

Please try following code snippet

app\Models\Clinic.php

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Clinic extends Model
{
    /**
     * Get the comments for the blog post.
     */
    public function services()
    {
        return $this->hasMany('\App\Models\Service','clinic_id','id');
    }
}
?>

app\Http\Controllers\ClinicController.php

<?php namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Clinic;
use App\Http\Controllers\Controller;
use App\Http\Requests;

class ClinicController extends Controller
{
/**
 * Create a new controller instance.
 *
 * @return void
 */
public function __construct()
{

}

 function index()
 {
   $Clinics = Clinic::with('services')->get();
 }
}
?>

Upvotes: 1

Related Questions