I have the following SQL query to create my grid.

$g->select_command = “SELECT mi.id,
concat(mi.lego_id,’: ‘,`p`.`name`) part_id_name,
mi.lego_id,
`p`.`name`,
`pc`.`name` pcname,
mi.quantity,
mi.color_id,
`c`.`name` colorname,
c.rgb,
mi.location,
mi.container,
mi.notes
FROM my_inventory mi
JOIN parts p
ON mi.lego_id = p.part_num
INNER JOIN colors c
ON mi.color_id = c.id
INNER JOIN part_categories pc
ON p.part_cat_id = pc.id
WHERE mi.type = ‘Part'”;

The Category column is configured as follows:

$col = array();
$col[“title”] = “Category”;
$col[“name”] = “pcname”;
$col[“hidden”] = false;
$col[“export”] = true;
$col[“search”] = true;
$col[“editable”] = false; // this column is not editable
$cols[] = $col;

The grid displays fine but search on the category column displays this error.

Couldn’t execute query. Unknown column ‘pcname’ in ‘where clause’ – SELECT count(*) as c FROM (SELECT mi.id, concat(mi.lego_id,’: ‘,`p`.`name`) part_id_name, mi.lego_id, `p`.`name`, `pc`.`name` pcname, mi.quantity, mi.color_id, `c`.`name` colorname, c.rgb, mi.location, mi.container, mi.notes FROM my_inventory mi JOIN parts p ON mi.lego_id = p.part_num INNER JOIN colors c ON mi.color_id = c.id INNER JOIN part_categories pc ON p.part_cat_id = pc.id WHERE mi.type = ‘Part’ AND ( `pcname` LIKE ‘%bricks%’ )) pg_tmp

I suspect this is because there are 3 tables, each with a ‘name’ column. Each name column is aliased but the search query does not seem to properly recognize the alias. I have also tried the format: “field_name AS friendly_field_name” but it makes no difference. The Select query works to display the grid but the Search query (which Grid4PHP creates), always fails.

Any suggestions?