• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Can I have 2 tables in codeigniter 4 model?

#1
Hello

In CI v.3x I used (and I was used it extensively) the following code:


Code:
        public function __construct()
        {
            parent::__construct();
            $this->table_name = ('faq');
            $this->primary_key = ('faqID');
            $this->order_by = ('faqcategoryID, faqSorder DESC');
            // my vars, used in joins, to get the category name
            $this->table_name2 = ('faqcategory');
            $this->foreign_key = ('faqcategoryID');
            $this->parent_name = ('faqcategoryName');
        }   // end of constructor function
           
    }



So later, in the MyModel I was able to call funciton getWithCategories like this:

Code:
public function get_with_category ($table_name2 = FALSE, $parent_name = FALSE, $foreign_key = FALSE, $order_by = FALSE)
       {
   
           $data = array();
          
           $query = $this->db->query("SELECT $this->table_name. * , $this->table_name2.$this->parent_name
                                      FROM $this->table_name
                                      LEFT JOIN $this->table_name2
                                      ON ( $this->table_name.$this->foreign_key = $this->table_name2.$this->foreign_key)
                                      ORDER BY $this->order_by");
        
           if($query->num_rows() > 0)
           {
              
               foreach($query->result_array() as $row)
               {
                  
                   $data[] = $row; 
               }
           }
         
           $query->free_result();
          
           return $data;
          
       } // end of function get_with_category

The role of this function was to provide me with the category name for each pair of related tables. In this example I have the faq and faq category name, but I used this for each and every case where I needed to get the category name (news, articles, etc).

Is there any way to implement this in CodeIgniter 4? I checked the documentation but I didn't find a way to have 2 tables in one model. Any idea how could I create similar reusable function in CI 4?
Reply

#2
I've never used CI3, but I have been able to accomplish what you describe in CI4 by using the standard CI4 model and Entities.  Create a method inside your model that joins other tables with your model's table. Then the query's results can be returned as an Entity object by the method where the attributes of that Entity are columns from all of the joined tables.

First create your Entity: https://codeigniter.com/user_guide/models/entities.html.

Then, configure the model to return an Entity object instead of the usual array.

PHP Code:
protected $returnType    'App\Entities\User'

Create a method that joins multiple tables together in your model.

PHP Code:
public function byUserID(string $user_id) {
    $user = $this
        
->join('user_roles', 'users.role_id = user_roles.role_id')
        ->join('job_list', 'users.job_id = job_list.job_id')
        ->where('user_id', $user_id)
        ->first();
    return $user;


In this case, a User entity object is returned where the database columns for all joined tables are attributes.

PHP Code:
$user_model = new \App\Models\UserModel//Instantiate model
$user $user_model->byUserID('12345678'); //Instantiate User entity

echo $user->username;         //From users table
echo $user->first_name;       //From users table
echo $user->last_name;        //From users table
echo $user->role_name;        //From user_roles table
echo $user->job_name;         //From job_list table
echo $user->job_description;  //From job_list table 

You can also create new methods in your entity to add attributes.

PHP Code:
public function getFullName() {
    $this->attributes['full_name'] = $this->attributes['first_name'] . ' ' . $this->attributes['last_name'];
    return $this->attributes['full_name'];


$user->full_name will automatically call $user->getFullName() and return the result. For this to work properly your method name must begin with "get" and use camel case.  CI4 automatically converts the camel case method name to a snake case attribute. (e.g. $entity->getMethodName() translates to $entity->method_name)

I hope this helps!  Let me know how you make out.
Reply

#3
Model is basic for every table
- With real applications are much more complicated, so should use Query

Learning CI4 from my works, from errors and how to fix bugs in the community

Love CI & Thanks CI Teams

Reply

#4
If you use the QueryBuilder you can do it like this.

PHP Code:
$builder $db->table('mytable');
$query   $builder->get(); 

Then when your finished just change it back to the original table for the model.

See the builder method in ./system/Model.php
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#5
Thanks to all members here that find time to replay to my question. For the moment I will stick to method for each model (where needed) and I will see in the future, if I find convenient method as the old one, I will bump this thread with my solution.

Again, thanks for your time.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.