[SOLVED] How do I add joins with selects in CodeIgniter/ActiveRecord?

How do I add joins with selects in CodeIgniter/ActiveRecord?

I want to add this part to my current sql in Active-Recod-class:

JOIN 
(SELECT product_id, max(angle) AS max_angle
FROM image t1
WHERE width = 100 AND height = 200
GROUP BY product_id) AS t1 ON (t1.product_id = mv_products.id)

What would be the correct approach to manage this?

Is the only way to download subquery-class github? https://github.com/NTICompass/CodeIgniter-Subqueries

Solution

This is exactly the kind of thing that you shouldn't do via CI's Active Record (Query Builder). It's there to help you with simple queries, not to force you to use it.

This is a custom query as such, should be executed via $this->db->query().

With the currently released versions of CI, you wouldn't be able to do that via AR anyway because join() will break that subquery while trying to escape it (it assumes that you're only passing two field names). CI 3.0-dev allows you to disable field name escaping, but again - you shouldn't use the query builder for what you're asking here.