controller.loadRecords(SQL) & find()

Classic Servoy
1

I wanted to use controller.loadRecords(SQL) using SQL at some point.
Is it expensive to
controller.find();
controller.search();

Which method is efficient?

I guess find() uses SQL to query database and there is not much difference between the above two ways. You opinions please…

Thanks!

2

They both use SQL statements to find the rows - so there is not really any speed difference.

You would use loadRecords(SQL) when you have criteria from multiple files away (that would require a sql statement to do the joins) - for example. You could use controller.find() if you were just searching through columns in a single table (or one file away through a relation).

Hope this helps.

3

Thanks for the explanation. Exactly for the same reason (multiple tables away) I am using loadRecords() instead of the find().

Thanks again.

4

Hi Bob,

I am using the following SQL for the controller.loadRecords(). But I get errors. But it works fine in phpMyAdmin.

SQL:

SELECT DISTINCT (
training.training_id
)
FROM training
LEFT JOIN group_activity_history ON training.training_id = group_activity_history.training_id
LEFT JOIN indv_activity_history ON training.training_id = indv_activity_history.training_id
LEFT JOIN broadcast_history ON training.training_id = broadcast_history.training_id
LEFT JOIN indv_email_history ON training.training_id = indv_email_history.training_id
LEFT JOIN contact_history ON ( group_activity_history.group_activity_id = contact_history.group_activity_id
OR indv_activity_history.indv_activity_id = contact_history.indv_activity_id
OR broadcast_history.broadcast_id = contact_history.broadcast_id
OR indv_email_history.indv_email_id = contact_history.indv_email_id )
WHERE contact_history.contact_id =9
ORDER BY training.training_id

Error:

java.sql.SQLException: Unknown table ‘contact_history’ in where clause Unknown table ‘contact_history’ in where clause

What I am doing here is wrong? Is there any other way I can re-write the SQL or the perfect thing is can servoy allow this SQL?

I really need to get this resolved very urgently.

Thanks for time & help.

5

Another thing is it works great with databaseManager.getDataSetByQuery()

6

faheemhameed:
Hi Bob,

I am using the following SQL for the controller.loadRecords(). But I get errors. But it works fine in phpMyAdmin.

…snip…
Error:

java.sql.SQLException: Unknown table ‘contact_history’ in where clause Unknown table ‘contact_history’ in where clause

What I am doing here is wrong? Is there any other way I can re-write the SQL or the perfect thing is can servoy allow this SQL?

I really need to get this resolved very urgently.

Thanks for time & help.

Did you read the limitations/requirements of the SQL syntax in the sample code?
I may be wrong, but I remember there are special requirements for using SQL directly with loadRecords…

7

I think I had read the limitations and I think I am within those limitations!

It does not say we cannot have LEFT JOIN in the sql statement.

I need to solve this ASAP. Dear servoyians can help??

Thanks!

8

faheemhameed:
Another thing is it works great with databaseManager.getDataSetByQuery()

Why don’t you use this one?
I use it everywhere, and it is fast… then use controller.loadRecords() to load the dataset returned by the Query…

9

In a tab panel I wanted to show the foundset as a normal list like other tabs (which is a related one). That’s why I am using controller.loadRecords() and I use databaseManager.getDataSetByQuery() for some other puposes too.

But whatever way I use, my SQL command should work with controller.loadRecords() since it is under the limitations set for it.

10

Any servoyians can give me a reply on this? Thanks!

11

faheemhameed:
Any servoyians can give me a reply on this? Thanks!

loadRecords does not support Left joins.

12

I can see loadRecords does support LEFT JOIN. See for example, the following SQL.

SELECT DISTINCT(training.training_id)
FROM training
LEFT JOIN group_activity_history ON training.training_id = group_activity_history.training_id 
LEFT JOIN indv_activity_history ON training.training_id = indv_activity_history.training_id 
LEFT JOIN broadcast_history ON training.training_id = broadcast_history.training_id 
LEFT JOIN indv_email_history ON training.training_id = indv_email_history.training_id 
LEFT JOIN contact_history ON ( group_activity_history.group_activity_id = contact_history.group_activity_id 
OR indv_activity_history.indv_activity_id = contact_history.indv_activity_id 
OR broadcast_history.broadcast_id = contact_history.broadcast_id 
OR indv_email_history.indv_email_id = contact_history.indv_email_id ) 
ORDER BY training.training_id

It runs perfectly through controller.loadRecords()

Only if the table name is referred in the WHERE class the servoy throws a validation error.

I guess this should be very easy for you guys. I think you are checking for whether the table name referred in the WHERE clause is from the list of tables in the FROM clause. I guess you can also allow the referred table which is also from the LEFT JOIN (and other JOIN) clauses.

Please do not ignore this. This is a very very useful feature for us. I would love you very much if this is fixed in the following beta.

Thanks a million!!

13

Dear servoyians!

Any news on this?

I understand all it needs for the Servoy form controller to show the records properly is unique primary keys (of the controller in use). My SQL query perfectly returns unique primary keys. So why can’t we just allow my LEFT JOINs?

Thanks!

14

Servoy internally cannot work with left/right joins (they are also different on each database) so allowing loading this SQL would mean you would loose all the other functions like “find in find” and reporting.
I’m not sure if we should allow the use of this SQL anyway (and loosing the other functionality), I suggest you start a vote for this.

15

What about this (pseudo) code?

var query = '<my left join query>';
var ds = databaseManager.getDataSetByQuery(controller.getServername(), query, null, 100000);
controller.loadRecords(ds);
16

This code will work up to about 200 records, Servoy generates a IN query for this type of loading (which we have to because otherwise aggregates fail to work)

17

wow!

will check this out!

Thanks!

18

Works great Robert. But the worry is about the limitation on number of records.

Will the controller.loadRecords(SQL) (with LEFT JOINs) be fixed shortly?

Thanks!

19

As Jan Blok stated in this thread there are consequences on using a left/right-join.
He suggested you start a poll on this forum to see how many people need this functionality and are willing to sacrifice other functionality that Jan pointed out.

20

I’m not sure, but I think, there is a workaround.

What I do is a querie that selects all the PK’s
than do: var dataArray = dataset.getColumnAsArray(1)

I put this array in a string-field for archiving purposes:
textfield = dataArray.join(‘,’)

when I have to load the records again, I do this:
var split = textfield.split(‘,’)
var dataset = databaseManager.convertToDataSet(split);
forms.[yourform].controller.loadRecords(dataset)

As far as I can see, there is no limitation in total of records (>200)