CI Merging queries from two different models

[eluser]rockoo[/eluser]
Hey guys,

I have a huge issue I can't figure out for the life of me. So here's the deal. I'm using APC cache for queries. So I created a helper function to check / save cache and return result accordingly. I have modified DB_query_builder with function to return compiled SQL before the execution.

With no caching enabled all works ok. With caching enabled on the first reload works ok when there is no cached results. On the second reload I get "Not unique table/alias: 'c'". Upon inspecting the returned query in error. You can see that both queries were merged together.... I can't figure this out for the life of me. Can you please help me out.

ERROR

Error Number: 1066

Not unique table/alias: 'c'

SELECT a.id album_id, a.title album_title, UNIX_TIMESTAMP(a.date) album_date, a.info album_info, a.client_1_id, a.client_2_id, a.hosted_by_client_id album_venue_id, a.status album_status, a.classification_id, a.is_featured, a.views album_visits, a.page_views album_views, a.tags album_tags, a.slug album_slug, a.status, cy.category album_category, cn.classification album_genre, e.id event_id, e.title event_title, e.info event_info, e.client_id event_client_id, e.hosted_by_client_id event_venue_id, e.views event_views, e.genre event_genre, cnn.classification event_genre, IF(e.day_of_the_week > 0, "regular", "special") event_type, c.name client_name, c.type client_type, c.slug client_slug, ct.name venue_name, ct.slug venue_slug, CONCAT(u.first_name, " ", u.last_name) album_photographer, pr.id album_photographer_id, (SELECT COUNT(p.id) FROM picture p WHERE p.album_id = a.id AND p.status = 1) album_pictures, (SELECT COUNT(pc.id) FROM picture_comment pc WHERE pc.album_id = a.id AND pc.status = 1) album_comments, (SELECT COUNT(pt.id) FROM picture_tag pt WHERE pt.album_id = a.id AND pt.status = 1) album_tags, (SELECT p.file_path FROM picture p WHERE p.album_id = a.id LIMIT 1) path, c.id, c.name, c.info, c.address, c.email, c.phone_number, c.website, c.logo, c.type, UNIX_TIMESTAMP(c.created_on), c.status, c.operation_status, c.twitter, c.youtube, c.facebook, c.slug, c.latitude, c.longtitude, c.vote_up, c.vote_down, cy.category, CONCAT(u.first_name, " ", u.last_name) creator FROM `client` `c` LEFT JOIN `category` `cy` ON `cy`.`id` = `a`.`category_id` LEFT JOIN `classification` `cn` ON `cn`.`id` = `a`.`classification_id` JOIN `event` `e` ON `e`.`id` = `a`.`event_id` LEFT JOIN `classification` `cnn` ON `cnn`.`id` = `e`.`classification_id` JOIN `client` `c` ON `c`.`id` = `e`.`client_id` LEFT JOIN `client` `ct` ON `ct`.`id` = `e`.`hosted_by_client_id` JOIN `photographer` `pr` ON `pr`.`id` = `a`.`photographer_id` JOIN `user` `u` ON `u`.`id` = `pr`.`user_id` LEFT JOIN `category` `cy` ON `cy`.`id` = `c`.`category_id` LEFT JOIN `user` `u` ON `u`.`id` = `c`.`created_by_id` ORDER BY `a`.`date` desc, `a`.`sort` asc, `a`.`created_on` desc LIMIT 20, 0

Filename: helpers/general/general_helper.php

Line Number: 15

DB_QUERY_BUILDER

Code:

public function return_sql()
{
  return $this -> _compile_select();
}

All good here. So I have two models. One that handles table of clients and the other to handle table for albums (client have picture albums). They look like this

CLIENT MODEL

Code:

public function list_all($type = false, $status = false, $operation = false, $start = 0, $limit = DEFAULT_PAGINATION_LIMIT)
{
  if( $type ) $this -> where(array('c.type' => $type) );

  if( $status ) $this -> where(array('c.status' => (int) $status) );

  if( $operation ) $this -> where(array('c.operation_status' => $operation) );

  $result = $this -> db
  -> select('c.id, c.name, c.info, c.address, c.email, c.phone_number, c.website, c.logo, c.type, UNIX_TIMESTAMP(c.created_on), c.status,
   c.operation_status, c.twitter, c.youtube, c.facebook, c.slug, c.latitude, c.longtitude, c.vote_up, c.vote_down,
   cy.category, CONCAT(u.first_name, " ", u.last_name) creator')
  -> join('category cy', 'cy.id = c.category_id', 'left')
  -> join('user u', 'u.id = c.created_by_id', 'left')
  -> limit($start, $limit);

  // No caching return raw result
  if( ! DEFAULT_CACHE_STATUS ) return $result -> get('client c') -> result();

  return return_cached($this -> db -> return_sql(), $result, 'result');