Query Builder

Another problem is that apparently SQL queries read only a maximum of 4,438 rows even though the data explorer shows that the table from which data are selected has many more rows. Why is this? And is there a workaround?

Hi @eicken71,

Thank you for reaching out!

The Query Builder currently performs a synchronous query. If you query a table with several million rows, you won’t be able to cancel. You will either use a lot of memory and have to wait a very long time or run out of memory and get an error. The max rows limitation was added to prevent users performing such queries.

For these reasons I don’t think it’s a good idea to disable the max rows limitation by default, but we can add a option to our plugin settings to give users a choise.

However, before going to solutions I would like to understand why the limit command is causing you issues. Can you provide the necessary information to reproduce this issue?

I’m also curious about the maximum available rows. Do you see any errors when you enable debug mode?

Our long term solution is to query the database asynchronous and add a cancel button to allow cancellation at any time. This is on our to do list, but it has a low priority as most users are not querying large tables using the Query Builder.

Out of curiosity: what is your purpose for querying such long lists? Your information can help us to provide better solutions for all users…

Thanks,
Peter

Hi, Peter,

starting my answer from the bottom of your message:
I’m operating a language learning site – ondaz.de – with interactive H5P exercises. In order to better understand user behaviour (exercises too difficult? how many give up without completing? which exercise nees improving?), I have installed the h5pxapikatchu plugin which collects and stores the xapi statements sent by H5P. It is a very messy tool: Whenever a visitor hits a post in which H5P exercises are embedded, even without actually interacting with the exercise, H5P fires “attempted” statements which are collected. When a user interacts with the exercise, an “interacted” statement is generated. Therefore, a monthly collection of data is around 25,000 rows of ‘interactions’. As I learnt from analysing the data, most of it is white noise of the kind just described. But in order to reduce the amount of data and prepare them for analysis, I must still be able to handle the mass. The h5pxapikatchu plugin itself only allows to delete everything in order to prevent the database to become too large; analysis and selective reduction require sql access.
The problem with the default max. row limitation was that I had taken from tutorials to end queries with “;” – the Data Access editor then places a LIMIT command behind the “;” which causes a syntax error. I have now found out that without the “;” there is no syntax problem. So this issue is resolved.
If I may add one more point: The funktionality for saving queries is not very convenient. I would prefer to build a query library with names of queries and explanations of what they do. It is not clear to me what happens when I click the “save” button and how I can label the query just saved. So I have resorted to collecting queries in an EXCEL sheet.

Thank you,

Matthias

Hi Matthias,

Thank you for you explanation!

The organization of queries needs to be improved. This is an known issue. It started small to see if there was any interest. It’s time to think bigger. This is on our to do list.

Regarding your use case, there might be simplier solutions. When you start the Data Explorer and search for your table, click the manage button, select export to CSV and click export. You can load the CSV directly into Excel.

Does this help?
Peter

Thank you, Peter, but in my use case exporting a single databse table is of little use. Gathering information suitable for analysing H5P user behaviour requires combining information from many tables, including those created by the H5P-plugin that contain information about the exercises presented on my site.

After now having understood WP data access, what it does and what it can’t do yet, the challenge for me is simply learning sql better, exploring the data structure and composing meaningful queries. The ultimate goal would be just one query that creates one table per month which can be CSV-exported from the query builder for import into a statistics tool – a table containining all the information needed for useful analyses but no more.

Best wishes,

Matthias

Hi Matthias,

Sorry for me late reply! I had some deadlines to catch…

An other alternative might be to create a data table and change the number of rows shown per page (100 for example) to make it easier to navigate through the pages/rows. Changing the page length is explained here:

https://wpdataaccess.com/docs/data-tables-advanced-features/advanced-settings/

Search for: pageLength

Would that be an acceptable solution for you?

Thanks,
Peter