Re: Use of an associated list with completing-read

[Jean Louis <bugs@gnu.support>]

* Heime <heimeborgia@protonmail.com> [2024-04-19 02:50]:
> Haw can I have an interactive function that displays a list of elements from an
> associated list using completing-read ?   

In my work on RCD Notes & Hyperscope for GNU Emacs, The Dynamic
Knowledge Repository:
https://gnu.support/gnu-emacs/rcd-notes-for-gnu-emacs/index.html I am
using daily many many times completing-read that is offering me
various choices.

In general, each choice has unique ID assigned, as that is how the database works.

I have faced similar problem as described as I wanted to choose items
by the name and get that unique ID as the result.

But guess what, people may be named same, and items could have same
name, with the different ID, as items could be in different contexts,
or belonging to different sets, but having same name.

The way how I have solved it, forever, is that items appear with the
unique ID in the same line, and their selection is based on combo view
in the database.

Combo view could combine the name with some other parameters, such as:

First name, last name, email address -- as this could make the name
enough unique and distinguishable rather than only choosing by first
name, last name, as there are many same entries in my database.

Here are some not so used functions which may demonstrate closer what I mean:

(defun cf-search-by-sql (sql prompt)
  "Finds items by SQL that shall contain at first place the ID of the item."
  (let* ((collection (rcd-sql-list sql cf-db))
	 (choice (completing-read prompt collection))
	 (id (string-cut-id choice)))
    id))

(cf-search-by-sql "SELECT hyobjectsubtypes_id || ' ' || hyobjectsubtypes_name FROM hyobjectsubtypes" "Find Hyperdocuments by subtypes: ")

The above function gives me results such as below:

128 possible completions:
1 Default 	10 Call 	100 Deja-Vu 	101 Minutes
102 Technical 	103 Police 	104 Daily Routine 	105 Presentation
106 DISEASE 	107 Project 	108 Sales Flow 	109 Sales stage
11 Pay 	110 Recommendation 	111 Policy 	112 Financial Planning Program No. 1

Choosing right one could involve using joker as prefix and object's name:

*Recom TAB -- would expand into 110 Recommendation

and function `string-cut-id' would then give me as result 110.

(defun string-cut-id (s)
  "Return the ID number as integer from beginning of a string S.
A space must follow the ID number, without many checks.

When string S is `123 Hello there' this function will return 123."
  (let* ((until (string-match " " s)))
    (if until
	(string-to-number (substring s nil until))
      nil)))

Today, many of those functions changed, and I am not using the ID number as prefix, rather as suffix, and to make it more distinguishable, I am enclosing it in square brackets:

(defun rcd-completing-read-sql-hash (prompt sql pg &optional history initial-input not-require-match auto-initial-input)
  "Complete selection by using SQL.

First column shall be unique id, followed by text
representation.  Example SQL query:

SELECT people_id, people_firstname || ' ' || people_lastname FROM people

PG is database handle.  HISTORY is supported with INITIAL-INPUT
Argument PROMPT will be displayed to user."
  (let* ((gc-cons-threshold 5000000)
	 (hash (rcd-sql-hash-with-key sql pg))
	 (completion-ignore-case t)
	 (require-match (if not-require-match nil t))
	 (history (or history (rcd-ask-history-variable prompt)))
	 (initial-input (or initial-input (when auto-initial-input (car (symbol-value history)))))
	 (choice (completing-read prompt hash nil require-match initial-input history))
	 (choice (string-trim choice))
	 (id (gethash choice hash)))
    (if id id
      (if not-require-match 
	  choice))))

The above function simplifies it for me so that I can make SQL queries in such way that first item is the ID followed by string representing the item.

The SQL statement "SELECT hyobjectsubtypes_id, hyobjectsubtypes_name FROM hyobjects" would then result with following choices in completing-read:

(rcd-completing-read-sql-hash "Select subtype: " "SELECT hyobjectsubtypes_id, hyobjectsubtypes_name FROM hyobjectsubtypes" cf-db)

128 possible completions:
Acknowledgment [86] 	Administrative Scale [95] 	Agreement [17]
Appointment [27] 	Archive [99] 	Article [2]
Attachment [9] 	Battle Plan [90] 	Book [6]
Borrowed Item [38] 	Business Plan [80] 	Business Profile [13]

Even if there would be 2 same names, there would be distinguishable
unique ID, for example, there could be "Business Plan [80]" and
"Business Plan [81]" to choose from.

Following function is internally preparing the hash for
completing-read function. It makes hash names unique, and the ID is
obtained by using (gethash choice hash) in the
rcd-completing-read-sql-hash function.

(defun rcd-sql-hash-with-key (sql pg)
  "Return hash with key TEXT [ID] and value ID from SQL result.

Output is in the form ID, TEXT
Argument PG is database handle."
  (let ((hash (make-hash-table :test 'equal))
	(res (rcd-sql sql pg)))
    (while res
      (let ((item (pop res)))
	(cond ((eq (type-of item) 'vector) (puthash (format "%s [%s]" (elt item 1) (elt item 0)) (elt item 0) hash)))))

    hash))

While this may all sound complicated the point is that one could embed
the "key" in the choice itself, and such need not be a number, it
could be string; additionally using hash with completing-read is
additional solution to general problems.

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/