Welcome Guest, Not a member yet? Register   Sign In
[solved] Inner Join Active Record
#1

[eluser]a&w[/eluser]
I was trying to leverage Active Record to do a join. Below I'm showing one that works and one that doesn't.

This version works.
Active record query generation:
Code:
$this->db->from('table_1');
$this->db->from('table_2');
$this->db->order_by('table_1.id', 'ASC');
$this->db->limit(25, 0);
$this->db->select('*');
$where = "`table_2`.`id` = `table_1`.`id`";
$this->db->where($where);
$query = $this->db->get();

generates this query:
Code:
SELECT *
FROM (`table_1`, `table_2`)
WHERE `table_2`.`id` = `table_1`.`id`
ORDER BY `table_1`.`id` ASC
LIMIT 25;


And I get results. Ok good. But I had to manually do the "WHERE" clause. So I tried to do generate the same query using a join:


Code:
$this->db->from('table_1');
$this->db->from('table_2');
$this->db->order_by('table_1.id', 'ASC');
$this->db->limit(25, 0);
$this->db->select('*');
$join = 'inner';
$this->db->join('table_2', 'table_2.id = table_1.id', $join);
//$where = "`table_2`.`id` = `table_1`.`id`";
$this->db->where($where);
$query = $this->db->get();

But that generates this query:
Code:
SELECT *
FROM (`table_1`, `table_2`)
INNER JOIN `table_2` ON `table_2`.`id` = `table_1`.`id`
ORDER BY `table_1`.`id` ASC
LIMIT 25;

Which has this result:
Code:
Error Code : 1066
Not unique table/alias: 'table_2'
#2

[eluser]Armchair Samurai[/eluser]
It seems like you're not using the join() function correctly. Try this (hasn't been tested, but should give you what you need):

Code:
$this->db->join('table_2', 'table_1.id = table_2.id', 'inner');
$this->db->order_by('table_1.id', 'asc');
$query = $this->db->get('table_1', 25);
#3

[eluser]a&w[/eluser]
Thanks for your help, yields same result:

Previous query:
Code:
FROM (`table_1`, `table_2`)
INNER JOIN `table_2` ON `table_2`.`id` = `table_1`.`id`
ORDER BY `table_1`.`id` ASC
LIMIT 25;


With your code (same thing pretty much...same result nonetheless):
Code:
<h1>A Database Error Occurred</h1>
<p>Error Number: 1066</p><p>Not unique table/alias: table_2</p>

<p>
SELECT *
FROM (`table_2`, `table_1`)
INNER JOIN `table_2` ON `table_1`.`id` = `table_2`.`id`
ORDER BY `table_1`.`id` asc
LIMIT 25
</p>
#4

[eluser]a&w[/eluser]
How approaching this a different way? Forget CI. IF you had to build the sql query using a join statement what would the sql query look like? Because I was also just testing the query against mysql and getting the same result. So this is more of an issue with the query itself (at least at this point).
#5

[eluser]a&w[/eluser]
Ok, got it resolved, was fairly close actually, but close just doesn't get it done in this case.

The query should have been:
Code:
SELECT *
FROM `table_1`
INNER JOIN `table_2` ON `table_2`.`id` = `table_1`.`id`
ORDER BY `table_1`.`id` ASC
LIMIT 25;

And to get that:
Code:
$this->db->select('*');
$this->db->from('table_1');
$this->db->join('table_2', 'table_2.id = table_1.id', 'inner');
$this->db->order_by('table_1.id', 'asc');
$this->db->limit(25, 0);




Theme © iAndrew 2016 - Forum software by © MyBB