• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need help about Query Bilder

#1
I have read through the CI 4 docs but still confused about the query builder. Following code shows my current CI4 model:

PHP Code:
<?php
namespace App\Models;
use 
CodeIgniter\Model;

class 
TestModel extends Model
{

    protected $db;

    public function __construct( )
    {
        $this->db db_connect();
    }

 private function 
checkData$t$v) {
 return 
$db->table$t )->select()->where$d )->countAllResults();
 }


But I am little bit confused about the CI4 ways to get row or rows from database. In CI3 my code was:

PHP Code:
// Get rows
$query $this->db->select()->from$table )->where$arr )->get();
return 
$query->result();

// Get row
$query $this->db->select()->from$table )->where$arr )->get();
return 
$query->row(); 

Could you please help by write some demo code of the following two operations?

Thanks in advance.
"Who thinks in code"
Perfectly describes who I am
webdevron.com
Reply

#2
PHP Code:
$db      = \Config\Database::connect();
// or
$db db_connect();

$builder $db->table($table);

// Get rows
$builder->where($arr)->get();

return 
$builder->getResultArray();

// Get row
$builder->where($arr)->get();

return 
$builder->getRow(); 

If you have the code in different methods you will need to use the top two lines
of code in each method.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#3
(08-28-2019, 04:53 PM)InsiteFX Wrote:
PHP Code:
$db      = \Config\Database::connect();
$builder $db->table($table);

// Get rows
$builder->where($arr)->get();

return 
$builder->getResultArray();

// Get row
$builder->where($arr)->get();

return 
$builder->getRow(); 

If you have the code in different methods you will need to use the top two lines
of code in each method.

Thank you for your response. I have another problem like this.  Could you please give me a solution on this? In CI 3 I used to write MY_Model like bellow:

CI 3 MODEL
PHP Code:
public function get_rows$table$arr ){
    $this->db->select()->from($table)->where($arr)->get();
    return $query->result();
}

public function 
order_row($by){
    $this->db->order_by($by'desc');
    return $this;


CI 3 CONTROLLER
PHP Code:
$data $this->my_model->order_row('column')->get_rows'my_table', array() ) 


I am trying this in CI 4 like bellow:

CI 4 MODEL
PHP Code:
public function getData$t$v ){
        $builder $this->db->table$t )->where$v )->get();
        return $builder->getRow(); 
}

public function 
orderData$by ){t;
        $this->db->orderBy$byDESC );
        return $this;


CI 4 CONTROLLER
PHP Code:
$m = new \App\Models\ManageModel();
$data $m->orderData'column')->getData'my_table', array() ); 

But this is producing ERROR:

Call to undefined method CodeIgniter\Database\MySQLi\Connection::orderBy()

How to solve this?
"Who thinks in code"
Perfectly describes who I am
webdevron.com
Reply

#4
If you are using CodeIgniters Model then you need to setup the parameters in it.

Like primaryKey, table etc;
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#5
Something like below:

PHP Code:
// Your Model
private $builder;

public function 
__construct()
{
    parent::__construct();

    $db db_connect();
    $this->builder $db->table($table);
}

// get_rows
public function getRows($table$arr)
{
    $this->builder
         
->where($arr)
         ->get()
         ->getResultArray();

    return $this;
}

// order_row
public function orderRow($by)
{
    $this->builder
         
->orderBy($by'desc')
         ->get()
         ->getResultArray();

    return $this;
}

// call using:
$data $this->my_model->orderRow('column')->getRows('my_table', array()) 

Not tested but should work for you.

The table name is in the builder when connecting to the db.

PHP Code:
$this->builder $db->table($table); 
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#6
(08-30-2019, 04:37 AM)InsiteFX Wrote: If you are using CodeIgniters Model then you need to setup the parameters in it.

Like primaryKey, table etc;

REPUTATION NOT WORKING Sad Sad

By the way, so, if I want to create a BaseModel, how to manage it in this case? What will be your suggestion?
"Who thinks in code"
Perfectly describes who I am
webdevron.com
Reply

#7
It would be just like before extend the base model to your model.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply

#8
(08-28-2019, 11:55 AM)webdevron Wrote: I have read through the CI 4 docs but still confused about the query builder. Following code shows my current CI4 model:

PHP Code:
<?php
namespace App\Models;
use 
CodeIgniter\Model;

class 
TestModel extends Model
{

    protected $db;

    public function __construct( )
    {
        $this->db db_connect();
    

Because you are extending CodeIgniter\Model you don't need any of the code above.
That code would only be used when you are creating a model that does not extend CodeIgniter\Model - which is a perfectly legitimate thing to do in CI4. 

CodeIgniter's Model class has a lot of built-in functionality that makes it easy to use and a logical choice much of the time. You probably know this, but for those readers that might not, the documentation for CI4 Models is HERE.

If you are going to extend CodeIgniter\Model there are a couple of properties items that are very important.

PHP Code:
<?php namespace App\Models;

use 
CodeIgniter\Model;

class 
TestModel extends Model
{
    protected $table 'my_table';
    protected $primaryKey 'id';



There are several other properties you can use to configure the model - see the docs.

When you want to use TestModel then in a controller simply make this call. (Remember to "use" the model's namespace)

PHP Code:
$testModel = new TestModel(): 

There is a lot built into CodeIgniter\Model including a method that does exactly what your getData() function does. It returns a single row where the primary key matches the value passed in as the first parameter.

PHP Code:
// in the controller
$testRow $testModel->find($someKeyValue); 

find() is going to use the table and primary key you set in the class definition.

But the really cool thing is you can mix the Model methods with Query Builder methods, e.g.

PHP Code:
// in the controller
$data $testModel->where('status''active')
                  ->orderBy('last_login''asc')
                  ->findAll(); 

The important thing to note about those last two examples is that the entire class declaration is the one shown above. We didn't need to add any other methods to the class just set a couple of properties in order to run those examples.

There is a full set of CRUD methods baked into CodeIgniter\Model too! The CRUD stuff is well documented, but at this time full documentation of the available methods is missing from the docs. Examine the core file /system/Model.php to see the other functionality of the class.

If you wanted to turn the last example into a method defined in TestModel you could do this

PHP Code:
<?php namespace App\Models;

use 
CodeIgniter\Model;

class 
TestModel extends Model
{
    protected $table 'my_table';
    protected $primaryKey 'id';

    public function getAllActive($sort 'ASC')
    {
        $builder $this->builder();
        $query $builder->where('status''active')
                         ->orderBy('last_login'$sort)
                         ->get();
        return $query->getResult();  // returns an array of objects
    }


The method Model::builder()  (i.e. $this->builder(); ) returns a builder instance.

@webdevron, Got your PM, but cannot reply. You need to change your settings to accept messages.
Reply

#9
(08-31-2019, 02:10 PM)dave friend Wrote:
(08-28-2019, 11:55 AM)webdevron Wrote: I have read through the CI 4 docs but still confused about the query builder. Following code shows my current CI4 model:

PHP Code:
<?php
namespace App\Models;
use 
CodeIgniter\Model;

class 
TestModel extends Model
{

    protected $db;

    public function __construct( )
    {
        $this->db db_connect();
    

Because you are extending CodeIgniter\Model you don't need any of the code above.
That code would only be used when you are creating a model that does not extend CodeIgniter\Model - which is a perfectly legitimate thing to do in CI4. 

CodeIgniter's Model class has a lot of built-in functionality that makes it easy to use and a logical choice much of the time. You probably know this, but for those readers that might not, the documentation for CI4 Models is HERE.

If you are going to extend CodeIgniter\Model there are a couple of properties items that are very important.

PHP Code:
<?php namespace App\Models;

use 
CodeIgniter\Model;

class 
TestModel extends Model
{
    protected $table 'my_table';
    protected $primaryKey 'id';



There are several other properties you can use to configure the model - see the docs.

When you want to use TestModel then in a controller simply make this call. (Remember to "use" the model's namespace)

PHP Code:
$testModel = new TestModel(): 

There is a lot built into CodeIgniter\Model including a method that does exactly what your getData() function does. It returns a single row where the primary key matches the value passed in as the first parameter.

PHP Code:
// in the controller
$testRow $testModel->find($someKeyValue); 

find() is going to use the table and primary key you set in the class definition.

But the really cool thing is you can mix the Model methods with Query Builder methods, e.g.

PHP Code:
// in the controller
$data $testModel->where('status''active')
                  ->orderBy('last_login''asc')
                  ->findAll(); 

The important thing to note about those last two examples is that the entire class declaration is the one shown above. We didn't need to add any other methods to the class just set a couple of properties in order to run those examples.

There is a full set of CRUD methods baked into CodeIgniter\Model too! The CRUD stuff is well documented, but at this time full documentation of the available methods is missing from the docs. Examine the core file /system/Model.php to see the other functionality of the class.

If you wanted to turn the last example into a method defined in TestModel you could do this

PHP Code:
<?php namespace App\Models;

use 
CodeIgniter\Model;

class 
TestModel extends Model
{
    protected $table 'my_table';
    protected $primaryKey 'id';

    public function getAllActive($sort 'ASC')
    {
        $builder $this->builder();
        $query $builder->where('status''active')
                         ->orderBy('last_login'$sort)
                         ->get();
        return $query->getResult();  // returns an array of objects
    }


The method Model::builder()  (i.e. $this->builder(); ) returns a builder instance.

Thank you very much. This is not just a "REPLAY" but a "Tutorial of CI 4 MODAL"
"Who thinks in code"
Perfectly describes who I am
webdevron.com
Reply

#10
You can access the Query Builder by $this->builder()-> if extending CI 4 Model.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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