Can I have 2 tables in codeigniter 4 model?

Can I have 2 tables in codeigniter 4 model? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Can I have 2 tables in codeigniter 4 model? (/showthread.php?tid=77864)

Pages: 1 2

Can I have 2 tables in codeigniter 4 model? - t_5810 - 10-28-2020

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?

RE: Can I have 2 tables in codeigniter 4 model? - mlurie - 10-28-2020

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.

RE: Can I have 2 tables in codeigniter 4 model? - nc03061981 - 10-29-2020

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

RE: Can I have 2 tables in codeigniter 4 model? - InsiteFX - 10-29-2020

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

RE: Can I have 2 tables in codeigniter 4 model? - t_5810 - 11-01-2020

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.

RE: Can I have 2 tables in codeigniter 4 model? - Corsari - 05-17-2023

Hello
I landed on this topic because basically I'd like to use Models for DB methods and the relative queries

Well I have a complex query with some joins which require the access to two more tables in the same DB

E.G.
db name : tickets

Model is TicketModel which is set to work with 'ticket' table

but in the TicketModel I need to write a method more or less like

PHP Code:
    public function getTicketList()
    {
        return $this->select('ticket.ticket_id, ticket.subject, dept.name AS department, ticket.name AS user, ticket.custom_text, ticket.lastpost, post.message AS last_message')
            ->join('dept', 'dept.dept_id = ticket.dept_id')
            ->join('post', 'post.id = ticket.lastpost')
            ->groupBy('ticket.ticket_id')
            ->orderBy('ticket.ticket_id', 'ASC');
    } 

well, how do I "enable" the 'dept' and 'post' tables?

Thank you

RE: Can I have 2 tables in codeigniter 4 model? - InsiteFX - 05-19-2023

Use CodeIgniter 4 QueryBuilder class and joins on the different tables.

RE: Can I have 2 tables in codeigniter 4 model? - Corsari - 05-20-2023

(05-19-2023, 11:47 PM)InsiteFX Wrote: Use CodeIgniter 4 QueryBuilder class and joins on the different tables.

Hello thank you

I was reading that section of the guide

So you confirm it is possible to join different tables at the Model level using the query builder methods

and this task can be accomplished inside the Model
Right?

Entity Classes may be an alternative or they make no sense in the case of my question? Thank you hinting

And , still for learning, third option: aiming to write the RAW query. Wanting to use the RAW query, how do I access the others tables in this model? I tried but I get errors because the others tables looks like not found.

R

RE: Can I have 2 tables in codeigniter 4 model? - InsiteFX - 05-20-2023

This shows you how it is done.

stackoverflow -> Codeigniter 4 query builder join 2 or more tables without duplicate result

RE: Can I have 2 tables in codeigniter 4 model? - Corsari - 05-20-2023

(05-20-2023, 02:45 AM)InsiteFX Wrote: This shows you how it is done.

stackoverflow -> Codeigniter 4 query builder join 2 or more tables without duplicate result

Great thank you

Kindly , what about using a RAW query inside the model making that join?
I mean , just to understand the mechanic, how would be achieved to access many tables of the same db for those joins?

Thank you and thank you for the link