Can this be done in CI's `Active Record`

[eluser]micha8l[/eluser]
Hi, I'm quite new to CI. I'm trying my best to abide tightly to CI's Active Record, but there's one SQL query I cannot build with CI and that is below:

Code:

snipped

The query fetches related articles that share >= 2 keywords in a foreign table.

I'm just wondering if it's possible.

[eluser]PhilTem[/eluser]
Not with the basic method-calls you see in the user guide. Search the forums or the web for "codeigniter subqueries", there are a lot of posts on this topic.

Just some links
[url]http://heybigname.com/2009/09/18/using-code-igniters-active-record-class-to-create-subqueries/[/url
https://github.com/NTICompass/CodeIgniter-Subqueries
http://codeigniter.com/wiki/Subqueries

PS: I never used subqueries so far hence I cannot tell you which of these sample links are the best solution. Just give each of them a try

[eluser]CodeTroll[/eluser]
This : https://github.com/NTICompass/CodeIgniter-Subqueries works like a charm..

I can still can't see why this functionality is missing from active record. It is not that uncommon to use subqueries and I guess most people that do would like to retain the active record way to do it rather than writing custom sql statements for it.

[eluser]Aken[/eluser]
The point of active record (which will be Query Builder in CI 3.0) is to abstract queries into a way that can be used across multiple database environments. As it stands, query builder allows an application to be installed using any number of supported DBs. Complex queries like this may not be compatible with one or more DBs, and therefore is pointless to integrate into query builder.

Not to mention that this library completely circumvents the benefits of using subqueries, which is to turn more than one separate query into a single query. This lib has to make a separate DB connection, run a query, and close the connection on EVERY use. Very poor performance.

Active Record / Query Builder are optional. If you do not need to support multiple DBs, there is zero requirement to use it. Sure, it's handy for writing less complex queries, and it's auto-escape features and such. But it is not absolutely necessary, and can even hinder your development time in situations like this.

I can't see this ever becoming a part of CodeIgniter. It over-abstracts queries and hurts performance.