sosamv
Posts: 50
|
| Posted: 12/22/2008, 9:53 PM |
|
Hi guys! I think I'm missing something, can you help me?
I have a grid, heres the sql statement created by codecharge:
SELECT tblrestaurante.*, tblcocina.Nombre AS tblcocina_Nombre, tblzona.Nombre AS tblzona_Nombre
FROM (((tblrestaurante INNER JOIN tblzonarestaurante ON
tblzonarestaurante.ID_Restaurante = tblrestaurante.ID_Restaurante) INNER JOIN tblcocinarestaurante ON
tblcocinarestaurante.ID_Restaurante = tblrestaurante.ID_Restaurante) INNER JOIN tblzona ON
tblzonarestaurante.ID_Zona = tblzona.ID_Zona) INNER JOIN tblcocina ON
tblcocinarestaurante.ID_Cocina = tblcocina.ID_Cocina
This works perfect, page count displayed "1 of 125"
but when i add a group by (required cuz i need to use group_concat() function) like this:
SELECT tblrestaurante.*, tblcocina.Nombre AS tblcocina_Nombre, tblzona.Nombre AS tblzona_Nombre
FROM (((tblrestaurante INNER JOIN tblzonarestaurante ON
tblzonarestaurante.ID_Restaurante = tblrestaurante.ID_Restaurante) INNER JOIN tblcocinarestaurante ON
tblcocinarestaurante.ID_Restaurante = tblrestaurante.ID_Restaurante) INNER JOIN tblzona ON
tblzonarestaurante.ID_Zona = tblzona.ID_Zona) INNER JOIN tblcocina ON
tblcocinarestaurante.ID_Cocina = tblcocina.ID_Cocina
GROUP BY tblrestaurante.ID_Restaurante
now the page count says "1 of 2" but if i click next i goes through to like 10 more pages 
Please help me =(
|
 |
 |
sosamv
Posts: 50
|
| Posted: 12/22/2008, 10:12 PM |
|
Argh! I've found the answer:
Custom SQL
If you use a custom SQL statement as the data source for a grid or editable grid there are certain nuances you should be aware of. In order for the navigator control to be able to get a count of the number of records returned by the SQL statement, the underlying code has to dynamically create a query to return the record count. In most cases, the generated SQL query is able to execute correctly. However, if a complex SQL query is used, for example, one that has a Group By clause, the total number of records cannot be calculated correctly and the navigator control will not function properly.
extracted from :
http://docs.codecharge.com/studio3/html/index.html?http...DataSource.html
Thanx!
|
|
|
|
sosamv
Posts: 50
|
| Posted: 12/22/2008, 10:31 PM |
|
It's working now =) I've modified the variable $CountSQL and copied my query from the variable $SQL and just did something like this $CountSQL = "Select count(*) from ([SQL STATEMENT FROM VARIABLE $SQL])"; and that's it, hope its useful to you guys!
jerry
|
|
|
|
datadoit
|
| Posted: 12/23/2008, 6:17 AM |
|
You could probably get around this also by using SELECT DISTINCT()
instead of GROUP BY.
|
|
|
|
sosamv
Posts: 50
|
| Posted: 12/23/2008, 7:44 AM |
|
Nope, cuz i have a relational table Many to Many and needed to use GROUP_CONCAT() to display all of the options on a single column :S
|
|
|
|
|
