Optimize db query

Hi everybody,
everything is working fine with my code but i'm willing to know if there is a way to optimize, queries, db connections etc
when it's needed a lot different data from different tables.. somehing like UNIONS or JOINS don't seem to be the case to me but maybe they have other meaning too..
p.s. I know the code should be organized in model functions, this is just an example..
Thanks a lot for your suggestion!

PHP Code:

public function index($service_id)
{

  $data['service'] = $this->db->table('services')
                              ->select('*')
                              ->getWhere(['id' => $service_id],1)
                              ->getRowArray();

  $data['doctors'] = $this->db->table('doctors')
                              ->select('id,name,surname')
                              ->get()->getResult();

  $data['specialty'] = $this->db->table('specialties')
                                ->select('*')
                                ->getWhere(['id' => $data['service']['specialty_id']],1)
                                ->getRowArray();

  $data['studios'] = $this->db->table('studios')
                              ->select('id,city,address')
                              ->get()->getResult();

  $data["main_content"] = "pages/service_view";
  return view("layouts/main_layout",$data);
} 


Hi,
You can always write raw query to make one DB call. Create SQL query with required output (JOINs, UNIONS etc) and you can have one DB call. Please read below link on how to use raw queries
https://codeigniter.com/user_guide/datab...eries.html

Hi,
Not sure what much to optimize here

$this->db - is basically a service, so real DB connection is called only once. Many queries will not make multiple DB connections

I can't see unions and joins here and even if so - they would not make DB responses faster

Sugestions:
- try using objects instead of arrays. Objects simply work faster
- use models
- use entities and model returns (entity will return as object by default) - https://codeigniter4.github.io/CodeIgnit...ities.html
- i would optimize controller code, something like this

PHP Code:

class ServiceController extends Controller
{
    private $serviceModel;
    private $doctorModel;
    private $studioModel;
    private $specialityModel;

    public function __construct() 
    {
        $this->serviceModel = model(ServiceModel::class);
        $this->doctorModel = model(DoctorModel::class);
        $this->studioModel = model(StudioModel::class);
        $this->specialityModel = model(SpecialityModel::class);
    }

    public function index(int $id) 
    {
        $data = [
            'main_content' => 'pages/service_view',
            'service' => $this->serviceModel->find($id),
            'doctors' => $this->doctorModel->select('id, name, surname')->findAll(),
            'studios' => $this->studioModel->select('id, city, address')->findAll(),
            'specialty' => [],
        ];

        if (!empty($data['service'])) {
            $data['specialty'] = $this->specialityModel->find($data['service']->specialty_id);
        }

        return view('layouts/main_layout', $data);
    } 
}