I’m working on a Yii2 project with the following MySQL DB structure:
Order: -id, -client_id, -date,
Client: id, name, area_id,
Area: id, name,
How to get all orders between two dates for each Area With model find.
I’m working on a Yii2 project with the following MySQL DB structure:
Order: -id, -client_id, -date,
Client: id, name, area_id,
Area: id, name,
How to get all orders between two dates for each Area With model find.
Just as blue as that?
Please make it easier for somebody to help you than just throwing out bunch of abstract statements and let the helper to figure out. Show what you have done. Else, just read relations in active record in Guide
After some research i tried this:
Order::find()
->innerJoin(Client::tableName(),’client
.id = order
.client_id
’)
->innerJoin(Area::tableName().’ as center’,’client
.area_id = center
.id’)
->innerJoin(Area::tableName().’ as governorate’,’center
.parent_id = governorate
.id’)
->where([‘IN’, ‘status’,[ORDER::STATUS_ACTIVE,Order::STATUS_DONE]])
->andWhere([‘between’,‘duedate’,$start_date,$end_date])
->andWhere([‘governorate.id’=>$area_id])
->all();
It returns the data that I need.
FYI, you can further simplify this with a joinWith(), so long as you have the hasOne() and hasMany() properly setup in your classes. Likely, you would get:
$thisOrder = Order::find()->joinWith('Client')->joinWith('Area')->...->where(...)
joinWith() will take care of managing the INNER JOIN ON for you.