Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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>  ​