Database

Nobody responded to my previous post, but since we needed it here I have developed a modification for the database module to allow for advanced searches.

Attached are two screenshots, one showing field setup of the demo database used and one showing an example advanced search. There is also a unified diff attached that patched cleanly with moodle head this morning. It will allow you to search on a specific field or fields from the database module. The advanced search form is dynamically generated from the database fields present so as you change your database structure your database search capabilities will change to suit  the schema.

Please note that in the example screenshots, not all data fields are displayed, but you do have the ability to search on all fields.

Due to the modularity of the database I have extended it to allow each data field to create its own search field, dropdown or text, etc. so that appropriate searches happen depending on the data that should be present. Currently date fields do not have search capabilities as I am not aware of a moodle function for handling date entry that allows for no date to be set via a form.

Hopefully others find this useful.

regards,
Jeff

New diff is attached with improved query speed; with large data sets the advanced search was way too slow. This fix even speeds up regular display.

It should be possible to further improve the speed. I am taking a look into cleaning up the record retrieval method.

regards,
Jeff

The data module should have indexes added to {$CFG->prefix}data_content for the following fields: 'fieldid', 'recordid', and 'content' with an appropriate size(10?) also 'recordid, fieldid'

Also, the row retrieval method should be reviewed and something like the following should be considered to retrieve a record in one query rather than X queries where X represents the number of fields in the database; the current method is rather slow for databases with a large number of fields.

select r.id, f12.content as Category,
f13.content as Company,
f14.content as Position,
f15.content as Contact,
f24.content as Phone,
f25.content as Cell,
f26.content as Fax,
f27.content as Address,
f28.content as City,
f30.content as Zip,
f31.content as Website,
f32.content as Email
FROM
mdl_data_records r
LEFT JOIN mdl_data_content f12 ON f12.recordid=r.id AND f12.fieldid=12
LEFT JOIN mdl_data_content f13 ON f13.recordid=r.id AND f13.fieldid = 13
LEFT JOIN mdl_data_content f14 ON f14.recordid=r.id AND f14.fieldid = 14
LEFT JOIN mdl_data_content f15 ON f15.recordid=r.id AND f15.fieldid = 15
LEFT JOIN mdl_data_content f24 ON f24.recordid=r.id AND f24.fieldid = 24
LEFT JOIN mdl_data_content f25 ON f25.recordid=r.id AND f25.fieldid = 25
LEFT JOIN mdl_data_content f26 ON f26.recordid=r.id AND f26.fieldid = 26
LEFT JOIN mdl_data_content f27 ON f27.recordid=r.id AND f27.fieldid = 27
LEFT JOIN mdl_data_content f28 ON f28.recordid=r.id AND f28.fieldid = 28
LEFT JOIN mdl_data_content f30 ON f30.recordid=r.id AND f30.fieldid = 30
LEFT JOIN mdl_data_content f31 ON f31.recordid=r.id AND f31.fieldid = 31
LEFT JOIN mdl_data_content f32 ON f32.recordid=r.id AND f32.fieldid = 32;

Please note that this small example to illustrate the idea. By adding the indexes mentioned above this query should be quick. This could also be considered to handle returning all of the results using "r.id IN (192,193,194)" where the values in parenthesis are obtained via the patch provided.

This suggested change combined with my latest patch in this thread should offer much improved speed by minimizing result sets, and the number of database queries necessary.

The code could also potentially be simplified by returning the whole result set via the above SQL and using the standard API for tables to handle paging etc.

Could whomever is maintaining the data module please consider and post any implications that I may have missed in making these modifications.

An additional problem: when we choose a filter selection, and the the system returns a multi-page dataset, and we select go to next page, it says that there are no compatible registers.

While we're talking about search capabilities it's worth noting that, rather than defaulting to list browse view, the user would encounter a search page upon entering a particular database. This of course is typically how one enters into a database of any size. One possibility could be to insert the appropriate search code in the header section of the list template. Has anyone given this a try?

Thanks,

Jim

Developement for the advanced search template is as follows:

I've gotten the advanced search tab working as the default tab for the advanced search option.  I've managed to get a default template to display in the advanced search tabe when you click the "Reset template" button.  This template shows up in the data object for the database under the [asearchtemplate] tag, but when I click "Save template" I get an "Invalid argument applied to a foreach" error....

Im not quite sure how the templates are linked to their data objects.  I've been looking through the Database files, but I haven't been able to find this yet.  What function gets called when you click "Save template"?  If I knew this, I would be able to get some progress made.

I'll keep looking through the module however.  I'll figure it out eventually.

I recently had a similar (though less complex) requirement, to be able to filter the database by a certain field (in this case a 'category', which is a menu field). I implemented this by allowing filter forms to be added to the list header and footer templates, so adding e.g. {{Category}} to the header template would cause a form to be inserted allowing the user to choose which category to show. It should also work for other field types, though this has not been tested much.

It sounds like this functionality would be covered by the 'Advanced Search Template' feature, but I have attached my patch anyway in case it is useful to anybody else.