Filter on HAS_MANY relation without together?

Yii 1.1 General Discussion
1

I’m having trouble filtering a HAS_MANY relation in CGridView.

I have 3 tables:

cost_land:

id

…other fields…

cost_land_item_group:

id

cost_land_id (references cost_land.id)

cost_vendor_id

…other fields…

cost_land_item:

id

cost_land_item_group_id (references cost_land_item_group.id)

item_number

…other fields…

In the CostLand model, I have item_number and vendor_id attributes to hold the value entered into the filters.

In my CGridView, I list all cost_lands. I also implode all of each cost_land’s cost_land_item_groups’ cost_vendor_ids and all of each cost_land’s cost_land_item_groups’ cost_land_items’ item_numbers. For example:




 ------------------------------

| ID | ... | Item #s | Vendors |

|----|-----|---------|---------|

| 1  | ... | 111BLK  | 1234    |

|    | ... | 111RED  | 4321    |

|    | ... | 111BLU  |         |

|    | ... | 111WHT  |         |

|    |     | 333BLU  |         |

|----|-----|---------|---------|

| 2  | ... | 444BLK  | 1235    |

|    | ... | 444RED  |         |

|----|-----|---------|---------|

| 3  | ... | 123BLK  | 1235    |

|    | ... | 223RED  | 4321    |

|    | ... | 223GRN  |         |

|    | ... | 223WHT  |         |

|    | ... | 223BLU  |         |

 ------------------------------

I have a filter for each column, but when I search in Item #s or Vendors, I get a message like the following:




CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'costLandItems.item_number' in 'where clause'

Here are my relations in CostLand:




'costLandItemGroups' => array(self::HAS_MANY, 'CostLandItemGroup', 'cost_land_id'),

Here are my relations in CostLandItemGroup:




'costLandItems' => array(self::HAS_MANY, 'CostLandItem', 'cost_land_item_group_id'),

'vendor' => array(self::BELONGS_TO, 'CostVendor', 'cost_vendor_id'),

Here is search() in CostLand model:




    public function search() {

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

        // should not be searched.


        $criteria = new CDbCriteria;


        $criteria->compare('t.id', $this->id, true);

        $criteria->compare('costLandItems.item_number', $this->item_number, true);

        $criteria->compare('costLandItemGroups.cost_vendor_id', $this->vendor_id);


        $criteria->with = array('costLandItemGroups', 'costLandItemGroups.costLandItems');

        //Messes paging up

        //$criteria->together = true;


        return new CActiveDataProvider(get_class($this), array(

            'criteria' => $criteria,

        ));

    }

I have tried setting $criteria->together = true, which works for filtering, but that messes the pagination up because it pulls them all at the same time. What happens when I have $criteria->together = true is that it goes by the number of cost_land_items or cost_land_item_groups.

I looked at the query with $criteria->together = false, and costLandItemGroups and costLandItemGroups.costLandItems relations are not present in the query executed during the filtering, so that’s why the filtering columns cannot be found. The weird thing is that the relations work fine with just displaying the data.

How do I get the relations loaded during the query so that I can search in them? Sorry for such a long post. Thanks for the help!

I also wanted to add that when CGridView executed the query to get the count of records that fit the filter criteria, it correctly joins the relations and gets the correct count.

2

Check this similar topic if it can help you - http://www.yiiframework.com/forum/index.php?/topic/14390-

3

Thanks for the reply/help!

I have exactly what you told Ocean Wind to do in the link you provided. I have the following:

  1. public attributes ($item_number, $vendor_id) in the model to hold the filter values

  2. $criteria->with = array(‘costLandItemGroups’, ‘costLandItemGroups.costLandItems’)

  3. $criteria->compare to search for filter values

My issue is the same as this issue. The solution Qiang provided is to use $criteria->together = true. Im my case, since the relationship is truly a HAS_MANY and not a HAS_ONE, I can’t do that because limit and offset will not work for pagination.

I solved the problem with a semi-dirty solution. What I do is if there is a value in $this->item_number, I do a separate query in search() to first get items with item_numbers like $this->item_number. I then look at each item’s group and get the group’s cost_land_id:




        if ($this->item_number !== null) {

            $ids = array();

            $relationsCriteria = new CDbCriteria;

            $relationsCriteria->with = array('costLandItemGroup');

            $relationsCriteria->addSearchCondition('item_number', $this->item_number);

            foreach (CostLandItem::model()->findAll($relationsCriteria) as $item) {

                $ids[$item->costLandItemGroup->cost_land_id] = $item->costLandItemGroup->cost_land_id;

            }

            $criteria->addInCondition('t.id', $ids);

        }

and for po_number and vendor_id, I just search the groups and get cost_land_ids:




        if ($this->vendor_id !== null || $this->po_number !== null) {

            $relationsCriteria = new CDbCriteria;

            $relationsCriteria->distinct = true;

            $relationsCriteria->select = 'cost_land_id';

            $relationsCriteria->index = 'cost_land_id';

            $relationsCriteria->compare('cost_vendor_id', $this->vendor_id);

            $relationsCriteria->compare('po_number', $this->po_number);

            $ids = array_keys(CostLandItemGroup::model()->findAll($relationsCriteria));

            $criteria->addInCondition('t.id', $ids);

        }

If anyone can think of a cleaner solution, please let me know. My solution uses an extra query or two, and I’ll have to do this for every HAS_MANY relation in the future, which isn’t a big deal I guess.