loadRecords and joins

Classic Servoy
1

Hello,

I am working on a flexible way to retrieve data. I am building SQL statements and want to use controller.loadRecords(query) to show the results.

Now I have run into a problem. A query like

SELECT table_1.pk FROM table_1 INNER JOIN table_2 ON table_1.pk = table_2.fk WHERE ... ORDER BY table_1.pk

will (if I look at the stack trace) be fired to the database as

used question less sql 
SELECT table_1.pk FROM table_1 WHERE ... ORDER BY table_1.pk

and of course result in an error, because the table_2 is not used anywhere in the FROM part but in the WHERE condition. I know that I don’t have to use an INNER JOIN in this case, but what if I want to use something like a LEFT OUTER JOIN?

Can this be changed/fixed?

Thanks
Patrick

2

Servoy only supports table specifications in the SQL “from” part currently no plans yet to alter this (reporting/printing whould get even more complex, since we have to alter the sql to support SQL “group by”)
TIP: Many databases also do support inner/outer join syntax in the SQL “where”

3

Many databases also do support inner/outer join syntax in the SQL “where”

Yes, I know. But then you are stuck to vendor specific (funny) stuff like

WHERE x += y

and that means I have to code for every database I am running on. NATURAL JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN is SQL92 and should be supported by all major database vendors, so I’d rather use it that way. Is there really no way you could leave this portion untouched? I understood that group by is a problem and could live without that, but proper joining is a must in my situation.

Thanks
Patrick

4

Well it’s a bit more complicated, we do support reporting on loadRecords(SQL) as it is currently, when you require special joins in the SQL “from” then the question arises how to lookup related data in reporting (using servoy relations => SQL “where” joins) and how to do a proper SQL “group by”…

Is it not possible for you to use loadRecords(pkdataset) for now?

5

Is it not possible for you to use loadRecords(pkdataset) for now?

That’s exactly what I want! I don’t know how we came to discuss reporting :) . I am building a search engine. I just want to use the queries to show results. But there I need Joins. I would be using getDataSetByQuery, but that seems problematic with larger datasets, isn’t it? Of course, I don’t know how big the results are…

6

Actually, I can live with exactly these limitations:

  • Must start with ‘SELECT’.
  • Must contain ‘FROM’ and ‘ORDER BY’ keywords.
  • Must select from the specified forms’s table.
  • Can contain ‘?’ which are replaced with values from the array supplied to parameters function argument.
  • Cannot contain ‘GROUP BY’ or ‘HAVING’.
  • All columns must be fully qualified like ‘orders.order_id’.
  • The selected columns must be the specified form’s table primarykey columns (alphabetically ordered like ‘select a_id, b_id,c_id …’)
7

Is there news on this?

Thanks
Patrick

8

Allowing anything else than a comma separated list of table names in the SQL “from” is not considered nor planned

9

Couldn’t you allow anything between FROM and WHERE? This is really a big downside on using manualy queries if you can’t join. I need to redesign my whole search approach if I can’t use joins.

10

Currently not, it would break printing/reporting.

11

Maybe we could have a different function that allows any query and doesn’t conflict with current functionality. Something like controller.loadQuery(query) instead of loadRecords()? Just trying to find a way that helps us all…

12

Maybe in the future, but not on short term, suggestion for sort term:

var dataset = databaseManager.getDataSetByQuery(,maxRecordsToBeLoaded)
controller.loadRecords(dataset)

now the dataset loading has size limitation which can be easily be overcome is we provide: dataset.removeRow(x)

Than its possible to build your own pagination system as seen on google website (showing blocks of 50 records orso at the time), by using maxRecordsToBeLoaded and dataset.removeRow(x)

Let us know…

13

Hello Jan,

thanks for your suggestion. While that sounds like a good feature, it does not really solve my problem. From a user point of view it is confusing that when you do a search you see something else then you normally do (a google type result page instead of the table view you are used to).

I think we do need the capabilty to send any wild query to the database and use that as a foundset. I accept the limitation of selecting the primary keys, but I think the limitation on Joins or other things should be overcome. In the meantime I search using Servoy directly, but I will soon need more flexibility, so I hope this is solvable. A function controller.loadQuery() would be the right thing, I think.

Thanks
Patrick

14

patrick:
thanks for your suggestion. While that sounds like a good feature, it does not really solve my problem. From a user point of view it is confusing that when you do a search you see something else then you normally do (a google type result page instead of the table view you are used to).

I suggested the pageable result like google but used in normal tableview, so instead of scrolling the user clicks to view the next set of records.