[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
Quote: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');
[eluser]rockoo[/eluser]
ALBUM MODEL
Code: public function list_all($clientID = false, $eventID = false, $category = false, $status = false, $featured = false, $start = 0, $limit = DEFAULT_PAGINATION_LIMIT)
{
if( (int) $clientID ) $this -> db -> where(array('c.id' => $clientID) );
if( (int) $status ) $this -> db -> where(array('a.status' => (int) $status) );
if( (bool) $featured ) $this -> db -> where(array('a.is_featured' => 1) );
$result = $this -> db
-> 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', false)
-> join('category cy', 'cy.id = a.category_id', 'left')
-> join('classification cn', 'cn.id = a.classification_id', 'left')
-> join('event e', 'e.id = a.event_id')
-> join('classification cnn', 'cnn.id = e.classification_id', 'left')
-> join('client c', 'c.id = e.client_id')
-> join('client ct', 'ct.id = e.hosted_by_client_id', 'left')
-> join('photographer pr', 'pr.id = a.photographer_id')
-> join('user u', 'u.id = pr.user_id')
-> limit($limit, $start)
-> order_by('a.date', 'desc')
-> order_by('a.sort', 'asc')
-> order_by('a.created_on', 'desc');
// No caching return raw result
if( ! DEFAULT_CACHE_STATUS ) return $result -> get('album a') -> result();
return return_cached($this -> db -> return_sql(), $result, 'result');
}
[eluser]Aken[/eluser]
If you don't run one of the methods that processes the query (get(), get_where(), count_all_results(), etc.), then all of your DB parameters like where() and like() and everything else will just accumulate.
Without knowing how all your stuff is set up, I can't give a perfect solution. The active record class has a protected method _reset_select() that would do what you need, but it's protected so you can't call it directly. You'll have to modify something to get it working perfectly.
[eluser]rockoo[/eluser]
Bro I love you right now! I scanned file for clear method and I couldn't find it :/ Thank you so much!!!!
|
