hi i wanna implement a data provider based on a sql statement[color=#545454][font=arial, sans-serif][size=2] (like [/size][/font][/color][font="arial, sans-serif"][size="2"][color="#545454"]csqldataprovider[/color][/size][/font][color=#545454][font=arial, sans-serif][size=2] in yii1). is there any way in yii2[/size][/font][/color]
this is a query that i wanna use with sql dataprovider. i would like to know the way in which i can use left outer join in [size="2"]sql dataprovider(syntax). as i am getting syntax error[/size][size="2"] [/size]
[size="2"]
$query1->select
([
'screen_ticket_booking_history.booking_id As Booking_id',
'screen_ticket_booking_history.booking_date As Booking_date',
'movies.movie_name As Movie',
'theatres.theatre_name As Theatre',
'screen_ticket_booking_history.amount As Amount',
])
->from('screen_ticket_booking_history')
->join('LEFT OUTER JOIN','movies','screen_ticket_booking_history.movie_id=movies.id')
->join('LEFT OUTER JOIN','theatres','screen_ticket_booking_history.theatre_id=theatres.id')
->join('LEFT OUTER JOIN','users_backend','theatres.users_backend_id=users_backend.id')
->where('booking_date >= :start_date', [':start_date' => $year_start])
->andWhere('booking_date <= :end_date', [':end_date' => $year_end])
->andWhere('theatres.users_backend_id = :id', [':id' => $userid]);
'sql' => 'SELECT A1.booking_id As Booking_id,
A1.booking_date As Booking_date,
A2.movie_name As Movie,
A3.theatre_name As Theatre,
A1.amount As Amount
FROM
screen_ticket_booking_history A1
LEFT OUTER JOIN movies A2 ON A1.movie_id=A2.id
LEFT OUTER JOIN theatres A3 ON A1.theatre_id=A3.id
LEFT OUTER JOIN users_backend A4 ON A3.users_backend_id=A4.id
WHERE A1.booking_date >= :start_date
AND A1.booking_date <= :end_date
AND A3.users_backend_id = :id',