Differences
This shows you the differences between two versions of the page.
tutorial:complete_query [2015/02/07 10:56] |
tutorial:complete_query [2020/04/16 10:56] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | Adichatz generates brute queries to fetch data from Application server or database (by default, one query per entity).\\ | ||
+ | For example, the query for a <wrap adicode>Customer</wrap> class is described by an XML file called <wrap adicode>$projectDirectory/resources/xml/model/customer/CustomerQUERYGENERATED.axml</wrap>: | ||
+ | \\ | ||
+ | <sxh xml; first-line: 10; title: excerpt from 'CustomerQUERYGENERATED.axml' file.> | ||
+ | <?xml version="1.0" encoding="UTF-8" standalone="yes"?> | ||
+ | <queryTree xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" queryType="JQL" entityURI="adi://myproject/model.customer/CustomerMM" suffix="c" xsi:noNamespaceSchemaLocation="http://www.adichatz.org/xsd/v0.8.7/generator/queryTree.xsd"> | ||
+ | <jointure fieldName="address" jointureType="join fetch " suffix="a"/> | ||
+ | <jointure fieldName="store" jointureType="join fetch " suffix="s"/> | ||
+ | <queryPreference orderByClause="c.customerId"> | ||
+ | <parameter id="active" property="active" style="SWT.RIGHT" suffix="c"/> | ||
+ | <parameter id="address" property="address" suffix="c"/> | ||
+ | <parameter id="createDate" property="createDate" style="SWT.CENTER" suffix="c"/> | ||
+ | <parameter id="customerId" property="customerId" style="SWT.RIGHT" suffix="c"/> | ||
+ | <parameter id="email" property="email" suffix="c"/> | ||
+ | <parameter id="firstName" property="firstName" suffix="c"/> | ||
+ | <parameter id="lastName" property="lastName" suffix="c"/> | ||
+ | <parameter id="lastUpdate" property="lastUpdate" style="SWT.CENTER" suffix="c"/> | ||
+ | <parameter id="store" property="store" suffix="c"/> | ||
+ | </queryPreference> | ||
+ | </queryTree> | ||
+ | </sxh><WRAP indic><wrap adititle>Remarks</wrap>:\\ | ||
+ | The query description contains 2 parts:\\ | ||
+ | * <wrap adicode>Jointures</wrap> part describes other entities linked to each rows fetched by the query. | ||
+ | * The <wrap adicode>QueryPreference</wrap> part describes the default attributes managed in the outline panel of a Query Editor. Here, only optional query parameters are listed. | ||
+ | </WRAP> | ||
+ | \\ \\ | ||
+ | <html><strong id="changed_query">Complete query by changing XML elements</strong></html>:\\ | ||
+ | Now, we want to complete the query in two ways: | ||
+ | - Add jointures to fetch the staff of the store and the address with the city and the country of the store. | ||
+ | - Add a preference to select only Customers from USA (<wrap adicode>countryId=103</wrap>) a new pagination, a new column orders and two filters.\\ | ||
+ | Queries could be easily changed as shown below: | ||
+ | <sxh xml; first-line: 1; highlight: [4,5,9,10,11,12,28,29,30,31,32,38,40,41,45,46]; title: excerpt from 'CustomerQUERY.axml' file (new version)> | ||
+ | <?xml version="1.0" encoding="UTF-8" standalone="yes"?> | ||
+ | <queryTree xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" queryType="JQL" entityURI="adi://myproject/model.customer/CustomerMM" suffix="c" xsi:noNamespaceSchemaLocation="http://www.adichatz.org/xsd/v0.8.7/generator/queryTree.xsd"> | ||
+ | <jointure fieldName="address" jointureType="JOIN FETCH" suffix="a"> | ||
+ | <jointure fieldName="city" jointureType="JOIN FETCH" suffix="ci"> | ||
+ | <jointure fieldName="country" jointureType="JOIN FETCH" suffix="co"/> | ||
+ | </jointure> | ||
+ | </jointure> | ||
+ | <jointure fieldName="store" jointureType="JOIN FETCH" suffix="s"> | ||
+ | <jointure fieldName="staff" jointureType="JOIN FETCH" suffix="st"/> | ||
+ | <jointure fieldName="address" jointureType="JOIN FETCH" suffix="sa"> | ||
+ | <jointure fieldName="city" jointureType="JOIN FETCH" suffix="sci"> | ||
+ | <jointure fieldName="country" jointureType="JOIN FETCH" suffix="sco"/> | ||
+ | </jointure> | ||
+ | </jointure> | ||
+ | </jointure> | ||
+ | <!-- default preference --> | ||
+ | <queryPreference orderByClause="c.customerId"> | ||
+ | <pagination firstResult="0" maxResults="50" paginated="true"/> | ||
+ | <parameter id="address" suffix="c"/> | ||
+ | <parameter id="customerId" style="SWT.RIGHT" suffix="c"/> | ||
+ | <parameter id="createDate" style="SWT.CENTER" suffix="c"/> | ||
+ | <parameter id="email" suffix="c"/> | ||
+ | <parameter id="firstName" suffix="c"/> | ||
+ | <parameter id="lastName" suffix="c"/> | ||
+ | <parameter id="lastUpdate" style="SWT.CENTER" suffix="c"/> | ||
+ | <parameter id="active" suffix="c"/> | ||
+ | <parameter id="store_city" prompt="Store city" property="city" suffix="a"/> | ||
+ | <parameter id="store_country" prompt="Store country" property="country" suffix="sci"/> | ||
+ | <parameter id="customer_city" prompt="Customer city" property="city" suffix="a"/> | ||
+ | <parameter id="customer_country" prompt="Customer country" property="country" suffix="ci"/> | ||
+ | <parameter id="staff_name" prompt="Staff name" property="lastName" suffix="st"/> | ||
+ | </queryPreference> | ||
+ | |||
+ | |||
+ | <customizedPreferences> | ||
+ | <!-- USA Customers Column order id, fist name, name..., Bar=Navigation, ... --> | ||
+ | <preference id="usa"> | ||
+ | <queryPreference orderByClause="c.customerId"> | ||
+ | <pagination firstResult="0" maxResults="20" paginated="true"/> | ||
+ | <parameter prompt="Customer country" property="country" entityURI="adi://myproject/model.country/CountryMM" suffix="ci" valid="true" columnText="United States" expression="103" operator="=" id="customer_country"/> | ||
+ | </queryPreference> | ||
+ | <controllerPreference columnOrder="0, 3, 4, 1, 2, 5, 6, 7, 8" statusBarKey="Navigation" tableRendererKey="Binding"> | ||
+ | <filters> | ||
+ | <filter enabled="true" text="Value in column 'active' is true." column="activeTC" searchString="true"/> | ||
+ | <filter enabled="false" text="Value in column 'store' contains string 'Australia' (Case insensitive.)." column="storeTC" searchString="Australia" exactString="false" caseInsensitive="true"/> | ||
+ | </filters> | ||
+ | </controllerPreference> | ||
+ | </preference> | ||
+ | </customizedPreferences> | ||
+ | </queryTree> | ||
+ | </sxh> | ||
+ | |||
+ | <WRAP indic><wrap adititle>Explanations</wrap>:\\ | ||
+ | |< 100% 10em - >| | ||
+ | ^ <html><small></html>Lines 4-5<html></small></html>:|@#eff5fb:Fetch city and country of the address of the customer.| | ||
+ | ^ <html><small></html>Line 9<html></small></html>:|@#eff5fb:fetch the staff of the customer.| | ||
+ | ^ <html><small></html>Lines 10-12<html></small></html>:|@#eff5fb:Fetch address, city and country of the store of the customer.| | ||
+ | ^ <html><small></html>Lines 28-32<html></small></html>:|@#eff5fb:Add parameters allowing the user to extend the selection to specified fields.| | ||
+ | ^ <html><small></html>Line 38<html></small></html>:|@#eff5fb:Define a new preference, identified by <wrap adicode>usa</wrap> string, column order is changed and chosen <wrap adicode>Navigation</wrap> bar is <wrap adicode>Scale</wrap>: User can navigates thru pages using a scale control.| | ||
+ | ^ <html><small></html>Line 40<html></small></html>:|@#eff5fb:Pagination start to first element (position 0) and fetch 50 rows per page.| | ||
+ | ^ <html><small></html>Line 41<html></small></html>:|@#eff5fb:select only customer from USA.| | ||
+ | ^ <html><small></html>Lines 45-46<html></small></html>:|@#eff5fb:Add 2 filters: first on column <wrap adicode>Active</wrap>, second on column <wrap adicode>Store</wrap>. Only first filter is enabled.| | ||
+ | Open [[tutorial:add_navigator_item|Add navigator item]] to see how to create a new item in navigator to call the query with defined preference.\\ | ||
+ | Open [[tutorial:change_table|Change table include]] to see how to display data from new jointures in table control. | ||
+ | </WRAP> |