Help converting a big query to active record

[eluser]naez[/eluser]
We're switching from MySQL to PostgreSQL in the near furture and I would like to resolve this now.

Is this even ANSI SQL?

Code:

SELECT t.url, t.title, c.cat_url, u.username AS author, u.username_clean AS author_clean, t.description, s.Ratings, s.AvgRating
FROM a_realm_tutorials t
JOIN a_realm_categories c ON t.cat_id = c.category_id
JOIN a_realm_users u ON u.id = t.author_id
JOIN (

SELECT t.tut_id, SUM( IF( r.tutorial_id IS NULL , 0, 1 ) ) AS Ratings, AVG( r.rating_value ) AS AvgRating
FROM a_realm_tutorials t
LEFT JOIN a_realm_tutorial_ratings r ON t.tut_id = r.tutorial_id
GROUP BY t.tut_id
) AS s ON t.tut_id = s.tut_id
WHERE c.category_id =  {$id}

Looks like it would be easy but I don't know how to use active pattern to join a subquery (or even if it can?)

I could just make a view, I know... mysql doesnt allow subquerys in views though.

[eluser]Rey Philip Regis[/eluser]
Well actually, I havent tied to do a subquery using active record pattern. But I know "BUT HAVENT DONE IT YET", this can solve your problem.

Using "not active record":

Code:

$this->db->query("SELECT t.url, t.title, c.cat_url, u.username AS author, u.username_clean AS author_clean, t.description, s.Ratings, s.AvgRating
FROM a_realm_tutorials t
JOIN a_realm_categories c ON t.cat_id = c.category_id
JOIN a_realm_users u ON u.id = t.author_id
JOIN (

SELECT t.tut_id, SUM( IF( r.tutorial_id IS NULL , 0, 1 ) ) AS Ratings, AVG( r.rating_value ) AS AvgRating
FROM a_realm_tutorials t
LEFT JOIN a_realm_tutorial_ratings r ON t.tut_id = r.tutorial_id
GROUP BY t.tut_id
) AS s ON t.tut_id = s.tut_id
WHERE t.tut_id =  {$id}");

I dont know if this is possible with active record...I also want to know haha..

[eluser]naez[/eluser]
Nobody knows?

on a semi-related note, I've enabled DB caching, set the path to

Code:

$db['live']['cachedir'] = APPPATH.'db_cache/';

Made the dir 777.

And the thing isn't caching!

I added die statements to the DB_Cache.php file, and it seems to lock up around line 69

Code:

if ( ! is_dir($path) OR ! is_really_writable($path))
        {
            // the line I added:
            die('Failed to open cachedir: ' . $path);
            // If the path is wrong we'll turn off caching
            return $this->db->cache_off();
        }

Output:

Failed to open cachedir: /home/mySharedHostname/public_html/system/application/db_cache/

I tried searching these forums, it doesn't seem like a lot of people take advantage of the DB caching class, which makes me wonder if it is even functional? Maybe I'm doing something wrong, but it does not work on my Dev machine nor my Host.

[eluser]naez[/eluser]
I hate to quit bumping this, but does anyone know the answer to either of my questions?

[eluser]Jon L[/eluser]
Currently AR does not support subqueries (you could have looked at the AR code to find that out), I have no clue if it will be supported in the future.

Regarding ANSI SQL, no clue.

Regarding DB Cache, I've never used that, as I've always found file caching to be ultimately slower than just querying the db, storing in a session, or storing in memory using something like Memcache (which is my current caching solution)

To find out what's wrong tho, do a var_dump() on the 2 values in your IF statement, replace the OR with a comma, and you should see which statement evaluates to false, which will point you towards what is breaking.