Column Search SQL Error

QuestionsColumn Search SQL Error
Mike Fischer asked 11 months ago

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?

1 Answers
Abu Ghufran Staff answered 11 months ago

In case of field name ambiguity b/w tables, set as table.fieldname in dbname property.

You might need to use:

$col[“dbname”] = “pc.name”;

Ref: https://www.gridphp.com/docs/column-options/#db-field-name-or-alias-in-sql-query-table

 

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Mike Fischer replied 11 months ago

That worked perfectly. Thank you so much!

Your Answer

0 + 3 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?