Complex search form help

General
1

Hi - James calling in from the UK.

I’m researching backendless for use upgrading a legacy app, I’ve worked thru most of the ‘Missions Map - Starter Pack with Code’ and I am liking what I see. I have a question about how to approach a complex search page.

Imagine an ecommerce app with an orders list page where an admin user can search for orders. In the DB there is a user table, order header table, and order line table. I want to provide a single search page with say 6 search inputs - lets say order number, orderer name, orderer email, product code, product description and order status (pending, delivered, returned, etc).

The concept is that any search field can be used in any combination. So:

  • entering an order number only would search for and return only that order number;
  • entering a product code would return all orders where that product code is referenced in an order line

So far so good, but…

  • entering some part of the orderers name AND some part of a product description would do a combined search matching cases where the name AND the product desc matched.

Quick summary would be ‘whatever search boxes are filled then use those values otherwise do not filter on those things’

That’s a simple example - our legacy app has several of these supersearch pages and one has around 15 search inputs.

The legacy app was built using SQL Server. Our solution in that world was to use the Coalesce function which I can explain further but do not with to mansplain if not needed.

I should also mention that I want to write a blog article about how to accomplish this, so there will be some payback for everyones collective brain-spend.

Thanks in advance.

James.

2

Ah - been reading docs. I see that I could do something at the client to set the where clause. For example I cut this JavaScript from How to Load Data Objects From the Server With an SQL Query | Backendless

  const dataQuery = Backendless.DataQueryBuilder.create()
  dataQuery.setWhereClause('age > 25 and addresses.city = \'New York\'')
  dataQuery.setRelated('addresses')

  return PersonStorage.find(dataQuery)

so I would be modifying the setWhereClause() param to be a mashed up clause based on the query fields that had values.

Whilst this is viable, it prompts me to ask if there is anything like the concept of a stored procedure in backendless? The reason I ask is that for a query that will be used in more than one place in an app, it is useful to be able to wrap the query up in a stored query (db function?) and invoke it with supplied parameters.

I would be happy to be told I am thinking ‘old-think’ on this subject !

Thanks

James

3

While there is no direct support for stored procedures, there are a few alternatives:

  1. Backendless Views. This is very similar to the traditional SQL views where you can compose a “virtual” table that has a mix of related columns. Views support the same where clause and make it easier to structure them as you do not need to add any complexity with related tables. Here’s a playlist with videos about views:
    Backendless Views - YouTube

  2. Cloud Code. You can encapsulate more complex logic into API Services where you can make multiple queries, and perform all the necessary ETL operations. From the UI perspective, there would be one request to an API service and expect a meaningful response. API Services can be created with JS (node.js), Java and Codeless. Here’s a quick guide for JS API Services: Basic Quick Start Guide - Developing Backendless Server Code with node.js

Hope this helps.

Regards,
Mark

4

Thanks Mark, I found the page How To Create A Database View | Backendless View Designer in the docs after my last post here and observed your enthusiasm for views ! I thought that maybe that would be a way ahead, so I’m pleased you concur.

I’ll look at the cloud code option too.

Coming from a SQL defacto-DBA direction, I think I’m going to have a number of ‘how-to-approach’ and ‘what does that mean for performance’ style questions on the data storage side of things, which I’ll try to document along the way for a possible future blog post. This must be common ground for us RDBMS dinosaurs! Anyway I’ll post those as other questions as they arise.

Thanks.

James.