Yii Framework Forum: INNER join using CActiveDataProvider - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

INNER join using CActiveDataProvider Rate Topic: -----

#1 User is offline   Big O 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 07-October 10

Posted 19 October 2010 - 06:10 PM

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...!
:)
0

#2 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 20 October 2010 - 02:01 AM

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



2

#3 User is offline   Big O 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 07-October 10

Posted 20 October 2010 - 06:51 PM

Thank you so much it helped a lot...! :)

-Big O
0

#4 User is offline   Big O 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 07-October 10

Posted 21 October 2010 - 12:18 AM

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
0

#5 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 21 October 2010 - 01:16 AM

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

#6 User is offline   Big O 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 07-October 10

Posted 01 November 2010 - 12:57 AM

sorry for replying late..

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

-Thanks
0

#7 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 01 November 2010 - 02:10 AM

<table>
<?php foreach ($recordsInB as $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>

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users