DC asked 3 years ago
I’m trying to execute this query
select distinct members.id, members.name, members.email, payments.create_time from members left outer join payments on members.recurring_payment_id = payments.billing_agreement_id order by payments.create_time desc
This works when I perform it directly in MYSQL, but generates this error when performed through Grid
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 distinct members.id, members.name, members.email, payments.create_time from members left outer join payments on members.recurring_payment_id = payments.billing_agreement_id order by payments.create_time desc WHERE 1=1 LIMIT 1 OFFSET 0
It seems part of the phpgrid is adding “WHERE 1=1 LIMIT 1 OFFSET 0” to the end of the query.
Is there any way to prevent this? It’s preventing my query from working
Mike answered 3 years ago
Don’t use order-by in your queries. The order-by is handled by the Grid commands (you can find examples on the demo pages).
The where 1=1 is part of the grid-software processing to build the top(n) records for the initial display.
Abu Ghufran Staff answered 3 years ago
Hello,
You need to remove the order by from query and set it with these options.
$opt["sortname"] = "payments.create_time"; $opt["sortorder"] = "desc"; // where $opt goes to set_options() function
