How to use Query Builder SubQuery in Model

How to use Query Builder SubQuery in 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: How to use Query Builder SubQuery in Model (/showthread.php?tid=87747)

How to use Query Builder SubQuery in Model - Corsari - 05-27-2023

Hello
the premise is that the tables of this thread are part of an help desk system, in particular we are in the ticket system

the 'Posts' table contains all the messages between me and all the users for all the existing tickets
each post has its ID , Date, Ticket_Id, User_Id and Message

In a summary view I need to show details taken from various tables

The problem to solve is this
- JOINing the 'Posts' table I don't want to load all the posts between me and the given user for the given ticket-id, instead I need just the last/newest post in each given ticket
( in the summary view they will be paginated 5 tickets per page and in each of those five rows, I want to echo and excerpt of the very last message written in that given ticket id )

So I need to get the newest/latest post ROW, no matter who is the poster

I have solved this successfully at Controller level in the following manner but I'd like to transfer/rewrite this into a Model

PHP Code:
    public function list()
    {

        $this->ticketModel = new \App\Models\TicketArrModel(); // Model returning an Array

        $data = [
            'ticket' => $this->ticketModel->orderBy('lastactivity','DESC')->paginate(5,'group1'),
            'pager' => $this->ticketModel->pager,
        ];


        $i = 0;                                      // good, after paginate() working only on 5 records
        foreach ($data['ticket'] as $x => $val) {
            $post = $this->postModel                 // but I have 5 queries on the Posts model
                         ->where('ticket_id', $val['id'])
                         ->orderBy('id', 'desc')     //  this is the part I don't know how
                         ->first();                  //  to do (I don't find JOIN with OrderBy clause. Maybe subquery?)
            $data['ticket'][$i]['messaggio'] = $post->message;
            $data['ticket'][$i]['utente'] = $post->user_id;
            $data['ticket'][$i]['datamess'] = $post->date;

            $dept = $this->deptModel                 // and 5 queries on the Depts model
                         ->where('id', $val['dept_id']);
            $data['ticket'][$i]['dept'] = $dept->name;

            $i++;
        }

        return view("admin/ticket/list", $data);
    } 

Just wondering how to have all of this in a GetTicketList() method in a Model with maybe just one query made with a SubQuery or a particular JOIN

I tried but I succeeded only partially,
because I can't get rid of JOIN(ing) the 'Post' table extracting only the very last post for the given ticket id

This could help https://stackoverflow.com/a/17038582/3446280 but how to apply this in query builder for MySQL?
Or this https://stackoverflow.com/a/66796891/3446280 need to study it.

I tried to use SubQueries http://codeigniter.com/user_guide/database/query_builder.html#query-builder-where-subquery but I get error because this pertains to the query builder class

Thank you for any help

My model is currently the following one, but it doesn't work as the controller above does, because the second join loads all the posts

PHP Code:
    public function GetTicketList()
    {
      
        $data = [
            'ticket' =>  $this
            ->select('ticket.date, ticket.subject, ticket.id, ticket.ticket_id, ticket.dept_id, ticket.name, ticket.lastActivity, ticket.note, dept.name as dept, post.user_id as utente, post.date as datamess, post.message as messaggio')
            ->join('dept', 'dept.id = ticket.dept_id')
            ->join('post', 'post.ticket_id = ticket.id')
            ->orderBy('ticket.lastActivity', 'DESC')
            ->paginate(5,'group1'),
            'pager' => $this->pager,
        ];

        return $data;
    } 

RE: How to use Query Builder SubQuery in Model - iRedds - 05-27-2023

Your last request will not work as you want, because for each ticket there will be as many lines created as there are joins found.

If you're using subqueries via a closure, you'll either need to import the query builder class or remove the type hint.

PHP Code:
use CodeIgniter\Database\BaseBuilder;
// or 
static function ($builder) {
 // ... subquery
} 

RE: How to use Query Builder SubQuery in Model - Corsari - 05-27-2023

(05-27-2023, 09:49 AM)iRedds Wrote: Your last request will not work as you want, because for each ticket there will be as many lines created as there are joins found.

If you're using subqueries via a closure, you'll either need to import the query builder class or remove the type hint.

PHP Code:
use CodeIgniter\Database\BaseBuilder; // or static function ($builder) { // ... subquery }

Thank you

I'll try,

but if you may hint/help for the right modification to my method, I thank you even more in advance

RE: How to use Query Builder SubQuery in Model - iRedds - 05-27-2023

I think the query should look like this, but I don't know if a subquery would work instead of a table in the join method.

Code:
SELECT ticket.*, p.id as post_id, p.*

FROM ticket

LEFT JOIN (

    SELECT * FROM post p2 WHERE id = (SELECT id FROM post WHERE ticket = p2.ticket ORDER BY id DESC LIMIT 1)

) as p ON ticket.id = p.ticket

ORDER BY ticket.id DESC

RE: How to use Query Builder SubQuery in Model - Corsari - 05-27-2023

(05-27-2023, 11:51 AM)iRedds Wrote: I think the query should look like this, but I don't know if a subquery would work instead of a table in the join method.

That is my initial aim. I was wondering about how to make a JOIN that picks only the latest post

RE: How to use Query Builder SubQuery in Model - Corsari - 05-28-2023

Ok

I have written the working MySQL query

PHP Code:
SELECT
  ticket.date, 
  ticket.subject, 
  ticket.id, 
  ticket.ticket_id, 
  ticket.dept_id, 
  ticket.name, 
  ticket.lastActivity, 
  ticket.note, 
  dept.name as dept, 
  post.user_id as utente, 
  post.date as datamess, 
  post.message AS messaggio
FROM ticket
LEFT JOIN dept ON ticket.ticket_id = dept.id
LEFT JOIN post ON post.ticket_id = ticket.id
WHERE
  post.date = (
      SELECT MAX(post.date)
      FROM post
      WHERE post.ticket_id = ticket.id
  )
ORDER BY ticket.lastactivity DESC; 

with the hint of this https://stackoverflow.com/a/35965649/3446280

Now the point is the best practice to set in the Model. Model methods only? Query Builder? RAW query?

Thank you for any direction

RE: How to use Query Builder SubQuery in Model - Eyad Mohammed Osama - 05-29-2023

(05-28-2023, 06:47 AM)Corsari Wrote: Ok

I have written the working MySQL query

PHP Code:
SELECT ticket.date, ticket.subject, ticket.id, ticket.ticket_id, ticket.dept_id, ticket.name, ticket.lastActivity, ticket.note, dept.name as dept, post.user_id as utente, post.date as datamess, post.message AS messaggio FROM ticket LEFT JOIN dept ON ticket.ticket_id = dept.id LEFT JOIN post ON post.ticket_id = ticket.id WHERE post.date = ( SELECT MAX(post.date) FROM post WHERE post.ticket_id = ticket.id ) ORDER BY ticket.lastactivity DESC;

with the hint of this https://stackoverflow.com/a/35965649/3446280

Now the point is the best practice to set in the Model. Model methods only? Query Builder? RAW query?

Thank you for any direction

I don't think that what you want can be done using Model methods because they're meant for simpler usage cases, and using the raw query can give you a performance boost but it has its own disadvantages

In my opinion, a query builder provides a cleaner and better way to write the query for several reasons:

Portability: your queries will still work fine in case you changed your DBMS from MySQL to something else because of the abstraction layer it provides
Security: all values are automatically escaped providing a safer query to be executed on the DBMS and reducing the risks of executing harmful queries
Readability: the query can be easily read and understood
Functionality: there are many methods to assist you and ease things for you

This code produces the equivalent query you want:

PHP Code:
$db = \Config\Database::connect();
$builder = $db->table("ticket t");
$builder->select("t.date, t.subject, t.id, t.ticket_id, t.dept_id, t.name, t.lastActivity, t.note, dept.name AS dept, post.user_id as utente, post.date as datamess, post.message AS messaggio");
$builder->join("dept", "ticket.ticket_id = dept.id", "LEFT");
$builder->join("post", "post.ticket_id = ticket.id", "LEFT");
$builder->where("post.date", function (\CodeIgniter\Database\BaseBuilder $subqueryBuilder) {
    return $subqueryBuilder->selectMax("post.date")
        ->from("post")
        ->where("post.ticket_id", "ticket.id");
});
$builder->orderBy("ticket.lastactivity", "DESC"); 

RE: How to use Query Builder SubQuery in Model - Corsari - 05-29-2023

Hello Mohammed
Thank you really much for your explanation and for the code
This Is a great and much appreciated Surprise!
Thank you

RE: How to use Query Builder SubQuery in Model - Eyad Mohammed Osama - 05-30-2023

(05-29-2023, 05:10 AM)Corsari Wrote: Hello Mohammed
Thank you really much for your explanation and for the code
This Is a great and much appreciated Surprise!
Thank you

You're welcome, my friend!
That's my pleasure!
Anything for you!