Alias on table and sorting failure

Suite and Optimus Suite 5
#1

$grid->render_sql(“Select FIRST_NAME,f.tabl.GEND as GENDER, b.tabl.COUNTRY as COUNTRY,CITY, c.tabl.STRTYPE as TSTREET, d.tabl.NAME_STREET as STREET from a.tabl, b.tabl, c.tabl, d.tabl, f.tabl where a.tabl.COUNTRY_LIVE= e.tabl.ID_COUNTRY and a.tabl.NAME_OF_STREET= d.tabl.ID_STR and a.tabl.GENDER= f.tabl.ID_GENDER and a.tabl.TYPE_OF_STREET= c.tabl.ID_STRT”,“”,“FIRST_NAME,GENDER,COUNTRY,CITY,TSTREET,STREET”);

grid is working and sorting work only for primary table a.tabl when i try to add sorting to b.tabl ,c.tabl, d.tabl, d.tabl or f.tabl it goes that error

====================================
Log started, 17/12/2010 07:12:08

SELECT * FROM ( select /+ FIRST_ROWS(100)/dhx_table.*, ROWNUM rnum FROM (SELECT FIRST_NAME,f.tabl.GEND as GENDER,B.tabl.COUNTRY as COUNTRY,CITY,c.tabl.STRTYPE as TSTREET,d.tabl.NAME_STREET as STREET FROM a.tabl, b.tabl, c.tabl, d.tabl, f.tabl WHERE a.tabl.COUNTRY_LIVE= e.tabl.ID_COUNTRY and a.tabl.NAME_OF_STREET= d.tabl.ID_STR and a.tabl.GENDER= f.tabl.ID_GENDER and a.tabl.TYPE_OF_STREET= c.tabl.ID_STRT AND CITY LIKE ‘%РОС%’ AND STREET LIKE ‘%П%’ AND NUMBER_OF_BULDING LIKE ‘%27%’ ORDER BY STREET ASC) dhx_table where ROWNUM <= 100 ) where rnum >0

oci_execute() [function.oci-execute]: ORA-00904: "STREET": invalid identifier at /www/Connector/db_oracle.php line 22

!!!Uncaught Exception
Code: 0
Message: Oracle - sql execution failed

#2

As far as I can see the problem is caused by aliaces in the original SQL, when sorting - original field name must be used, not the alias. I think you can change the sql to the next, which will work similar but will not fails for sorting.

$grid->render_sql("Select FIRST_NAME,f.tabl.GEND as GENDER, b.tabl.COUNTRY as COUNTRY,CITY, c.tabl.STRTYPE as TSTREET, d.tabl.NAME_STREET from a.tabl, b.tabl, c.tabl, d.tabl, f.tabl where a.tabl.COUNTRY_LIVE= e.tabl.ID_COUNTRY and a.tabl.NAME_OF_STREET= d.tabl.ID_STR and a.tabl.GENDER= f.tabl.ID_GENDER and a.tabl.TYPE_OF_STREET= c.tabl.ID_STRT","","FIRST_NAME,GENDER,COUNTRY,CITY,TSTREET,NAME_STREET");
#3

yes like i said it working when not sorting alias :frowning:(

using sqldeveloper that sql sorting with alias working, but i need to sort alias !!!

without it be hard to find need row in hole database :frowning:

provide pls. a sample code that work or how i can bypass this error

maybe exist another solution to solve problem with aliases ???

#4

field is gender in primary table, i have values “0” and “1”, at secondary is “male” and “female”.

at sample higher i use aliases to render this, but at sorting i must set the values like “0” and “1” how me to replace this values to “male” and “female” using #connector_select_filter ???

#5 
but at sorting i must set the values like "0" and "1" how me to replace this values to "male" and "female" using #connector_select_filter ???

Sorry, but it is not clear which result you need to achieve. Can you provide some more details?

#6

result must be “0” and “1”.

the aliases set to the values “0”=“male” and “1”=“female”.

As result at grid, i see what i need gender is “male” and “female”.

I want to use #connector_select_filter that output be from “male”, “female” and empty value and sorting by ID of table “B” “male”, “female” -> “0”, “1” in my primary table “A”

at primary table “A” gender has value “0” and “1”.

When i use #connector_select_filter at filter values is “0”,“1” for true output it must be “male”, “female”.

or attach pics for imagine ?

#7

[code]function custom_logic($filters){
$index = $filters->index(“gender”);
if ($index !== false){
$value = $filters->rules[$index][“value”];
if ($value == ‘male’) $value = 0;
if ($value == ‘female’) $value = 1;
$filters->rules[$index][“value”] = $value;
}
}

$grid->event->attach(“beforeFilter”, “custom_logic”);[/code]

#8

:wink: that quiet enough, but what me do if sort is 100 values ???

thanks for code, i think found what needed

$filter1 = new OptionsConnector($res);
$filter1->render_table(“countries”,“country_id”,“country_name(value)”);
$grid->set_options(“item_nm”,$filter1);

$grid->render_table("grid50","item_id","item_nm,item_cd");
#9

at picture gender is what i need and in table set aliases on value “0”,“1”.

But when sorting select filter sql has …GENDER LIKE ‘%чол.%’… but i needed the ID of value “чол.” ???

#10

You can set on client side the col-type for related column as coro and use

$grid->set_options(“gender”,array(“0” => “male”, “1” => “female”));

or render_table - if it better suits for you needs.

The main sql, which you are using for loading, need to be changed as well , you need to output just a 0 and 1 values. Client side code will automatically change such values to relative labels.

#11

thanks for everything i uses combox and it work perfect :smiley:

#12

using #combo_filter as result of error i receive correct xml, but like error ;( and in combo values empty …

at server side filtering combo
$gender = new ComboConnector($res,“Oracle”);
$gender->dynamic_loading(10);
$gender->render_table(“MHC.MHC_T_GENDER”,“ID_GENDER”,“GEND”);
or something mist ???

#13

have at table 64000 values, and using #select_filter it say very slow script …

#14

have at table 64000 values, and using #select_filter
It will be hardly usable in any case ( selectin one option from 64k list of values )

or something mist ???
Code looks correct, what is the error in your case?

#15

when loading page with grid open new window and it handle xml with data.

at samples of combo and attached to

, but nothing about using in grid.header ???
#16

You can

a) place div tag in the header ( you can place any html in it, actually )
b) init combo in that div
c) attach combo to the connector
d) attach onChange event to combo and call grid.filter from it ( or reload from server with new data )

#17

pls provide examples :slight_smile:

#18

combo box working perfect.

how me to filter my combo with other header values that connected to base “connector” at server side ???

by custom filter or some else …

#19

how me to filter my combo with other header values that connected to base “connector”
There is no any build in integration for such task. When some master input changed you can call

combo.clearAll() combo.load("connector.php?by="+master.value)

and on server side, user render_sql for combo connector, and include $_GET[‘by’] in it