Welcome Guest, Not a member yet? Register   Sign In
How to make this sql queries with CodeIgniter 4
#1

Hey there!

I want to know how to do these 2 sql queries with CodeIgniter 4:

1. select max

Just a simple query like this:
Code:
select max(id) from table

My guess is this:
PHP Code:
$max $this->myModel->selectMax('id')->get()->getFirstRow()->id

However, it seems too difficult to understand. Two methods get + getFirstRow, two times "id" field name... Is there a better or easier to understand way?


2. Joins
I want to join the main table of a model with a secondary table. After getting the model I have a problem because the join creates two “id” fields. Manually writing the queries I can give an alias, such as 't1' and 't2' to the tables, so that I can say “orderby('t1.id'). But when getting the main table from the model, how can I set the alias or make it clear how to differentiate the main table from the secondary table?
Reply
#2

Hi,

1. You don't need to use ->getFirstRow() as max(id) will result in only one record being returned.
Code:
function getLastOrderId()
{
  $builder = $this->db->table('orders o');
  $builder->selectMax('o.id');
  $query = $builder->get();
  return $query->getRowArray();
}

2.
Code:
function getCustomers()
{
  $builder = $this->db->table('customers c');
  $builder->select('c.id, c.first_name, c.last_name, a.id AS address_id, a.line1, a.line2, a.city, a.post_code');
  $builder->join('addresses a', 'c.id = a.customer_id');
  $builder->orderBy('c.last_name DESC');
  $query = $builder->get();
  return $query->getResultArray();
}
Reply
#3

Thanks for both tips!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB