Join question using CDbCriteria

Yii 1.1 General Discussion
1

Further to my post regarding a where clause, I am now trying to do the same using a join.

I have a table named Client that can have many corresponding records in Invoice. I am trying to apply this relationship in the Client::search() method with the following code:


		$criteria=new CDbCriteria;


		$criteria->join='LEFT JOIN Client ON Client.id=Invoice.clientId';

		$criteria->condition='Client.businessId='. Yii::app()->userInfo->business;

However, this is returning the error: Column not found: 1054 Unknown column ‘Invoice.clientId’ in ‘on clause’

But this column does exist and I get the result I expect if I manually write the following into MySQL directly:




SELECT Invoice.id,clientId,Invoice.active,Client.id AS client_id,Client.businessId FROM Invoice 

LEFT JOIN Client ON Client.id=Invoice.clientId 

WHERE Client.businessId=1;

It seems that even though the code is running within the Invoice model, the table Invoice is unknown. Do I need to specify the table some other way?

Thanks,

Dubby.

2 

		

$criteria=new CDbCriteria;

$criteria->alias = 'Invoice';

$criteria->join='LEFT JOIN Client ON Client.id=Invoice.clientId';

$criteria->condition='Client.businessId='. Yii::app()->userInfo->business;
3

Thanks Jaz Manister,

That did it.

4

Hi again,

Although the join is now working as I hoped, it has created a new problem.

When viewing Invoice in admin, the id being passed is the Client id (Client.id) rather than the Invoice id (Invoice.id).

This means that the links on the page to update and delete have the wrong IDs in the URI.

Any ideas on this issue?

Thanks,

Dubby.

5

Does no one have any suggestions on the issue quoted here?

6

try doing an alias for the field name in conflict, or change the field name in the db

7

Pleae help me i want to make this type of query "SELECT * FROM table1 a, table2 m WHERE a.colid=1 and m.colid=1 order by a.date desc" in Join using CDbCriteria in search function in model

8

Hi Shafqat, welcome to the forum.

We usually deal this kind of issue with "Relational Active Record".

http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Probably you can establish a BELONGS_TO, HAS_ONE or HAS_MANY relation between table1 and table2 regarding the colid.

9

actually i need to combine two different tables data in one view gridview in admin can you please help me out…

for example table1 has two child table2 and table 3 on the criteria of table1 id search record from both tables

10

Could you elaborate your case a little more, please?

What are the db schema and the AR models for table1, table2 and table3? Have you already established the relations among those AR models?

11

ya everything is working in normal way with relationship but i need to combine two tables data fetching data on criteria of table 1 view in widget in view of admin

12

Well, I’m sorry but I don’t understand what the problem is.

State your issue with a specific case, please. :)

13

can we have skype chat mine is shafqat.jan.siddiqui i will explain it more there

14

No, definitely not, at least with me.

If you ever want that kind of "service", you have to be willing to pay for it.

IMHO, this forum is under the spirit of open source software. You may get what you want for free, if you are willing to share your knowledge and experience. You have to be honest and frank, sometimes exposing your stupid broken codes. If you are too proud to do that, you may want to hire some private teacher. :)