Subqueries on CodeIgniter 4 using models

Question

I am programming using CodeIgniter 4 and I am using CodeIgniter's model as I understand it is a good practice to use it. It is a great way to do queries using one table very simple:

$this->myModel
         ->select('field')
         ->where('user_id', 1)
         ->findAll()

I don't even have to write the name of the table.

However, I am not able to do subqueries, in particular, I need user_id to be read from another table:

$this->myModel
         ->select('field')
         ->whereIn('user_id', SUBQUERY)
         ->findAll()

SUBQUERY is select('id') from users where status='active'.

However, this doesn't work:

$this->myModel
         ->select('field')
         ->whereIn('user_id', $this->userModel->select('id)->where('status', 'active')->findAll() )
         ->findAll()

Is there a way to do it with CodeIgniter's model or what is the best way to do it? Should I do the subquery with the Query Builder and mix it with the usage of the model?

Thanks!

Does this answer your question? [Writing SQL subquery with CI4 active record](https://stackoverflow.com/questions/66795533/writing-sql-subquery-with-ci4-active-record) — steven7mwesigwa, Dec 28 '22 at 17:42
`->whereIn()` accepts a callback as the second parameter in which you may define your subquery. Always read the docs first: [$builder->whereIn()](https://codeigniter.com/user_guide/database/query_builder.html#builder-wherein) — steven7mwesigwa, Dec 28 '22 at 17:46
@steven7mwesigwa Your suggestions are using Query Builder instead of CodeIgniter's Model, aren't they? — user1314836, Dec 29 '22 at 20:45
I found the solution checking again CodeIgniter's Model documentation. In the subquery, instead of using `$this->userModel->select('id)->where('status', 'active')->findAll()` which returns an array of "user" objects (even if it is with a single attribute 'status'), which is not valid to use as a subquery, I found the existence `findColumn()` function of CodeIgniter's Model. This way, using `$this->userModel->where('status', 'active')->findColumn('id')` is the solution. — user1314836, Dec 29 '22 at 21:20
This is the complete code for the example in my original question: $this->myModel ->select('field') ->whereIn('user_id', $this->userModel->where('status', 'active')->findColumn('id') ) ->findAll() — user1314836, Dec 29 '22 at 21:20
However, this has an inconvenient: two separate SQL queries are generated, when checking CodeIgniter's debug functions. Does this perform worse than a single SQL query with a subquery? — user1314836, Dec 29 '22 at 21:21

steven7mwesigwa · Answer 1 · 2023-01-07T00:56:55.567

0

Instead of:❌

 ->whereIn('user_id', $this->userModel->select('id')->where('status', 'active')->findAll() )

Use this:✅

// Get User table.
$userTable = $this->userModel->getTable();

// ...

->whereIn('user_id',
    static fn(\CodeIgniter\Database\BaseBuilder $builder) use ($userTable) => $builder->select('id')
        ->from($userTable)
        ->where('status', 'active')
)

$builder->whereIn()

Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with AND if appropriate:

You can use subqueries instead of an array of values:

Addendum

In your particular case, you could alternatively use $builder->join()

Permits you to write the JOIN portion of your query:

I.e:

$userTable = $this->userModel->getTable();
$myTable = $this->myModel->getTable();

$this->myModel
    ->select(["$myTable.field"])
    ->join($userTable, "$myTable.user_id = $userTable.id")
    ->where("$userTable.status", "active")
    ->get()->getResult();

edited Jan 07 '23 at 00:56

answered Dec 30 '22 at 03:31

steven7mwesigwa

5,701
3
20
34

I like the second solution you propose because it uses models (no table names here) but I am more used to subqueries than using join for this. In the first one, however, I see too much code. A simple select-from-where SQL query becomes `static fn(\CodeIgniter\Database\BaseBuilder $builder) => $builder->select('id')->from($this->userModel->getTable())->where('status', 'active')`. Isn't CodeIgniter supposed to simplify writing SQL queries? For this particular case, I see that writing SQL query is more simple than the CodeIgniter code solution. – user1314836 Jan 01 '23 at 23:21
In the first solution I get this error: "Using $this when not in object context search". Is that the expected behavior or am I doing something incorrect? – user1314836 Jan 06 '23 at 20:12
In the second solution I get duplicated results when there are two references in the second table, that's why I though of a subquery instead of join. – user1314836 Jan 06 '23 at 20:43
@user1314836 In the first solution, please check my edited answer to fix the error. My bad. – steven7mwesigwa Jan 07 '23 at 01:00
Thank you. In any case doesn't look too intuitive for me, the advantages of models disappear. Finally I made use of getTable() for the two tables and I did a join + groupby on the first table. At least the query with CodeIgniter looks better: $table1 = $this->modelModel->getTable(); $table2 = $this->model2Model->getTable();; $query = $this->modelModel->select(...) ->join(...) ->where(...) ->groupBy(...) ->findAll(); – user1314836 Jan 09 '23 at 23:13

Subqueries on CodeIgniter 4 using models

1 Answers1

Addendum