Strange Issue Duplicated DB Queries

Strange Issue Duplicated DB Queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Strange Issue Duplicated DB Queries (/showthread.php?tid=47106)

Strange Issue Duplicated DB Queries - El Forum - 11-26-2011

[eluser]rockoo[/eluser]
Hi,

I need to start caching my DB queries due to high traffic volume. One of the application is using CI 1.7 as is being ported to 2.1 (not sure if this might be a problem).

I've used two different cache libraries Memcached and File cache however both produce this strange result of duplicate query throwing Not unique/alias DB error.

This is my original query

Code:
$q = $this->db

  ->select('a.id, a.title album, e.id eventID, UNIX_TIMESTAMP(a.date) date, c.name client, c1.name venue, cy.category')

  ->from('album a')

  ->join('event e', 'e.id = a.event_id')

  ->join('client c', 'c.id = e.client_id')

  ->join('client c1', 'c1.id = e.hosted_by_client_id', 'left')

  ->join('category cy', 'cy.id = a.category_id')

  ->where(array('a.status' => 1, 'a.date <' => date('Y', time())))

  ->order_by('a.id', 'random');

resulting in this error

Quote:<h1>A Database Error Occurred</h1>
<p>Error Number: 1066</p><p>Not unique table/alias: 'a'</p>

And the outputed query is this

Code:
<p>SELECT `a`.`id`, `a`.`title` album, `e`.`id` eventID, UNIX_TIMESTAMP(a.date) date, `c`.`name` client, `c1`.`name` venue, `cy`.`category`, `a`.`id`, `a`.`title` album, `e`.`id` eventID, UNIX_TIMESTAMP(a.date) date, `c`.`name` client, `c1`.`name` venue, `cy`.`category`

FROM (`album` a, `album` a)

JOIN `event` e ON `e`.`id` = `a`.`event_id`

JOIN `client` c ON `c`.`id` = `e`.`client_id`

LEFT JOIN `client` c1 ON `c1`.`id` = `e`.`hosted_by_client_id`

JOIN `category` cy ON `cy`.`id` = `a`.`category_id`

JOIN `event` e ON `e`.`id` = `a`.`event_id`

JOIN `client` c ON `c`.`id` = `e`.`client_id`

LEFT JOIN `client` c1 ON `c1`.`id` = `e`.`hosted_by_client_id`

JOIN `category` cy ON `cy`.`id` = `a`.`category_id`

WHERE `cy`.`category` = 'club'

AND `a`.`status` = 1

AND `a`.`date` < '2011'

AND `cy`.`category` = 'birthdays'

AND `a`.`status` = 1

AND `a`.`date` < '2011'

ORDER BY  RAND(),  RAND()</p>

I have no idea why this happens... And its only happening when trying to use any caching library. The queries work with no problem otherwise...

Can anybody please help me out

Thank you

Strange Issue Duplicated DB Queries - El Forum - 02-22-2012

[eluser]ludo31[/eluser]
I have also the same problem

Code:
$this->db->select('id');

            $this->db->from('chaussure');

           $this->db->order_by("id", "desc");

            $this->db->limit(1);

            $query = $this->db->get("chaussure");

and I change $query = $this->db->get("chaussure"); by $query = $this->db->get();

and it works

Strange Issue Duplicated DB Queries - El Forum - 02-23-2012

[eluser]smilie[/eluser]
->from('album as a')

is correct syntax, try that first;

Cheers,
Smilie