I'm beginning the process of implementing the kind of simple search interface documented by JN here and in preceding posts.

I've generated a view of the complete document data (excluding custom fields, for the moment). The view can be dumped into an Excel spreadsheet, so that's what I've done (attached). These are the details:

• There's one row for each document in the spreadsheet.
• Regular fields in the document table are copied as-is into the spreadsheet.
• One-to-one lookup fields (such as document status) are copied in the form of their text representation (just as you see them when viewing a single document)One-to-many fields are turned into a single field in which each entry is shown in its text representation, and entries are separated by pipe characters (|).

I think this view, and the ability to dump it into a spreadsheet, will be useful in itself (although it takes several minutes to generate it). The rest of the plan, though, goes like this:

1. Write a procedure to copy the view (in which everything is represented as text) to a new table, in which all fields are text fields.
2. Create a full-text index on this table.
3. Create a simple text-search interface that uses a simple Google-style search box to search that full-text index.

This would make it simple to do normal searches as if you were searching with Google, not worrying about fields and drop-down selectors etc. It would have to lag behind changes in the db a little, though; the table would have to be re-generated in order to take account of changes in the underlying database. We might be able to automate that, or we might have to run a procedure to refresh it periodically.

Here's the SQL used to create the views:

/*
CREATING VIEWS: 
LITTLE VIEWS FOR USE IN THE MASTER VIEW
*/

CREATE VIEW VW_doc_chapters_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(chapters.ch_name SEPARATOR ' | ') 
	FROM docs_to_chapters 
        INNER JOIN chapters ON chapters.ch_id = docs_to_chapters.dtc_ch_id_fk 
        INNER JOIN documents as ch_docs ON ch_docs.doc_id = docs_to_chapters.dtc_doc_id_fk
        WHERE docs_to_chapters.dtc_doc_id_fk = documents.doc_id) 
AS chapters

FROM documents
ORDER BY documents.doc_id);


CREATE VIEW VW_doc_authConcerns_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(authConcerns.ac_name SEPARATOR ' | ') 
	FROM docs_to_authConcerns 
        INNER JOIN authConcerns ON authConcerns.ac_id = docs_to_authConcerns.dta_authConcern_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_authConcerns.dta_doc_id_fk
        WHERE docs_to_authConcerns.dta_doc_id_fk = documents.doc_id) 
AS authConcerns

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_disStatus_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(disStatus.ds_desc SEPARATOR ' | ') 
	FROM docs_to_disStatus 
        INNER JOIN disStatus ON disStatus.ds_id = docs_to_disStatus.dts_disStatus_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_disStatus.dts_doc_id_fk
        WHERE docs_to_disStatus.dts_doc_id_fk = documents.doc_id) 
AS disStatus

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_disTopics_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(disTopics.dc_name SEPARATOR ' | ') 
	FROM docs_to_disTopics 
        INNER JOIN disTopics ON disTopics.dc_id = docs_to_disTopics.dtc_disTopic_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_disTopics.dtc_doc_id_fk
        WHERE docs_to_disTopics.dtc_doc_id_fk = documents.doc_id) 
AS disTopics

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_disTypes_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(disTypes.dt_name SEPARATOR ' | ') 
	FROM docs_to_disTypes 
        INNER JOIN disTypes ON disTypes.dt_id = docs_to_disTypes.dtd_disType_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_disTypes.dtd_doc_id_fk
        WHERE docs_to_disTypes.dtd_doc_id_fk = documents.doc_id) 
AS disTypes

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_docPeriods_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(docPeriods.dp_name SEPARATOR ' | ') 
	FROM docs_to_docPeriods 
        INNER JOIN docPeriods ON docPeriods.dp_id = docs_to_docPeriods.dtp_docPeriod_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_docPeriods.dtp_doc_id_fk
        WHERE docs_to_docPeriods.dtp_doc_id_fk = documents.doc_id) 
AS docPeriods

FROM documents
ORDER BY documents.doc_id);


CREATE VIEW VW_doc_meetPlaces_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(meetPlaces.mp_desc SEPARATOR ' | ') 
	FROM docs_to_meetPlaces 
        INNER JOIN meetPlaces ON meetPlaces.mp_id = docs_to_meetPlaces.dtm_meetPlace_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_meetPlaces.dtm_doc_id_fk
        WHERE docs_to_meetPlaces.dtm_doc_id_fk = documents.doc_id) 
AS meetPlaces

FROM documents
ORDER BY documents.doc_id);

CREATE VIEW VW_doc_news_concat AS
(SELECT
documents.doc_id,
(SELECT GROUP_CONCAT(news.nw_desc SEPARATOR ' | ') 
	FROM docs_to_news 
        INNER JOIN news ON news.nw_id = docs_to_news.dtn_news_id_fk 
        INNER JOIN documents as temp_docs ON temp_docs.doc_id = docs_to_news.dtn_doc_id_fk
        WHERE docs_to_news.dtn_doc_id_fk = documents.doc_id) 
AS news

FROM documents
ORDER BY documents.doc_id);





/* THE MASTER VIEW, TO BE EXPANDED. */
CREATE VIEW VW_documents_all AS

(SELECT 
documents.doc_id,
docTypes.dt_name AS docType,
VW_doc_chapters_concat.chapters AS chapters,
VW_doc_docPeriods_concat.docPeriods AS docPeriods,
projects.proj_name,
documents.doc_archive, documents.doc_cote, documents.doc_date,
docStatusVals.dsv_description,
documents.doc_notBefore, documents.doc_notAfter, documents.doc_description, documents.doc_people,
documents.doc_notes, documents.doc_transcription, documents.doc_done,
documents.doc_revisit, documents.doc_linked_files, 
VW_doc_disStatus_concat.disStatus AS disStatus,
VW_doc_disTypes_concat.disTypes AS disTypes,
VW_doc_disTopics_concat.disTopics AS disTopics,
documents.doc_disTopicDesc,
VW_doc_authConcerns_concat.authConcerns AS authConcerns,
documents.doc_authConcernDesc,
VW_doc_meetPlaces_concat.meetPlaces AS meetPlaces,
VW_doc_news_concat.news AS news

FROM documents
LEFT JOIN docTypes ON documents.doc_to_docTypes_id = docTypes.dt_id
JOIN VW_doc_chapters_concat ON documents.doc_id = VW_doc_chapters_concat.doc_id
JOIN VW_doc_docPeriods_concat ON documents.doc_id = VW_doc_docPeriods_concat.doc_id
LEFT JOIN projects ON documents.doc_to_projects_id = projects.proj_id
LEFT JOIN docStatusVals ON documents.doc_to_docStatusVals_id = docStatusVals.dsv_id
JOIN VW_doc_disStatus_concat ON documents.doc_id = VW_doc_disStatus_concat.doc_id
JOIN VW_doc_disTypes_concat ON documents.doc_id = VW_doc_disTypes_concat.doc_id
JOIN VW_doc_disTopics_concat ON documents.doc_id = VW_doc_disTopics_concat.doc_id
JOIN VW_doc_authConcerns_concat ON documents.doc_id = VW_doc_authConcerns_concat.doc_id
JOIN VW_doc_meetPlaces_concat ON documents.doc_id = VW_doc_meetPlaces_concat.doc_id
JOIN VW_doc_news_concat ON documents.doc_id = VW_doc_news_concat.doc_id

ORDER BY documents.doc_id);