Writing SQL subquery with CI4 active record

Question

I'm struggling to write my sql statement with subquery in Codeigniter 4 with active record.

SQL

SELECT
    p.id, p.catId, p.image, c.id, (c.name) as category
FROM
    product_feature p
JOIN
    categories c ON p.catId = c.id
WHERE
p.id IN(
   SELECT MAX(p.id) FROM product_feature p 
   GROUP BY p.catId 
)

Answer 1

Except for the subquery part, all parts are easy to write and straight forward.

The first step is to obtain an instance of the BaseConnection object, this is done by calling the static method connect() from the Database class which belongs to the Config namespace:

$db = \Config\Database:connect();

Now, you need to load an instance of the query builder class and pass it the name of the first table:

$builder = $db->table("product_feature p");

Now, you should use the select() method to write the SELECT portion of your query:

$builder->select("p.id, p.catId, p.image, c.id, c.name AS category");

After that, you have to use the join() method to write the JOIN portion of your code:

$builder->join("categories c", "p.catId = c.id");

Now we come to the interesting part, How to write the subquery ?

In fact, whereIn() method accepts the second parameter as an anonymous function that takes an instance of BaseBuilder which you can use to write the subquery, for example:

$builder->whereIn("p.id", function (BaseBuilder $subqueryBuilder) {
    return $subqueryBuilder->selectMax("p.id")->from("product_feature p")->groupBy("p.catId");
});

Let's put them all together now:

$db = \Config\Database::connect();
$builder = $db->table("product_feature p");
$builder->select("p.id, p.catId, p.image, c.name AS category");
$builder->join("categories c", "p.catId = c.id");
$builder->whereIn("p.id", function (BaseBuilder $subqueryBuilder) {
    return $subqueryBuilder->selectMax("p.id")->from("product_feature p")->groupBy("p.catId");
});

This will produce the desired result you're looking for, because if you use the method getCompiledSelect() on the builder instance, you will get the following result:

SELECT `p`.`id`, `p`.`catId`, `p`.`image`, `c`.`id`, `c`.`name` AS `category`
FROM `product_feature` `p`
JOIN `categories` `c` ON `p`.`catId` = `c`.`id`
WHERE `p`.`id` IN (SELECT MAX(`p`.`id`) AS `id` FROM `product_feature` `p` GROUP BY `p`.`catId`)

I suggest you take a look at CodeIgniter4 documentation about query builder and anonymous functions:

http://codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data