CodeIgniter Forums
join problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: join problem (/thread-9985.html)



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');