Converting SQL query into CodeIgniter format query

Currently I have 2 tables in my mysql database and I'm comparing my entries in both those tables to get an output. The output is correct when placed as a query in mysql. Now I want to convert it to CodeIgniter format code. I don't want to use the $this->db->query() operator for this.
The following is my query:

Code:

with X as (
  select l.*,
        (select status_from from logs where logs.refno = l.refno and logs.logtime >= '2021-10-01' order by logs.logtime limit 1) logstat
    from listings l
    where l.added_date < '2021-10-01')
select X.*, ifnull(X.logstat,X.status) stat20211001 from X;

@JJM50 ,

What version of CI are you using (CI 3.x or 4.x)? Also, more then likely you will have to use some form of $this->db->query() but CI gives you options that makes it easier.
Query binding for CI 4.x https://codeigniter.com/user_guide/datab...ight=query
Query binding for CI 3.x https://codeigniter.com/userguide3/datab...y-bindings

Also, keep in mind that you could create your query as a MySQL view which would allow you to have a simpler query in CI.