Static query and junction table

Yii 2.0 General Discussions
1

I have these tables:

  • Property (property_id)

  • Contact (contact_id)

  • PhoneCall (phonecall_id, contact_id)

  • PropertyPhoneCall (property_id, phonecall_id)

A contact calls and asks for different properties. I want to query all properties "asked" by a concrete contact. Is there a way to do this query statically without use SQL? With hasMany, find, or other magic method…

If I try:




Property::find()->with('propertyPhoneCalls')->where(['contact_id' => $model->id])->all();

I get this error:




SQLSTATE[42S22]: Column not found: 1054 Unknown column 'contact_id' in 'where clause'

The SQL being executed was: SELECT * FROM `property` WHERE `contact_id`=1

so I assume it’s not doing the join with PhoneCall table…

2

as i understand your requirement.Im not geeting this.

Find below what i have assumed,

@ PropertyPhoneCall Model


public function getPropertydata()

    {

        return $this->hasOne(Property::className(), ['property_id' => 'property_id']);

    }

public function getPhonecalldata()

    {

        return $this->hasOne(PhoneCall::className(), ['phonecall_id' => 'phonecall_id']);

    }

@ PropertyPhoneCall Search Model


$query = PropertyPhoneCall ::find();

$query->joinWith(['phonecalldata'])->where(PhoneCall.contact_id => $model->id])->all();
3

OMG!! Thank you!! PropertyPhoneCall aproach is great!!

I’ve done this:




PropertyPhoneCall::find()->with('property')->joinWith(['phoneCall'])->where(['contact_id' => $model->id])->all();

And then I can loop over PropertyPhoneCall and get related Property with a $propertyPhoneCall->property. This way I cannot order by any Property field, but I can reorder via PHP.

Thank you again!

4

vote reply ‘+’ once it solved.

thanx