join problem - Printable Version

join problem - 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: join problem (/showthread.php?tid=9985)

join problem - El Forum - 07-15-2008

[eluser]mandrake[/eluser]
Hi,
excuse me for my english.

I use Activerecord in my model

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

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

$this->db->join('clienti_authors_related', 'clienti_authors_related.author_id = authors.author_id AND clienti_authors_related.cliente_id = 1');

Result: 0 records.

I try the query result (last_query()) in phpmyadmin:

Code:
SELECT *

FROM authors

JOIN clienti_authors_related ON clienti_authors_related.author_id = authors.author_id AND clienti_authors_related.cliente_id = 1

Result: 2 records.

why?

Is this a bug?

Thanks…

join problem - El Forum - 03-06-2012

[eluser]Josepzin[/eluser]
I think i have the some problem...

Code:
$this->db->join('catalogs_arranges', 'catalogs_arranges.key=nexts AND catalogs_arranges.catalog_id=catalogs.id', 'LEFT');

create this:

Code:
LEFT JOIN `catalogs_arranges` ON `catalogs_arranges`.`key`=`nexts` AND catalogs_arranges.catalog_id=catalogs.id

The "nexts" string is "protected" by CI... What is the solution??

join problem - El Forum - 03-06-2012

[eluser]Matalina[/eluser]
add 'left' as a third parameter to your join in active record and see if you get the same results.

I don't expect you will since both CI and mysql default to Inner join.

You also don't want to forget

Code:
$this->db->get();

$this->db->from('table_name') only specifies the table it doesn't actually run the query.

join problem - El Forum - 03-06-2012

[eluser]Josepzin[/eluser]
Thanks Matalina, i think you do not understand my question Tongue

I replace the JOIN by sub SELECT:

Code:
$this->db->select('(SELECT arrange FROM catalogs_arranges WHERE catalogs_arranges.key="nexts" AND catalogs_arranges.catalog_id=catalogs.id) AS arrange');

It works, but i think JOIN may be is better.

join problem - El Forum - 03-06-2012

[eluser]Matalina[/eluser]
My reply was in reference to the original poster.

join problem - El Forum - 03-06-2012

[eluser]Josepzin[/eluser]
[quote author="Matalina" date="1331073441"]My reply was in reference to the original poster.[/quote]

Uops... sorry!

join problem - El Forum - 03-07-2012

[eluser]aquary[/eluser]
Josepzin, in your case, it's not about CI protect the "nexts" string, but because it IS a string. You'll need to put it in a string anyway.

Also, try switching the condition of joining. I cannot remember why but I did got some problem like that before, somehow, I realized that I have to put the "relation" part (catalogs_arranges.catalog_id=catalogs.id) before the "condition" part (catalogs_arranges.key="nexts").....

Although "join" could be replaced with "where", the usage are not the same :3.

join problem - El Forum - 03-07-2012

[eluser]Josepzin[/eluser]
Thnaks aquary!!!! Smile

It works, your two options are good!

#1 JOIN + WHERE

Code:
$this->db->where('catalogs_arranges.key', 'nexts');

$this->db->join('catalogs_arranges', 'catalogs_arranges.table_id=catalogs.id', 'LEFT');

#2 ONLY JOIN

Code:
$this->db->join('catalogs_arranges', 'catalogs_arranges.table_id=catalogs.id AND catalogs_arranges.key="nexts"', 'LEFT');