Calculated fields within sql queries

Hyperbytes · August 7, 2018, 9:32am

I suspect this is currently beyond Wappler at this stage but you guys never fail to amaze me so i will ask just in case
An application I am remaking involved a lot of geo mapping and distance measurements.
Each record (accommodation locations) has the lat and lng preset and queries are run on the database to find the distance to these places from a point selected by the user.

In the current app (which i wrote) the sql calculation uses the below equation which is coded into the SQL statement.:

sqrt(power(69.1*(lat - (".$baselat.")),2)+power(69.1*(lng - (".$baselong."))*cos(lat/57.3),2)) AS distance

where lat and lng are table fields and $baselat and $baselong is the fixed point location, in this case the fixed point being stored in variables.
so to find all within 50 miles with radius as $raduis

The query is basically select *, {formula} as distance where distance < $radius

Is there any way a cacluated field such as this can be input via server connect?
Alternatively is it worth trying to “hack” the dmxConnect file accordingly?

psweb · August 5, 2018, 1:25pm

I am really not sure if this is going to help you or not @Hyperbytes but I was forced to do something totally out of the realms of what Wappler Query Builder was capable of just the other day, and after hours upon hours of various attempts, with the assistance of @TomD and @s.alpaslan they pointed me towards the SQL CREATE VIEW, which because i live under a rock I had never heard of before
Armed with this new found power I was able to create 2 different, fairly complex views and still parse in the variables from my application in Wappler to get the results I needed.

Here are the 2 Views just incase it gives you some idea, not sure.
Header of a csv file data

Summary

CREATE VIEW csv_header_view AS
select distinct `bronpro_vulo`.`br_invoices`.`inv_order_number` AS `Filter_Order_Number`,
`bronpro_vulo`.`csv_header_defaults`.`Header` AS `Header`,
concat(`bronpro_vulo`.`br_branch`.`b_order_number_prefix`, `bronpro_vulo`.`br_invoices`.`inv_order_number`) AS `Document_Number`,
`bronpro_vulo`.`csv_header_defaults`.`Deleted` AS `Deleted`,
`bronpro_vulo`.`csv_header_defaults`.`Print_Status` AS `Print_Status`,
`bronpro_vulo`.`br_branch`.`b_account_number` AS `Customer_Code`,
(case date_format(`bronpro_vulo`.`br_invoices`.`inv_date`,'%M') when 'January' then '4' when 'February' then '5' when 'March' then '6' when 'April' then '7' when 'May' then '8' when 'June' then '9' when 'July' then '10' when 'August' then '11' when 'September' then '12' when 'October' then '1' when 'November' then '2' when 'December' then '3' end) AS `Period_Number`,
date_format(`bronpro_vulo`.`br_invoices`.`inv_date`,'%d/%m/%Y') AS `Date`,
concat(`bronpro_vulo`.`br_branch`.`b_order_number_prefix`, `bronpro_vulo`.`br_invoices`.`inv_order_number`) AS `OrderNumber`,
`bronpro_vulo`.`csv_header_defaults`.`Inc_Exc` AS `Inc_Exc`,
`bronpro_vulo`.`csv_header_defaults`.`Discount` AS `Discount`,
`bronpro_vulo`.`br_global_settings`.`gs_banking_institute` AS `Invoice_Message_1`,
`bronpro_vulo`.`br_global_settings`.`gs_bank_account_number` AS `Invoice_Message_2`,
`bronpro_vulo`.`br_global_settings`.`gs_bank_branch_code` AS `Invoice_Message_3`,
`bronpro_vulo`.`br_branch`.`b_delivery_address1` AS `Delivery_Address_1`,
`bronpro_vulo`.`br_branch`.`b_delivery_address2` AS `Delivery_Address_2`,
`bronpro_vulo`.`br_branch`.`b_delivery_address3` AS `Delivery_Address_3`,
`bronpro_vulo`.`br_branch`.`b_delivery_address4` AS `Delivery_Address_4`,
`bronpro_vulo`.`br_branch`.`b_delivery_address5` AS `Delivery_Address_5`,
`bronpro_vulo`.`csv_header_defaults`.`Sales_Analysis_Code` AS `Sales_Analysis_Code`,
`bronpro_vulo`.`br_branch`.`b_terms` AS `Settlement_Terms`,
date_format(`bronpro_vulo`.`br_invoices`.`inv_date`,'%d/%m/%Y') AS `Document_Date`,
`bronpro_vulo`.`br_users`.`u_phone_mobile` AS `Telephone`,
`bronpro_vulo`.`br_branch`.`b_fax` AS `Fax_Number`,
`bronpro_vulo`.`br_users`.`u_first_name` AS `Contact_Person`,
`bronpro_vulo`.`csv_header_defaults`.`Exchange_Rate` AS `Exchange_Rate`,
`bronpro_vulo`.`br_branch`.`b_delivery_method` AS `Freight_Method`,
`bronpro_vulo`.`br_branch`.`b_freight` AS `Ship_Deliver`,
`bronpro_vulo`.`csv_header_defaults`.`Additional_Costs` AS `Additional_Costs`,
`bronpro_vulo`.`csv_header_defaults`.`Email_Status` AS `Email_Status`
from
(`bronpro_vulo`.`br_global_settings` left join
  (`bronpro_vulo`.`csv_header_defaults` left join
    (
      (
        (
          (
            (`bronpro_vulo`.`br_invoices` join `bronpro_vulo`.`br_users` on
              (
                (`bronpro_vulo`.`br_users`.`id_users` = `bronpro_vulo`.`br_invoices`.`inv_user_id`)
              )
            )
            join `bronpro_vulo`.`br_branch` on
            (
              (`bronpro_vulo`.`br_branch`.`id_branch` = `bronpro_vulo`.`br_users`.`id_branch_fk`)
            )
          )
          join `bronpro_vulo`.`br_group` on
          (
            (`bronpro_vulo`.`br_group`.`id_group` = `bronpro_vulo`.`br_users`.`id_group_fk`)
          )
        )
        join `bronpro_vulo`.`br_pricing` on
        (
          (`bronpro_vulo`.`br_pricing`.`id_price` = `bronpro_vulo`.`br_invoices`.`inv_price_id`)
        )
      )
      join `bronpro_vulo`.`br_products` on
      (
        (`bronpro_vulo`.`br_products`.`id_products` = `bronpro_vulo`.`br_pricing`.`id_product_fk`)
      )
    )
    on
    (
      (`bronpro_vulo`.`csv_header_defaults`.`Header` = `bronpro_vulo`.`csv_header_defaults`.`Header`)
    )
  )
  on
  (
    (`bronpro_vulo`.`br_global_settings`.`id_global_settings` = 1)
  )
)

And here is the Detail section for my csv file

Summary

CREATE VIEW csv_detail_view AS
select distinct `bronpro_vulo`.`br_invoices`.`inv_order_number` AS `Filter_Order_Number`,
`bronpro_vulo`.`csv_detail_defaults`.`Detail` AS `Detail`,
`bronpro_vulo`.`csv_detail_defaults`.`Cost_Price` AS `Cost_Price`,
`bronpro_vulo`.`br_invoices`.`inv_quantity` AS `Quantity`,
`bronpro_vulo`.`br_invoices`.`inv_price` AS `Unit_Selling_Price`,
round(((`bronpro_vulo`.`br_invoices`.`inv_price` * concat(1,`bronpro_vulo`.`br_invoices`.`inv_vat`)) / 100),2) AS `Inclusive_Price`,
`bronpro_vulo`.`br_products`.`pr_units` AS `Unit`,
`bronpro_vulo`.`br_branch`.`b_tax_type` AS `Tax_Type`,
`bronpro_vulo`.`csv_detail_defaults`.`Discount_Type` AS `Discount_Type`,
`bronpro_vulo`.`csv_detail_defaults`.`Discount_Percentage` AS `Discount_Percentage`,
`bronpro_vulo`.`br_products`.`pr_code` AS `Code`,
concat(`bronpro_vulo`.`br_products`.`pr_description`,' ',`bronpro_vulo`.`br_products`.`pr_units`,'     ',`bronpro_vulo`.`br_products`.`pr_mix`) AS `Description`,
`bronpro_vulo`.`csv_detail_defaults`.`Line_Type` AS `Line_Type`,
`bronpro_vulo`.`csv_detail_defaults`.`Projects_Code` AS `Projects_Code`,
`bronpro_vulo`.`csv_detail_defaults`.`Store` AS `Store`
from
(`bronpro_vulo`.`br_global_settings` left join
  (`bronpro_vulo`.`csv_detail_defaults` left join
    (
      (
        (
          (
            (`bronpro_vulo`.`br_invoices` join `bronpro_vulo`.`br_users` on
              (
                (`bronpro_vulo`.`br_users`.`id_users` = `bronpro_vulo`.`br_invoices`.`inv_user_id`)
              )
            )
            join `bronpro_vulo`.`br_branch` on
            (
              (`bronpro_vulo`.`br_branch`.`id_branch` = `bronpro_vulo`.`br_users`.`id_branch_fk`)
            )
          )
          join `bronpro_vulo`.`br_group` on
          (
            (`bronpro_vulo`.`br_group`.`id_group` = `bronpro_vulo`.`br_users`.`id_group_fk`)
          )
        )
        join `bronpro_vulo`.`br_pricing` on
        (
          (`bronpro_vulo`.`br_pricing`.`id_price` = `bronpro_vulo`.`br_invoices`.`inv_price_id`)
        )
      )
      join `bronpro_vulo`.`br_products` on
      (
        (`bronpro_vulo`.`br_products`.`id_products` = `bronpro_vulo`.`br_pricing`.`id_product_fk`)
      )
    )
    on
    (
      (`bronpro_vulo`.`csv_detail_defaults`.`Detail` = `bronpro_vulo`.`csv_detail_defaults`.`Detail`)
    )
  )
  on
  (
    (`bronpro_vulo`.`br_global_settings`.`id_global_settings` = 1)
  )
)

As I say I am really not sure if in your situation this will help at all though, but hope so, good luck.

Hyperbytes · August 5, 2018, 7:10pm

Thanks @psweb. You may be on to something there, a parameterised query may just do the job, i will have a look tomorrow and let you know how it turns out. Certainly a good avenue to explore allowing the complex query to be taken outside Wappler and into Mysql server level.

Hyperbytes · August 6, 2018, 10:41am

@psweb Tried a few ideas but crashed and burned up to now.
Writing a query within Mysql database is simple and works but of course you cant pass parameters to a query.
Tried stored procedure next, works OK at command line but they are not visible withing server connect and I am not sure how I would pass the parameters anyway. Will build the page framework in Wappler then sadly will have to drop back into DW to finalise those pages. Not the end of the world, was a big ask at this early stage of development, just means I will have to continue to code the google mapping also rather than use Wapplers google maps feature which is really slick

psweb · August 6, 2018, 10:54am

Thanks for letting me know, yeah, thats a little unfortunate, suppose we can’t do everything in Wappler just yet, lol, hopefully soon, very soon, we will. If i think of anything else I will give you a shout though.

Hyperbytes · August 6, 2018, 11:02am

Calculated fields would be really useful though, even at simple level to do something like {{Amount}} * {{Cost}} as Total rather than doing the calculation at client end

George · August 6, 2018, 2:14pm

For having calculated fields you gave two choices:

Make a database view with the calculated field as column and then just use it as a table in the query builder
Make an server connect action with multiple steps:

the query with all the fields needed for calculation, but switch output off
loop trough the query records
for each record use set Variable to make the calculation with server formatter expression (us expression syntax) and output it

This way you can have your custom data output

Hyperbytes · August 7, 2018, 8:14am

Thanks @George for taking the time to answer this question.
Had looked at using a view but as the query needs 3 parameters, centre point lat, lng and radius a view is not suitable; views don’t take parameters. Tried a paramaterised query but they are not visible to the query builder (not sure how i would send the parameters either).
Option 2 is a possible workaround but as the query retrieves a subset of typically around 25 records from currently 6,500, the problem still remains of filtering that query on the calculated distance form the centre point. Also have some performance concerns with this type of solution.
No criticism of Wappler intended, i love your product, but in this case a few lines of PHP will make for a much simpler result. I am basically converting the site to PHP7 compatibility (uses PHP5/SQL at present) so there isn’t that much code to rewrite, just the query sections and was using this project as a evaluation/ training project for Wappler. This is the only place where i have hit a limitation but the improvements i have been able to make far outweigh that 1 limitation.

George · August 7, 2018, 9:34am

Agreed @Hyperbytes in your situation indeed a sql calculated fields are the best solution specially when they are parametric and also used as filter.

@patrick will check it out after our short summer break

Hyperbytes · August 7, 2018, 9:55am

Thanks for giving this your time and consideration anyway, i am sure at some time in the future Wappler will evolve into a product which can do this type of thing but as i often say to customers, everything is possible, the real question is when (and how much in customers case), Rome wasn’t built in a day!

Hyperbytes · August 9, 2018, 7:30am

For anyone interested i have come up with a solution to the problem which only required a few lines of custom code in the wappler script.

Basically the company provided insurance based products to customers related to accommodation.
When clients apply for the product they are also offered a referral to local accommodation providers to help them find accommodation
Thankfully (for me) anyone using this service is logged in as a customer/ potential customer and has a user id
The system must find all accommodation providers (ap’s) within a user specified radius of the customers desired place of residence (which is then shown on a map and provided as a list)
All subscribing accommodation providers have their details including lat/long stored in a table “ap”, unique key “ID”.
All users have details in a table “users” unique key “UserID”
The former SQL based method simply ran the above query to find all ap’s withing the specified radius.
My desire was to make the new solution as wappler friendly as possible.
My solution was to create a “mapping table” containing the UserID of users and ID of ap’s in range
A few lines of php in the header deletes all entries in the mapping table for that user on entry.
The above query is then used to select all appropriate ap’s and insert into the mapping table.
Once created it is a simple join from the mapping table to the ap’s and the users to generate details for the aps and lists within wappler.
There is a small performance hit but as this is not something a user will run often (only repeated if locations change) i am not too worried
The “mapping table” aplinks simply contains two fields UserID (users unique id) and ID (ap’s unique id) so storage overhead is not significant. Once a user completes the process and their application is closed then the linked mapping records will be removed also keeping the overhead down.
Variables are hard coded for test purposes but will be passed as POST variables in final version
It’s not perfect but does the job for the moment and allows integration with other wappler features such as google maps
The PHP code in the header is simply this

$baselat = 53.9;
$baselng = -1.0;
$radius=25;
$user=1;
$pdo = new PDO(‘mysql:host=192.168.1.106;dbname=sarah;charset=utf8’, '**’, ‘********’);
$deleter="DELETE from aplinks where UserID = ".$user;
$statement =$pdo->prepare($deleter);
$statement->execute();
$sql="INSERT INTO aplinks select ID, $user as UserID from ap where sqrt(power(69.1(lat - “.$baselat.”),2)+power(69.1(lng - “.$baselng.”)*cos(lat/57.3),2)) <= ".$radius;
$statement =$pdo->prepare($sql);
$statement->execute();

***note minor code changes made since original posting

psweb · August 8, 2018, 4:44pm

Well done, sounds like you had a bit of fun getting that one just right

psweb · August 8, 2018, 7:06pm

Want to ask you a silly question here @Hyperbytes, in the insert to database builder, you can select the table to insert to and select the value you want to insert, and i can use something like {{fieldA}} as my value or if i wanted to add two form inputs to the same field in mt database table i can do {{fieldA}}+{{fieldB}} in the value area.
Would this not help a little if you can use this to do some of the Math for you? Just out of curiosity.

t11 · August 8, 2018, 8:46pm

This is why Wappler is a revolution. Although is promoted as a build everything with minimal knowledge software, true power is shown when Wappler is used by people who know how to program. We don’t use Wappler beacuse it is a bunch of components that is creating ‘dancing bears’ results rather than it is a cutting edge fullstack framework. Just to remind you. There is almost less than six months that Wappler was officially released. I am so pleased that more and more Wappler users are creating stuff in code view. This means that Wappler is something great. Wappler is a revolution in custom web development. And even if most of traditional programmers are suspicious and arrogant, they should open their eyes and see what is coming.

Hyperbytes · August 9, 2018, 7:43am

@psweb
Valid question Paul.
The issue lies with which data resides within the App Connect framework and which doesn’t.
The calculation to measure the distance between two lat/lng’s is too complex to efficiently calculate within Wappler.
My problem was how to manipulate the data outside Wappler but leave it in a form which Wappler could later deal with (i.e. a table). Hence the need to manipulate the data in PHP outside the framework but present the result to Wappler as a fixed name table which could be used within the framework.
It probably could have been done in Wappler using repeating regions and multiple inserts but that would be very messy and slow by comparison.
The above method is highly efficient and typically selects a subset of around 200 records from a table of 6,500 pretty much instantly.

psweb · August 9, 2018, 7:50am

Thanks @Hyperbytes appreciate the explanation, makes sense.

patrick · August 22, 2018, 12:35pm

In most cases it is good enough to do the calculations client-side, like with calculating the total {{Amount * Cost}} or displaying a full name like {{FirstName + ' ' + LastName}}. There is no need to do this in the query, it only results in more data traffic. If you want to have it in the query result then most databases support calculated columns (http://www.mysqltutorial.org/mysql-generated-columns/), or you can create a separate View that returns the result that you need.

The above will not work for your distance problem since it requires parameters, I suggest creating a Stored Procedure for it. Currently this is not supported in Wappler, but we are investigating it.

TomD · August 22, 2018, 1:57pm

Just as an example of what Patrick mentioned:
I used a calculated column to get round the limitation of the mail extension not including dynamic repeats. I needed to allow the user to make a selection of records and include them in an email. Each item in the list consisted of several fields, with some basic HTML formatting, in a table. I created a calculated field in the database, merging these fields and the HTML, and it worked fine*.

I created a repeat in server connect and populated a variable with the contents of this field:
{{query1.join("<br>", "item_summary") }}
I then added a Send Mail step and included this variable in the relevant place in the content.

I think this overcomes one of the main shopping cart problems. I shall use the approach for a shopping cart next.

*I think there is a limit to the number of characters in a calculated/generated column. When I added some more field to my calculation, I reached this limit, so I used a trigger instead. Everything worked in exactly the same way.

patrick · November 14, 2019, 10:43am

patrick · November 14, 2019, 10:43am