INNER join using CActiveDataProvider

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…!

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

Thank you so much it helped a lot…!

-Big O

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

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.

sorry for replying late…

could you pls give an example (using code) of what you are tyring to say here…?

-Thanks

<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>