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

Question

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 ?

What are you trying to do, and can you include some sample data which explains that? The error is a common one, stemming from using `SELECT *` (i.e. all columns) along with a `GROUP BY` clause on only one column. You can't do that in certain modes of MySQL. — Tim Biegeleisen, Oct 11 '18 at 05:58
Possible duplicate of [Group by not working - Laravel](https://stackoverflow.com/questions/41571271/group-by-not-working-laravel) — Bugfixer, Oct 11 '18 at 05:59
@TimBiegeleisen...I have to table 'clinic` and `service`...in service I have `clinic_id`...now I want to get all the services under every clinic — lene, Oct 11 '18 at 06:05
@Bugfixer...not at all...I have already change `strict => false` in `config/database.php` — lene, Oct 11 '18 at 06:07

score 1 · Answer 1 · answered Oct 11 '18 at 09:15

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();
 }
}
?>

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

1 Answers1