letsdecode
(Yii Devils)
October 19, 2010, 11:10pm
1
Hi there,
i am trying to execute a query using CActiveDataProvider and CDBCriteria
but not able to find out how to do it.
i have 3 tables
table A, table B and a relational table C
table A has ta_id as primary key along with other columns
table B has tb_id as primary key along with other columns
table C has ta_id, tb_id as foreign keys along with other columns.
Now i want find all the rows of table B which have a common ta_id in table C.
my sql query for the that is.
SELECT B.ta_id,
B.type,
B.language,
B.user_id
FROM B
INNER JOIN C
ON B.tb_id=C.tb_id
where C.ta_id = 1
ORDER BY B.user_id
Could any one pls help me.
Thanks in advance…!
zaccaria
(Matteo Falsitta)
October 20, 2010, 7:01am
2
$criteria=new CDBcriteria;
$criteria->select='t.ta_id,t.type,t.language,t.user_id';
$criteria->join='INNER JOIN C ON t.tb_id=C.tb_id';
$criteria->condition='C.ta_id = 1';
letsdecode
(Yii Devils)
October 20, 2010, 11:51pm
3
Thank you so much it helped a lot…!
-Big O
letsdecode
(Yii Devils)
October 21, 2010, 5:18am
4
this produces the result i wanted for me.
now i have another table called table D with D.tb_id as a foreign key (which is primary key in table B ).
each row of table B has 0 or more rows associated in table D or we can say
1 or more rows in table D has exactly one corresponding row in table B.
Now i want to list my each row of table B with all the associated rows of table D.
so it should look like this.
first row of table B
first corresponding row of table D
second corresponding row of table D
…
…
second row of table B
first corresponding row of table D
second corresponding row of table D
…
…
so in a way i am mixing the contents of 2 tables in CListView.
Please tell me how to achieve this functionality.
Thanks
zaccaria
(Matteo Falsitta)
October 21, 2010, 6:16am
5
You cannot achive using CGridView, you have to write your own table.
Write a standard html table, and display the row if table B in a foreach statment (You can use CListView instead of the foreach).
In this foreach you can get the records of table D with the one to many relationship, and do a second one foreach (You can always use CListView instead of the second foreach).
There are no standard widget for this kind of functionality.
letsdecode
(Yii Devils)
November 1, 2010, 5:57am
6
sorry for replying late…
could you pls give an example (using code) of what you are tyring to say here…?
-Thanks
zaccaria
(Matteo Falsitta)
November 1, 2010, 7:10am
7
<table>
<?php foreach ($recordsInB as $<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />: ?>
<tr>
<td><?php $b->field?><td>
[...]
<tr>
<?php foreach ($b->relatedRecordsInD) as $d:?>
<tr>
<td><?php $d->field?><td>
[...]
<tr>
<endforeach;>
<?php endforeach;?>
</table>