Welcome Guest, Not a member yet? Register   Sign In
natural join with CI Active Record
#1

[eluser]inari[/eluser]
How can I use natural join with Codeigniter Active Record?

Something like:
Code:
SELECT *
FROM   employee NATURAL JOIN department


C/P:

Quote:The NATURAL JOIN joins two tables which contain a column or multiple columns with the same name and data-type.

The following query joins the customer table to the invoice table with a natural join, the natural join utilizes the customer_id that is present on both the customer table and the invoice table. It returns the customer and invoice data for invoices that have not had any payments made on them.

Code:
SELECT customer_id, invoice_id, customer.first_name, customer.last_name
FROM CUSTOMER
NATURAL JOIN invoice
WHERE invoice.amount_paid = 0;

Thanks for any answer
#2

[eluser]xwero[/eluser]
Code:
$this->db->from('employee NATURAL JOIN department');
should work
#3

[eluser]inari[/eluser]
How can be used with get_where() method?
#4

[eluser]xwero[/eluser]
you could try it in the first parameter but i'm not sure it will work.
#5

[eluser]roryokane[/eluser]
I'm afraid this doesn't work for me. With the code
Code:
$query = $this->db->select('title, description, length(audio), realname, creationdate, podid')->from('podcasts NATURAL JOIN users')->where('userid', $this->uri->rsegment(4) )->get();
I get this error:


Quote:A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN` users) WHERE `userid` = '4'' at line 2

SELECT `title`, `description`, length(`audio`), `realname`, `creationdate`, `podid` FROM (`podcasts` NATURAL `JOIN` users) WHERE `userid` = '4'


As you can see, the "JOIN" was quoted and "users" was not. Maybe an update of CodeIgniter stopped this from working.

What I'd really like to do, and wish CodeIgniter could do, is say something like
Code:
$this->db->from('podcasts')->join('users', 'NATURAL')
or perhaps even
Code:
$this->db->from('podcasts')->join('users')

I'm having trouble working around this problem without just using $this->db->query(). When I change the middle of the query to
Code:
->from('podcasts')->join('users', 'users.userid = podcasts.userid')
, I get this error:


Quote:A Database Error Occurred

Error Number: 1052

Column 'userid' in where clause is ambiguous

SELECT `title`, `description`, length(`audio`), `realname`, `creationdate`, `podid` FROM (`podcasts`) JOIN `users` ON users.userid = podcasts.userid WHERE `userid` = '4'
, even though I made sure to specify the column names (though stupid MySQL 5.0.41 should be able to realize that the result will be the same after the join).

Can anyone help with either of these problems, or tell me how I would officially request or write myself (for the official release) the modification to join()? (I'm not sure if a bug report is appropriate.)
#6

[eluser]maesk[/eluser]
How about this:

Code:
$this->db->select('userid, title, description, length(audio), realname, creationdate, podid');
$this->db->from('podcasts');
$this->db->join('users', 'users.userid = podcasts.userid');
$this->db->where('users.userid', $this->uri->rsegment(4));
$query = $this->db->get();

Does this work?
#7

[eluser]Sumon[/eluser]
[quote author="inari" date="1210996091"]How can I use natural join with Codeigniter Active Record?

Code:
SELECT customer_id, invoice_id, customer.first_name, customer.last_name
FROM CUSTOMER
NATURAL JOIN invoice
WHERE invoice.amount_paid = 0;
[/quote]

Try this

Code:
$query = $this->db->select('I.customer_id, I.invoice_id, C.customer.first_name, C.customer.last_name')
    ->from('CUSTOMER C, invoice I')
    ->where('I.amount_paid', 0)
    ->get();

return $query;

/*    view file
foreach ($query->result() as $row)
{
    echo $row->title;
}

*/
#8

[eluser]roryokane[/eluser]
Thanks, maesk. Your code didn't quite work verbatim, but all I needed to do to it is remove the userid in the the the select clause, because I don't need it in the result, and I got an error saying that userid was ambiguous, too. Here is my final code, which works:

Code:
$this->db->select('title, description, length(audio), realname, creationdate, podid');
$this->db->from('podcasts');
$this->db->join('users', 'users.userid = podcasts.userid');
$this->db->where('users.userid', $this->uri->rsegment(4));
$query = $this->db->get();

It really shouldn't make a difference whether I do it in one statement or many with PHP 5.2.5, so I guess this is a bug in CodeIgniter.
#9

[eluser]maesk[/eluser]
The select clause, when selecting also the userid from podcasts, probably should have been:

Code:
$this->db->select('podcasts.userid, title, description, length(audio), realname, creationdate, podid');

instead of

Code:
$this->db->select('userid, title, description, length(audio), realname, creationdate, podid');

and yes, it should work in one statement, as long as nothing is ambiguous. I couldn't test it but I'm glad you could make it work.




Theme © iAndrew 2016 - Forum software by © MyBB