Custom SQL duplicate column names. WHERE clause not working

QuestionsCustom SQL duplicate column names. WHERE clause not working
Vygandas Pigaga asked 3 months ago

Hello,

I have to use custom SQL:

$g->select_command = “SELECT gaminiai.id as id, gaminiai.code as gaminiai_code, gaminiai.name as gaminiai_name, gaminiai.ean as gaminiai_ean, gaminiai.ar_batt_charger as gaminiai_ar_batt_charger, battery.code as battery_code, charger.code as charger_code
FROM gaminiai
LEFT JOIN battery on battery.id = gaminiai.battery_id
LEFT JOIN charger on charger.id = gaminiai.charger_id”;

Displays fine!
But if I use Search, I get error, because MySQL does not allow to use Alias in Where Clause:

Couldn’t execute query. Unknown column ‘gaminiai_code’ in ‘where clause’ – SELECT count(*) as c FROM (SELECT gaminiai.id as id, gaminiai.code as gaminiai_code, gaminiai.name as gaminiai_name, gaminiai.ean as gaminiai_ean, gaminiai.ar_batt_charger as gaminiai_ar_batt_charger, battery.code as battery_code, charger.code as charger_code FROM gaminiai LEFT JOIN battery on battery.id = gaminiai.battery_id LEFT JOIN charger on charger.id = gaminiai.charger_id WHERE 1=1 AND ( `gaminiai_code` LIKE ‘%178%’ )) pg_tmp

1 Answers
Vygandas answered 3 months ago

UPDATE:

Solved. SImply change returned id’s to names/varchar with this:

$col[“dbname”] = “gaminiai.battery_id”;
$col[“editoptions”] = array(“table”=>”battery”, “id”=>”id”, “label”=>”code”);

The only thing left unclear – is how to export XLS value, not id….

Your Answer

0 + 19 =

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 / 5. Vote count:

As you found this post useful...

Follow us on social media!

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

Let us improve this post!