joining two tables

Yii 1.1 General Discussion
1

[font="Verdana"]Hi,

I want to join two tables in mysql: tickets and ticket_periods. I want to join the two tables with ticket_number field.

For the first when creating it I’ve set up a FK:

FOREIGN KEY tk_num(ticket_number) REFERENCES ticket_periods(ticket_number)

In the Tickets model i have relation:

‘ticketNumber0’=>array(self::BELONGS_TO, ‘TicketPeriods’, ‘ticket_number’),

,and in the TicketPeriods model i have relation:

‘tickets’=>array(self::HAS_MANY, ‘Ticket’, ‘ticket_number’),

In the TicketController i am trying this:

	$criteria=new CDbCriteria;


	$criteria->with=array('ticketNumber0',);


	$criteria->condition = "t.id = $id";


	


	$this->render('view',array(


		'ticket_periods1'=>Tickets::model()->findAll($criteria),


	));

But, when i am trying to create one ticket i get this error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1452 Cannot add or update a child row: a foreign key constraint fails

(ticket.tickets, CONSTRAINT tk_num FOREIGN KEY (ticket_number) REFERENCES ticket_periods (ticket_number))

, that would mean i have no records in the referenced table and i have "childs" without "parents".

if i manually populate the second table i would get the proper sql but i don’t have the columns from the referenced table displayed!

Can someone please give me some tips of how to join two tables in mysql?

[/font]

2

Welcome to the forum!

For display the field of the related table you have to use $model->ticketNumber0->number for example.

3

indeed i get the columns for the 2nd table like that, but i am only getting the first record not the many.

I wanted to loop through the ‘ticket_periods1’=>Tickets::model()->findAll($criteria) and also tried with CActiveDataProvider but only getting the first records of the join statement.

and also still haven’t figured out the 1st error when creating a ticket, that is mysql error, i guess i will have to change the whole approach of doing this join.

4

the problem is : you calling ticketNumber0 wich have relation belongs to, and thats whay you get one result, you have to call




$criteria=new CDbCriteria;

$criteria->with=array('tickets');

$criteria->condition = "t.id = $id";


$this->render('view',array(

'ticket_periods1'=>Tickets::model()->findAll($criteria),

));


//in model

,and in the Tickets  model setup relation:

'tickets'=>array(self::HAS_MANY, 'TicketPeriods', 'ticket_number'),

and you dont need this relation




In the Tickets model i have relation:

'ticketNumber0'=>array(self::BELONGS_TO, 'TicketPeriods', 'ticket_number'),
5

I think that maybe you set the relations in the wrong way.

The foreign key field should lie in the table child (the ‘many’ table) not in the parent one (the ‘one’ table)

6

Hi, Thank You for your replies,

I wouldn’t have started a thread if i didn’t tried both ways of self::BELONGS_TO and self:HAS_MANY if the two models.

it means i was doing it right the first time as you pointed out, i was juts missing the $model->ticketNumber0->number to display columns from the referenced table.

Anyway, i still haven’t fully implemented this, because meanwhile i’ve done it with simple join like:

$criteria->join =‘LEFT JOIN ticket_periods ON ticket_periods.ticket_number = t.ticket_number’;

with no need of creating foreign keys in db. and it works just fine, and i got one sql in the log.

so my question is: why would i do the FK solution at all? this way looks simpler.

any comments!

7

Hello,
Copy my code for example:

$criteria = new CDbCriteria();
$criteria->select = ‘t.codusu, t.codasi, texcor, texlar, fechor’;
$criteria->join = 'INNER JOIN asistencia AS asi ON asi.codasi = t.codasi ’
. 'JOIN usuario AS usu ON usu.codusu = t.codusu ’
. 'JOIN municipio AS mun ON mun.codmun = asi.codmun ’
. ‘JOIN departamento AS dep ON dep.coddep = mun.coddep’;