Using conditions inside Sql Data provider

Question

I want to use conditions inside SQL data provider query as shown below

 $count         =   Yii::$app->db->createCommand('
                    SELECT COUNT(*) FROM screen_ticket_booking WHERE id!=:id
                    ', [':id' => 0])->queryScalar(); 
                    $dataProvider = new SqlDataProvider([
                    'sql' =>    'SELECT A1.booking_id As Booking_id,
                                A3.movie_name As Movie,
                                A4.theatre_name As Theatre,
                                A1.show_date As Show_date,
                                A2.start_time As Time,
                                A5.booking_date As Booking_date,
                                A1.id As Id

                                FROM 
                                screen_ticket_booking A1

                                LEFT OUTER JOIN screen_show_times A2 ON A1.show_time_id=A2.id
                                LEFT OUTER JOIN movies A3 ON A1.movie_id=A3.id
                                LEFT OUTER JOIN theatres A4 ON A1.theatre_id=A4.id
                                LEFT OUTER JOIN screen_ticket_booking_history A5 ON A1.booking_id=A5.booking_id

                                WHERE A1.id !=0 ',
                    if( $userid != '1')
                    {
                    $sql .= ' AND A4.users_backend_id = {$userid}';
                    }   
                    'totalCount' => $count,
                    ]);

i am getting an error " syntax error, unexpected 'if' (T_IF), expecting ']' " . is it not possible to use a condition inside the SQL data provider?

Tony · Accepted Answer · 2015-06-26 06:26:52Z

If you want to use conditions to build your query, you need to do it outside of dataprovider. Like this:

$sql = 'SELECT A1.booking_id As Booking_id,
    A3.movie_name As Movie,
    A4.theatre_name As Theatre,
    A1.show_date As Show_date,
    A2.start_time As Time,
    A5.booking_date As Booking_date,
    A1.id As Id

    FROM 
    screen_ticket_booking A1

    LEFT OUTER JOIN screen_show_times A2 ON A1.show_time_id=A2.id
    LEFT OUTER JOIN movies A3 ON A1.movie_id=A3.id
    LEFT OUTER JOIN theatres A4 ON A1.theatre_id=A4.id
    LEFT OUTER JOIN screen_ticket_booking_history A5 ON A1.booking_id=A5.booking_id

    WHERE A1.id !=0 '

if( $userid != '1')
{
    $sql .= ' AND A4.users_backend_id = {$userid}';
}

And then $dataProvider = new SqlDataProvider([ 'sql' => $sql, 'totalCount' => $count, ]);

Also consider use of query builder which provides more flexible and independent of DBMS way of creating SELECT SQL statements

Collectives™ on Stack Overflow

Using conditions inside Sql Data provider

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related