Select Command

QuestionsSelect Command
John Godish asked 11 years ago

How do I stop the select_command from adding "WHERE 1=1 LIMIT 1 OFFSET 0" to the end of my custom Select statement?

8 Answers
Abu Ghufran answered 11 years ago

This query is used internally to get the field names for grid header.
It is not used in data fetching. If you can tell what exact problem are you facing with this query, i may suggest some solution.

You can search this string in jqgrid_dist.php and change with more desired one.

John Godish answered 11 years ago

Per your documentation;

Grid Options
Custom SQL Query
By default, when we define the ->table property, it fetches all the possible columns of table.
We can provide custom SQL query in ->select_command property to pick columns available for grid.
We can use complex multi-join sub-queries in it.

$g->select_command = "SELECT i.id, invdate , c.name, i.note, i.total, i.closed FROM invheader i INNER JOIN clients c ON c.client_id = i.client_id";

Here is my select_command
$d->select_command = "SELECT pipn.id,pipn.products_id,products_description.products_name,products.products_price,pipn.inventory_position_room_id,pipn.inventory_position_rack_id,pipn.inventory_position_level_id,pipn.products_stored_qty,pipn.date_update,pipn.is_deleted FROM products_inventory_position_new AS pipn LEFT OUTER JOIN products ON products.products_id = pipn.products_id LEFT OUTER JOIN products_description ON products_description.products_id = pipn.products_id ORDER BY pipn.products_id";

Here is the error I get:
Couldn't execute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 LIMIT 1 OFFSET 0' at line 1 – SELECT pipn.id,pipn.products_id,products_description.products_name,products.products_price,pipn.inventory_position_room_id,pipn.inventory_position_rack_id,pipn.inventory_position_level_id,pipn.products_stored_qty,pipn.date_update,pipn.is_deleted FROM products_inventory_position_new AS pipn LEFT OUTER JOIN products ON products.products_id = pipn.products_id LEFT OUTER JOIN products_description ON products_description.products_id = pipn.products_id ORDER BY pipn.products_id WHERE 1=1 LIMIT 1 OFFSET 0

The WHERE 1=1 LIMIT 1 OFFSET 0 is getting automatically added to the end of the statement. How do I stop that from happening?

John Godish answered 11 years ago

If the select_command is only used for column names then your documentation is wrong, as it states "We can provide custom SQL query in ->select_command property to pick columns available for grid.
We can use complex multi-join sub-queries in it."

How do you tell tell the grid what data goes in the custom columns defined above??

Abu Ghufran answered 11 years ago

Please try removing ORDER BY from SQL, and set it in using following code.

$grid["sortname"] = 'id'; // by default sort grid by this field
$grid["sortorder"] = "desc"; // ASC or DESC

$g->set_options($grid);

It should resolve the case.

Nani answered 11 years ago

Fixed my problem.
Perfect.

shahab answered 10 years ago

i get this error i want to use sql command:

Couldn't execute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 LIMIT 1 OFFSET 0' at line 1 – SELECT WHERE 1=1 LIMIT 1 OFFSET 0

Abu Ghufran answered 10 years ago

Perhaps, you forgot to set $g->table property.
It is required in all cases.

C3media answered 8 years ago

Hi,

I have tried a query and works fine, I need to load grid based in post value from other page in comparison value into query. this is my query:
SELECT
`demo_candidatos`.`demo_eleccion_ideleccion` AS `Ideleccion`,
`demo_candidatos`.`idcandidatos` AS `Idcandidatos`,
`demo_candidatos`.`nombre` AS `Candidatos`,
`demo_candidatos`.`numero` AS `Numero_Tarjeton`,
`demo_candidatos`.`tarjeton` AS `tarjeton`
FROM
(`demo_candidatos`
JOIN `demo_eleccion`,`demo_voting`)
WHERE
((`demo_eleccion`.`ideleccion` = `demo_candidatos`.`demo_eleccion_ideleccion`)
AND (`demo_eleccion`.`estado` = 'Abierto') AND (`demo_eleccion`.`ideleccion` = `_POSTVAR`)AND
(`demo_eleccion`.`ideleccion` = `_POSTVAR`)AND
(`demo_eleccion`.`ideleccion` = `_POSTVAR`))
GROUP BY `demo_candidatos`.`idcandidatos`

Thanks!

Your Answer

0 + 19 =

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?