Welcome Guest, Not a member yet? Register   Sign In
Strange Issue Duplicated DB Queries
#1

[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

#2

[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
#3

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

is correct syntax, try that first;

Cheers,
Smilie




Theme © iAndrew 2016 - Forum software by © MyBB