Welcome Guest, Not a member yet? Register   Sign In
join problem
#1

[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…
#2

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

[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.
#4

[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.
#5

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

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

Uops... sorry!
#7

[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.
#8

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




Theme © iAndrew 2016 - Forum software by © MyBB