retrieving data from a database and re-retrieving from another database

how to i make the following request correctly and optimized?

PHP Code:

$BusinessOwnership = $this->BusinessOwnership->where('userID', session()->get('id'));

 foreach($BusinessOwnership as $row) {
 $data['business_profile'] = $this->BusinessProfile->where('id', $row['BusinessID']);
 } 


in this case it does not work

$BusinessOwnership - you must return one or more results with BusinessID, then extract information from BusinessProfile by id-> BusinessID

can anyone give an idea how to do it?

Join table using SQL?

Join table using SQL?

I want to use a model only

@Secux ,



(Using a join can be used in the Model) You can do what @nfaiz suggested by creating a more complex query. (Use this link https://codeigniter.com/user_guide/datab...cific-data. Then scroll up looking for $builder->join() )

OR

You could create a view in the database that joins both tables then in CI call the database view with your parameters.

Please avoid N+1 query (unless you have a different DBGroup connection)

@Secux ,

How where you able to solve your problem?

@Secux ,

How where you able to solve your problem?

I found this solution. I don't know how correct and optimized it is

PHP Code:

        $list_business = $this->BusinessOwnership->where('userID', session()->get('id'))->get()->getResult('array');

         if (!empty($list_business)) {
             $i = 0;
             foreach ($list_business as $row) {
                 $listBusiness[$i] = $this->BusinessProfile->where('id', $row['businessID'])->orderBy('id', 'asc')->findAll();
                 $i++;
             }
         }
         $data['business_profile'] = $listBusiness;