Problem with forming querys

Yii 1.1 General Discussion
1

Having a problem getting a handle on Yii and creating appropriate queries from my database

Query I would like to run:


SELECT 

   *

FROM

    `form_field` `t`

        LEFT JOIN

    form_field_map ffm ON t.field_id = ffm.field_id

WHERE

    ((ffm.agency_id IS NULL)

        AND (ffm.form_id = '258'))

        AND (t.is_deleted = '0')

ORDER BY sort_order , t.field_id

Query I’m getting


SELECT 

   *

FROM

    `form_field` `t`

        LEFT JOIN

    form_field_map ffm ON t.field_id = ffm.field_id

WHERE

    ((t.agency_id IS NULL)

        AND (t.form_id = '258'))

        AND (t.is_deleted = '0')

ORDER BY sort_order , t.field_id

Here is the my search function from my Model, MFormField


    public function search()

    {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.

        $criteria = new CDbCriteria;

        $criteria->compare('field_id', $this->field_id);

        $criteria->mergeWith(array(

         'join'=>'LEFT JOIN form_field_map ffm ON t.field_id = ffm.field_id'));

        $criteria->order = 'sort_order, t.field_id';

        

        return new ActiveDataProvider($this, array(

            'criteria'   => $criteria,

            'pagination' => false,

            'sort'       => false

        ));

    }

A little of backstory the data was in a folder/hierarchy layout

form.form_id,

form.agency_id,

form.name,

form.description,

form.is_active,

form.is_deleted,

form.is_non_dated,

form.sort_order,

…

form_section.form_section_id,

form_section.agency_id,

form_section.form_id,

form_section.name,

form_section.sort_order,

form_section.is_deleted,

…

form_field.field_id,

form_field.agency_id,

form_field.form_id,

form_field.form_section_id,

form_field.name,

form_field.is_required,

form_field.is_active,

form_field.sort_order,

…

Now trying to move to relational data layout by using a map with the following fields

form_field_map.form_field_map_id,

form_field_map.agency_id,

form_field_map.form_id,

form_field_map.form_section_id,

form_field_map.field_id,

form_field_map.sort_order

Any help with this would be appreciated, still learning so be kind :D

Thanks

2

Inside your form_field table model, add the following code:




$criteria=new CDbCriteria;

$criteria->select = '*'

$criteria->join = 'LEFT JOIN form_field_map ffm ON t.field_id = ffm.field_id';

$criteria->condition = "ffm.agency_id IS NULL AND ffm.form_id = '258' AND  t.is_deleted = '0'"

$criteria->order ='sort_order , t.field_id';

To be more general so you can pass value to is_deleted and form_id use this:




$criteria=new CDbCriteria;

$criteria->select = '*'

$criteria->join = 'LEFT JOIN form_field_map ffm ON t.field_id = ffm.field_id';

$criteria->condition = "ffm.agency_id IS NULL AND ffm.form_id = :formId AND  t.is_deleted = :isDeleted"

$criteria->params = array(':formId'=>'258', ':isDeleted'=>0);//Instead 258 and 0 you can put a variable

$criteria->order ='sort_order , t.field_id';