I am beginner with laravel 8 and i want to run sql query in larvel 8 please help me how can i make ? thank u

Room table

room table

Booking table

booking table

sql query

SELECT id as roomID, name, (SELECT count(id) from bookings where room_id=roomID group by 
room_id) as count FROM `rooms`;

I want output like this.

Room name ["Meeting Room","Office Rooms","Single room"]

Users booking counts

[1,0,1]

controller

   public function index()
    {

     $room = Room::get();
             $lebal=[];
             $data=[];
             foreach($room as $rooms){
                $userBooking =  Booking::where('room_id',$value->id)- 
                 >get()->groupBy('room_id');
                 foreach($userBooking as $users){
                    $data[] = $users->count('user_id');
                    $lebal[] = $rooms->name;
                 }
             }
              $data=[
                'lebal'         => $lebal,
                'data'          => $data,
              ];

          return view('web.dashboard',$data);
}

return $label

[
  "Meeting Room",
  "Single room"
]

return $data;

[ 1, 1 ]

I want output like this.

Room name ["Meeting Room","Office Rooms","Single room"]

Users booking counts

[1,0,1]

CodePudding user response：

Laravel includes Eloquent, an object-relational mapper (ORM) that makes it enjoyable to interact with your database. When using Eloquent, each database table has a corresponding "Model" that is used to interact with that table. In addition to retrieving records from the database table, Eloquent models allow you to insert, update, and delete records from the table as well.

If you are not interestet in using that ORM, you can always use the DB facade to access specific tables not correlating with a Model.

// Remember to import the facade at the top of your document
use Illuminate\Support\Facades\DB;

$results = DB::table('rooms')->select(DB::raw('id AS roomID, name, (SELECT count(id) FROM bookings WHERE room_id=roomID GROUP BY 
room_id) AS count)'))->get();

CodePudding user response：

just use this :

DB::unprepared( 'SELECT id as roomID, name, (SELECT count(id) from bookings where room_id=roomID group by room_id) as count FROM rooms');

laravel provides methods to do sql queries like select() , where() , ..... but if you want to write a specific query that laravel don't have or any query you want just use unprepared method that DB class provides